Slow Front End Performance (Access 2002)

[jamjar]

Hi,

I recently set up a fairly simple database, though one table has dozens of text and date fields and 2000+ records. It resides on a file server at my company HQ.

It is being used at another site hundreds of miles away, so I set up a Front End with the forms and reports contained, and links to the tables in the main dB file. My understanding is that forms require bandwith, so that setting them up locally should speed up the application by reducing the information flow from the server.
These forms work about the same here at HQ whether accessed through the separate FE or through the main dB. However, the front end performance is considerably slower when it comes to retrieving data:

Test Description	Front End	Direct
Open DB									     1s				   7s
Open Clauses form	25s		   3s
Filter Clauses		   8s					      3s
Open MDocs form				  3s					      1s
Open Pos form		  3s			  1s
Open MDocs table	12s									  1s
Add record (form)	23s		   2s
Scroll (form view)	<1s			1-2s
Text entry (forms)	<1s			1-2s

which is exactly the opposite of what I thought should happen.

Am I mistaken in my understanding of the theory, or is there something I may have done wrong, or is there some other explanation?

thanks-

James

[ADezii]

Hi,

I recently set up a fairly simple database, though one table has dozens of text and date fields and 2000+ records. It resides on a file server at my company HQ.

It is being used at another site hundreds of miles away, so I set up a Front End with the forms and reports contained, and links to the tables in the main dB file. My understanding is that forms require bandwith, so that setting them up locally should speed up the application by reducing the information flow from the server.
These forms work about the same here at HQ whether accessed through the separate FE or through the main dB. However, the front end performance is considerably slower when it comes to retrieving data:

Test Description	Front End	Direct
Open DB									     1s				   7s
Open Clauses form	25s		   3s
Filter Clauses		   8s					      3s
Open MDocs form				  3s					      1s
Open Pos form		  3s			  1s
Open MDocs table	12s									  1s
Add record (form)	23s		   2s
Scroll (form view)	<1s			1-2s
Text entry (forms)	<1s			1-2s

which is exactly the opposite of what I thought should happen.

Am I mistaken in my understanding of the theory, or is there something I may have done wrong, or is there some other explanation?

thanks-

James

It's not the Forms themselves but the underlying Record Sources. The data contained within them has to be retrieved over the Network from the Back End Server.

[jamjar]

It's not the Forms themselves but the underlying Record Sources. The data contained within them has to be retrieved over the Network from the Back End Server.

I should clarify, the second column in this table (titled Direct):

Test Description	Front End   Direct
Open DB								       1s		   7s
Open Clauses form	 25s		     3s
Filter Clauses			  8s			     3s
Open MDocs form	      3s								   1s
Open Pos form		   3s			     1s
Open MDocs table	 12s		   1s
Add record (form)	 23s		     2s
Scroll (form view)   <1s		 1-2s
Text entry (forms)   <1s		 1-2s

indicates the remote user is running the forms through the main DB file directly instead of through a Front End. In both case, the user is connecting from the same remote PC to the same server file. So both results are inclusive of data being retrieved over the network from the Back End. I would have thought there would be less data transfer required when running the forms via a local FE than running the forms off the BE. If so, why the slower performance?

thanks-
James

[jamjar]

Problem solved!
In the interests of science and general education, I'll share what I've found that fixed this problem. After a lot of research, I found there are many factors contribruting to slow FE performance, when in theory the FE should be faster.
See the thread at another forum that details much of the advice leading to the following conclusions/solutions:

• Turn off Tools|Options\G eneral 'Track name Auto Correct' (on the back end)
• Set SubDataSheet.Na me to [None] for all BE tables (used script from microsoft/kb/891176).
• Changed table link references in the FE to use the actual short (8.3 type) folder names using script from microsoft/kb/261000, and changed the actual file name to 8.3 format. (Note: the table links are be set using the 8.3 type folder names without actually have to change the long folder names. I guess this saves time in translation for Access/Jet. But according to Microsoft's script, the file name must be changed to 8.3 format).
• Establish persistent link via hidden control on the switchboard, with Switchboard data source changed to include a join to a small table filtered to return just one item.

I made the first three changes first, and testing found this brought performance in opening forms down almost to the speed of opening forms in the Back End.
I then added the fourth fix (persistent link via the switchboard) and the performance opening forms from the FE is now as fast or faster than from the BE.
As per the original testing, the speed of navigation, typing, scrolling once a form is open in the FE is superior (practically instantaneous, versus 1-2 second delays).

Anyways, I hope this info helps someone else. It was an education for me!

James


PS- Another tip I came across but didn't use is to load your Row Sources using VB and the On Load event .... I can see how loading row sources for a bunch of controls can slow the form down, though I'm not sure why this would be any different than building them in to the form. But it made me think that there are some controls that don't need to be loaded until (and unless!) they are actually requested by the user .....