By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

Slow Front End Performance (Access 2002)

jamjar
P: 50
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:

Expand|Select|Wrap|Line Numbers
  1. Test Description    Front End    Direct
  2. Open DB                                         1s                   7s
  3. Open Clauses form    25s           3s
  4. Filter Clauses           8s                          3s
  5. Open MDocs form                  3s                          1s
  6. Open Pos form          3s              1s
  7. Open MDocs table    12s                                      1s
  8. Add record (form)    23s           2s
  9. Scroll (form view)    <1s            1-2s
  10. Text entry (forms)    <1s            1-2s
  11.  
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
May 21 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,619
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:

Expand|Select|Wrap|Line Numbers
  1. Test Description    Front End    Direct
  2. Open DB                                         1s                   7s
  3. Open Clauses form    25s           3s
  4. Filter Clauses           8s                          3s
  5. Open MDocs form                  3s                          1s
  6. Open Pos form          3s              1s
  7. Open MDocs table    12s                                      1s
  8. Add record (form)    23s           2s
  9. Scroll (form view)    <1s            1-2s
  10. Text entry (forms)    <1s            1-2s
  11.  
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.
May 21 '07 #2

jamjar
P: 50
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):
Expand|Select|Wrap|Line Numbers
  1. Test Description    Front End   Direct
  2. Open DB                                       1s           7s
  3. Open Clauses form     25s             3s
  4. Filter Clauses              8s                 3s
  5. Open MDocs form          3s                                   1s
  6. Open Pos form           3s                 1s
  7. Open MDocs table     12s           1s
  8. Add record (form)     23s             2s
  9. Scroll (form view)   <1s         1-2s
  10. Text entry (forms)   <1s         1-2s
  11.  
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
May 21 '07 #3

jamjar
P: 50
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\General 'Track name Auto Correct' (on the back end)
  • Set SubDataSheet.Name 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 .....
May 24 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.