Network speed...

[Mgomp]

Hello all!

I have ran into a problem which I can't figure out why happening.
Background: Access 2010, wide area corporate network.

I have made a app which are splitted in a "front end" with everything except tables, and a "Back end" with all the tables.
The "Back End" are 7 tables, all tables with one unik filed as key index, all tables exept 2 of them are holding 3 fields. Tha last two have 5 and 7 fields.
The relation between the tables are set in the "Back end". All relation have reference integrity set, and some of them with aut. update related fields.

When moving the "Back end" to one of the company servers, I get a ugly suprise; When starting the Linked Table Manager on the Front End, it took almost 1 minute to complete. And the front end almost choked up! Running the app on this non-existing speed is out of the question....
Then, I moved the back end to a smaller sever I knew have much lesser trafic, and the server is physiclly much closer. There was a big different in connection speed, and the program run some what faster. But still to slow.

Localy the Linked Table Manager and the app runs like a rabbit....

I have run the Basic Compile in the VBA editor, an I have also made an accde version of the front end. (one onother story here on the forum....)

Ok, How can I speed up? The testing was on a almost empty back end - less than 1 MB total... The front end is round 2MB

All of the data - with no exepitons - is gathered via queries. No forms or control ar linked directly to the table.

Any good Ideas? I know there are impossible to give me an exactly answer based on this info - but a "where to look" would also be very helpful!

Regards.

 

[RuralGuy]

You mentioned a Corporate WAN. What speed? Typically a LAN will run perhaps 100 times faster than a WAN. You will probably be disappointed until you start using a Terminal Service such as Citrix but here's some suggestions: http://www.granite.ab.ca/access/performancefaq.htm

 

[gemma-the-husky]

one trick is to ensure you keep a connection to the backend open at all times.

i use a dummy table in the backend, and open a recordset connection to this with my switchboard.

 

[RuralGuy]

Dave: Tony talks about a Persistant Connection in the link I supplied.

 

[Mgomp]

Thank You!

I will go through everything and try to check out as much as posible in the liste.

Regards.

 

[RuralGuy]

What speed is your WAN?

 

[Mgomp]

I must check tomorrow, but I think it should be 100Mbits. At least on the paper...

Regards.

 

[Mgomp]

Hi again.

Just an update; I have done most of thing on the list posted earlier here;
- Changed the Subdatasheet Name Property to None
- Track name Auto Correct to Off
- Location of the back end closer to the servers root (only two levels down)
- Cleaned up all the code.
- Use the comiled state.

I am sorry to say, it is impossible to measure any diference..

I even create this accde file, but this just give me an another hedacke, but the speed problem where the same.

So I think I just have to let the network gurus in my companny take a look. But of course - they will blame the Access itself..

What my little mind can't understand is; When running the back end on a server located in the same building as my office, the program works all right. The linked table manager is slow to update, but everything else work all right. When moving the back end to a server 70km away, the linked table manager uses almost 1 minut to update, and every clik on any of my forms - specially opening a different form - takes for 15 to 30 seconds to get the result. Haveing the back end locally on my computer - the app runs extremly well.

And for a earlier question; The network is based on Win Server 2003 systems with several member servers several places, 100Mbit (for all I know between my com and the nearest switch...). My computer is running on Win 7 and Office pro 2010.

Regards

 

[RuralGuy]

I suspect *strongly* that your LAN is 100Mb but after the switch, the WAN is Internet speed ~1.5 Mb. Did you implement the Persistant Connection?

 

[Mgomp]

Yepp.
On my main forms On Open event I have setup an ADODB recordset, get some values, and hopefully this stays open until I close the app..
I have no rsThisrecordset.close in the app.

Maybe I should do this differently?

Regards,

 

[RuralGuy]

It should work as long as the Form remains open.

 

[Mgomp]

In the new Access menu system, I used the main form which helds all the sub menues/forms.
I believe(hope) this will aways have the status open...

 

[RuralGuy]

I load an invisible form with the AutoExec macro and let it do all of the housekeeping including keeping a table open to the BackEnd. It is also the last form to close so I can do any cleanup I might want to do.

 

[Simon_MT]

Another thing to look at is the size of the volume that the database is located in. If is a large volume 500GB+ is there a smaller volume 100GB that the database can go on. I can't remember the reason why but we used a RAID and it had something to do with the Heads and Seeks.

Simon

 

[mcgilla]

I load an invisible form with the AutoExec macro and let it do all of the housekeeping including keeping a table open to the BackEnd. It is also the last form to close so I can do any cleanup I might want to do.

Found this discussion in a search but it brings up an interesting thought in something I'm trying to do.. What you're saying is open a form linked to a dummy table with no data.. And that will help with performance for a multiuser FE/BE setup?

Using Access 2007.. having no SQL Server.. will having multiple back ends with just one "dummy table" still react slow? Just curious.

 

[RuralGuy]

Every BackEnd needs an LDB file to keep trach of the users in the file. Keeping a Persistant connections keeps Access from having to create/open this file every time someone gets into it. If you have multiple BackEnds then if possible you should keep them all open to speed things up.

 

[Mgomp]

Hi again.
Our network guys have started to look into it.... Our company have several sites in my area, with ca 50 - 70km betveen the sites. The network, Lan and Wan is running at 100Mb.
The network guys have to come up with a reason which tels me why the amount of kilometer between the sites makes the tremendous speed differences..
They have until friday to come up with a solution/answer. If any, I will update here!

Regards.

 

[RuralGuy]

Thanks for the update.

 

[Mgomp]

Well, As I supposed.... I got the information that my app is way beyond the scope of "normal" useage of access.... Yeah-right!

This is exactly what I expected. Of course my little app is within the limits of Access and network usage.

Example:
Front end; The new menu system in access 2010. Five "main forms", two of them as a main and related subform.
There are som extended forms on each main form, exapmle if one of my mainform where a sales form, then I would have an access to a small extende form which I could manipulate curstomers info.
All contact between forms and controllers(comboboxes) are all done with queries. Som few updates are done with ADO commands. After a Cleanup and Repair, the front end is about 2Mb.

Back end;
7 tables which 4 of them held 3 colums, 1 held 5 columns and 2 helds 7 columns. All tables are strongly releated to each other using - by the book - only single primary indexes. Not any primary indexes based on two ore more fields.

The point is also to use one back end for each project. Based on a earlier version of this app (also a bit simpler) and new calulation I suppose each back end will around 8 - 12MB large. The test backends I have used have been under 1MB...

I suppose only 3 - 6 persons will be using each back end.

Ok. I know that splitting a accdb into a front and back end, don't make any nettwork solution at all. It only creates an easier way to share common data. And yes, I also know that the back end is a passive component witn no transactions at all. And maybe my queries are to heavy for the network. But still - only one querie is fired at the time. (I cant open more than one form at the time. Likewise - I can only use one controller at the time....)

But is this to big and beyond the scoop of Access?

Regards.