Volibrawl
asked on
A2K Performance Tips
Many of my clients have "upgraded" to Access 2000 in the past year or so. Most have suffered through poor performance and just accepted it.
I have a few applications, however, where the performance is so poor that they border on "unuseable". These are sytems that have been in place for 3 or 4 years, running quite successfully under Access 97, but now are so slow that they must be modified or abandoned in favor of some other solution. I have had to "add" hourglasses and "Wait..calculating" prompts to assure the users that the app is actually working, when for years this was not necessary.
By way of example, I have a form, based on a query that does some calculations, which loads and refreshes in 4 seconds in Access 97. It takes (varies) from 9 to 20 seconds in A2K. This is using all local tables on the same machine. The ONLY difference is that the mdb is converted to A2k.
Can anyone offer any tips for speeding up A2k SPECIFICALLY, particularly in Networked environments with linked tables .. is there some different Jet that can be used? some special query optimizations? should I use code modules instead of code behind the forms? create relations or just JOIN in queries? more tables related to each other or fewer tables with more fields? yada yada yada ..
I have of course compacted, indexed, etc. All hardware has PLENTY of memory and in most cases are MUCH faster machines than they used to have. The amount of data is NOT a cause, we have archived and pared down files beyond the client's desires.
I would welcome any tips that relate specifically to Access2k (not defrag, empty recycle bin, etc. please) and any insights into XP (is it faster? faster than 97?).
Thanks in advance.
Holding back some points for multiple awards ..:)
I have a few applications, however, where the performance is so poor that they border on "unuseable". These are sytems that have been in place for 3 or 4 years, running quite successfully under Access 97, but now are so slow that they must be modified or abandoned in favor of some other solution. I have had to "add" hourglasses and "Wait..calculating" prompts to assure the users that the app is actually working, when for years this was not necessary.
By way of example, I have a form, based on a query that does some calculations, which loads and refreshes in 4 seconds in Access 97. It takes (varies) from 9 to 20 seconds in A2K. This is using all local tables on the same machine. The ONLY difference is that the mdb is converted to A2k.
Can anyone offer any tips for speeding up A2k SPECIFICALLY, particularly in Networked environments with linked tables .. is there some different Jet that can be used? some special query optimizations? should I use code modules instead of code behind the forms? create relations or just JOIN in queries? more tables related to each other or fewer tables with more fields? yada yada yada ..
I have of course compacted, indexed, etc. All hardware has PLENTY of memory and in most cases are MUCH faster machines than they used to have. The amount of data is NOT a cause, we have archived and pared down files beyond the client's desires.
I would welcome any tips that relate specifically to Access2k (not defrag, empty recycle bin, etc. please) and any insights into XP (is it faster? faster than 97?).
Thanks in advance.
Holding back some points for multiple awards ..:)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Frédéric Métraux
Another trick: Access 2000 has a query optimizer based on statistics. These statistics are calculated when saving the query. So, when you move your frontend from small test data to large user data, the query should be opened and saved in order the optimizer to recalculate.
1. Turn off subdatasheet feature ff your not using it.
2. Turn off Autocorrect.
3. Absolutly make sure nothing is still in A97 format (BE and workgroup file).
4. If this app is split, make sure you hold open a reference to the backend for the the life of the app. This can be as simple as a global object variable with a database ref.
5. If split and BE is on an NT server, turn off opportunistic locking.
6. Make sure the FE/BE is not being virus scanned.
7. Make sure the app is compiled.
XP is no faster from what I've heard (from the very few that seem to be using it), which is what I'd expect as it's still JET 4.0 under the covers.
Jim.
2. Turn off Autocorrect.
3. Absolutly make sure nothing is still in A97 format (BE and workgroup file).
4. If this app is split, make sure you hold open a reference to the backend for the the life of the app. This can be as simple as a global object variable with a database ref.
5. If split and BE is on an NT server, turn off opportunistic locking.
6. Make sure the FE/BE is not being virus scanned.
7. Make sure the app is compiled.
XP is no faster from what I've heard (from the very few that seem to be using it), which is what I'd expect as it's still JET 4.0 under the covers.
Jim.
ASKER
I don't think I have any need for subdatasheets but I don't know how to turn it off/on. Is the code provided by Ornicar how you do it? Is there no Option/Preference setting?
Many thanks for the other tips as well, some of which I am already doing ... I intend to try moving some code from behind my slowest loading forms to modules .....??
Many thanks for the other tips as well, some of which I am already doing ... I intend to try moving some code from behind my slowest loading forms to modules .....??
Yes, there is a checkbox for turning off subdatasheets somewhere in the right-click of a table, but you will have to do this on all tables. So the code I provided helps by doing this for all tables at once.
Good idea for moving your form's code to modules. I did this myself on very slow opening forms, and very often used forms with satisfaction. Be sure when finished with this to set the 'Has Module' property of the form to NO: This will suppress all code behind the form, and the form loads in a snap!
Remember to make all events a public function, and call this function with =MyFormNameOnOpen() for instance.
Be patient as this is almost a migration job. Well, moving from A97 to A2K IS a migration anyway.
I hope there will be other tips from other experts in this question. I myself had a lot of performance troubles with A2K apps.
Good idea for moving your form's code to modules. I did this myself on very slow opening forms, and very often used forms with satisfaction. Be sure when finished with this to set the 'Has Module' property of the form to NO: This will suppress all code behind the form, and the form loads in a snap!
Remember to make all events a public function, and call this function with =MyFormNameOnOpen() for instance.
Be patient as this is almost a migration job. Well, moving from A97 to A2K IS a migration anyway.
I hope there will be other tips from other experts in this question. I myself had a lot of performance troubles with A2K apps.
Oh! Another tip, worth for any version: If in your forms you have combo boxes or drop down boxes based on a select statement: Create a query from this statement and base the combo to the query instead of the SQL text. The form is displayed faster. This works well for A97, but for A2K I feel it works too.
ornicar,
<<Oh! Another tip, worth for any version: If in your forms you have combo boxes or drop down boxes based
on a select statement: Create a query from this statement and base the combo to the query instead of
the SQL text. The form is displayed faster. This works well for A97, but for A2K I feel it works too. >>
This is no longer required. Starting with A97, Access automatically saves any SQL rowsource for list and combo boxes as a temp query that's hidden. You can see them though by looping through the querydefs collection and looking for any that starts with a ~ character.
Jim.
<<Oh! Another tip, worth for any version: If in your forms you have combo boxes or drop down boxes based
on a select statement: Create a query from this statement and base the combo to the query instead of
the SQL text. The form is displayed faster. This works well for A97, but for A2K I feel it works too. >>
This is no longer required. Starting with A97, Access automatically saves any SQL rowsource for list and combo boxes as a temp query that's hidden. You can see them though by looping through the querydefs collection and looking for any that starts with a ~ character.
Jim.
ASKER
hmm.. I have always saved big/complex or combo/list "selects" as queries anyway.
Only recently have I started not doing that 'cuz I learned (somewhat) how to build some queries on the fly.
Keep the tips coming, Folks!
ASIDE:
One application I am currently re-working has several forms/reports with controls that are the result of many queries with group bys, sums, etc. I probably don't have this working very efficiently, but in 97 the speed was within acceptable limits. The overhead of the calculations/relations groupbys, etc. seems to be the hangup here.
I don't want to go into depth here, but if any of you efficiency experts are interested in having a look at it, I can explain what I'm trying to do privately and maybe some glaring errors would be apparent.
If interested, drop your e-mail address here.
Only recently have I started not doing that 'cuz I learned (somewhat) how to build some queries on the fly.
Keep the tips coming, Folks!
ASIDE:
One application I am currently re-working has several forms/reports with controls that are the result of many queries with group bys, sums, etc. I probably don't have this working very efficiently, but in 97 the speed was within acceptable limits. The overhead of the calculations/relations groupbys, etc. seems to be the hangup here.
I don't want to go into depth here, but if any of you efficiency experts are interested in having a look at it, I can explain what I'm trying to do privately and maybe some glaring errors would be apparent.
If interested, drop your e-mail address here.
Proper indexing is crucial. Two things you can use to check performance of queries is the ISAMStats() function, which returns disk hits and what not. The second is the SHOWPLAN() function, which creates a text file showing the execution plan of a query. However it is limited in that it cannot show sub queries.
As for looking at your MDB< I wish I could, but I don't have the time right now. I've already got 3 problem MDB's in my in-box and I leave for a 4 day business trip to NYC tomorrow morning. And I won't even start to talk about my regular client stuff<g>
Jim.
As for looking at your MDB< I wish I could, but I don't have the time right now. I've already got 3 problem MDB's in my in-box and I leave for a 4 day business trip to NYC tomorrow morning. And I won't even start to talk about my regular client stuff<g>
Jim.
ASKER
Thanks guys .. implemented the suggestions where appropriate. I can't say that it made an appreciable difference.
I guess I am just IRRITATED as HELL at A2K .. it REALLY SUCKS in EVERY WAY that I can see. I am forced to use it since so many clients "upgraded".
Jim, I will post a question for some points for you as well.
I guess I am just IRRITATED as HELL at A2K .. it REALLY SUCKS in EVERY WAY that I can see. I am forced to use it since so many clients "upgraded".
Jim, I will post a question for some points for you as well.
Yes, A2K is a piece of junk. A97 is far better. Thanks M$!
Try checking the record locking type.
MS reckons row locking is slower than page locking, but I have never tested it.
Jet 3.51 (A97) uses page level locking, but Jet 4.0 (A2k/XP) supports row level locking. Also use DAO because it is optimised for jet databases. ADO is a larger object model and would be a tad slower on obtaining some properties/methods.
BTW.. I love all these responses; this feedback is so valuable for all of us. 8)
MS reckons row locking is slower than page locking, but I have never tested it.
Jet 3.51 (A97) uses page level locking, but Jet 4.0 (A2k/XP) supports row level locking. Also use DAO because it is optimised for jet databases. ADO is a larger object model and would be a tad slower on obtaining some properties/methods.
BTW.. I love all these responses; this feedback is so valuable for all of us. 8)
I like this thread too, everyone has contributed to add something useful and it results in a nice collection of performance tips. Thanks, Mattew to add this one. Cheers...
There are some insightful comments on this section.
I wanted to ask people if they have had problems when changing OS platforms.
Still running Access 97 (runtime edition at the mo) and the whole system was mapped onto a new server running Windows 2000.
Since then it has been so slow to run.
The access file is running a query extracting data from a FoxPro DB (.DBF).
It is then transferring it to another Access database.
The old system was NT4 but since the change over I have not been able to extract half the info I require (200,000 records).
I know the whole thing works because it will give results on smaller jobs (10,000 records)
ANy suggestions?
I wanted to ask people if they have had problems when changing OS platforms.
Still running Access 97 (runtime edition at the mo) and the whole system was mapped onto a new server running Windows 2000.
Since then it has been so slow to run.
The access file is running a query extracting data from a FoxPro DB (.DBF).
It is then transferring it to another Access database.
The old system was NT4 but since the change over I have not been able to extract half the info I require (200,000 records).
I know the whole thing works because it will give results on smaller jobs (10,000 records)
ANy suggestions?
Yes, changing OS affects performance because there are thtee OS features that comes in mind: Swap file, Disk cache size and TC/PIP settings in case the db is on a network. This isn't a2K specific so other versions of Access are affected.
This is more OS specific and the better would be another thread, but I would:
1) Optimize my swap file to be contigous (same min & max sizes) and on another physical HD.
2) Check the sharewares for any cache & system tuning proggy.
3) Check the web for network tweaks.
Hey, this question is soon two years old :o) Two more weeks and the fiesta can begin!
For me, with A2k after all this time is that now more memory really does the trick most.
What do you think kids? Which present should we give to this thread?
This is more OS specific and the better would be another thread, but I would:
1) Optimize my swap file to be contigous (same min & max sizes) and on another physical HD.
2) Check the sharewares for any cache & system tuning proggy.
3) Check the web for network tweaks.
Hey, this question is soon two years old :o) Two more weeks and the fiesta can begin!
For me, with A2k after all this time is that now more memory really does the trick most.
What do you think kids? Which present should we give to this thread?
<<Still running Access 97 (runtime edition at the mo) and the whole system was mapped onto a new server running Windows 2000.
Since then it has been so slow to run.>>
Turn off Opportunistic locking on the server. It's aregistry setting. You can find info on it in the MSKB. Just do a search for OPLOCKS
Jim.
Since then it has been so slow to run.>>
Turn off Opportunistic locking on the server. It's aregistry setting. You can find info on it in the MSKB. Just do a search for OPLOCKS
Jim.
Other thoughts
1. Make sure the MDB's are not being virus scanned on open.
2. Make sure all users have read/write/delete priv for the directory where the MDB resides on the server.
Now expert hat off and Page Editor hat on: when in the heck do you plan to close this question? ;)
Jim.
1. Make sure the MDB's are not being virus scanned on open.
2. Make sure all users have read/write/delete priv for the directory where the MDB resides on the server.
Now expert hat off and Page Editor hat on: when in the heck do you plan to close this question? ;)
Jim.
Its already closed, but it is the second time it is awakened for the benefit of all :O)
Jim: <Turn off Opportunistic locking on the server> How you do that? Where to go?
Jim: <Turn off Opportunistic locking on the server> How you do that? Where to go?
<<Its already closed, but it is the second time it is awakened for the benefit of all :O) >>
I made the assumtion that it was still open from your comment. Really need to slow down and look at things. That's twice in the same week they I've managed to look really stupid.
<<Jim: <Turn off Opportunistic locking on the server> How you do that? Where to go?>>
Configuring Opportunistic Locking in Windows
http://support.microsoft.com/default.aspx?scid=kb;en-us;296264
I made the assumtion that it was still open from your comment. Really need to slow down and look at things. That's twice in the same week they I've managed to look really stupid.
<<Jim: <Turn off Opportunistic locking on the server> How you do that? Where to go?>>
Configuring Opportunistic Locking in Windows
http://support.microsoft.com/default.aspx?scid=kb;en-us;296264
LOL
I found this link with some great info on Opportunitic Locking issues.
http://www.granite.ab.ca/access/corruption/causesoplocks.htm
Which mentions:
"Access, and other software, has some Opportunistic Locking (OpLocks) problems with Win NT 4.0, Windows 2000 and Novell servers commonly known as the OpLocks problem..
..Converting to Access 2000 from Access 97 also appears to bring up this problem. Also note that this same situation can greatly decrease Access performance on the network."
It also mentions how this can be causing .mdp corruption and as well has links to a number of related sub issues.
http://www.granite.ab.ca/access/corruption/causesoplocks.htm
Which mentions:
"Access, and other software, has some Opportunistic Locking (OpLocks) problems with Win NT 4.0, Windows 2000 and Novell servers commonly known as the OpLocks problem..
..Converting to Access 2000 from Access 97 also appears to bring up this problem. Also note that this same situation can greatly decrease Access performance on the network."
It also mentions how this can be causing .mdp corruption and as well has links to a number of related sub issues.
1. More Performance Ideas
--------------------------
Oh at the same site the following link is specifically on A2k performance,
This site is packed full of additional performance issues not previously mentioned.
http://www.granite.ab.ca/access/performancefaq.htm
Excerpt:
"The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection fixes
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
Other reasons are
- Speed up your Access 2000 Forms (2003-12-17)
- New format of Access 2000 MDB
- Place backend MDB on the root of the network share rather than several folders down
- Shorten the name of the backend MDB
- Miscellaneous Performance Suggestions
- Virus scanning
- System utilities
- Outlook 97 Journaling
- Queries up to five times slower if user defined functions and Jet 4.0 SP4 or 5
- Use of DSUM, DCOUNT, etc after splitting. (2003-11-06)
- How to speed up complex forms and reports with many records each with subreports.
- Wireless Access Point channel conflict"
2. LDB Locking affecting performance
--------------------------
I found the LDB locking which a persistent recordset connection something that wasn't mentioned on this post so far, the above site has a link of what to do but basically you bind a permanatly open table to a tiny table on your BE and that means that access stops accessing the lockfile multuiple times for the same user reducing lock file access & related performace issues.
Im only just trying it now so I can't comment on how useful this is.
Direct link on fixing the 'lockfile' issue here:
http://www.granite.ab.ca/access/performanceldblocking.htm
--------------------------
Oh at the same site the following link is specifically on A2k performance,
This site is packed full of additional performance issues not previously mentioned.
http://www.granite.ab.ca/access/performancefaq.htm
Excerpt:
"The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection fixes
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
Other reasons are
- Speed up your Access 2000 Forms (2003-12-17)
- New format of Access 2000 MDB
- Place backend MDB on the root of the network share rather than several folders down
- Shorten the name of the backend MDB
- Miscellaneous Performance Suggestions
- Virus scanning
- System utilities
- Outlook 97 Journaling
- Queries up to five times slower if user defined functions and Jet 4.0 SP4 or 5
- Use of DSUM, DCOUNT, etc after splitting. (2003-11-06)
- How to speed up complex forms and reports with many records each with subreports.
- Wireless Access Point channel conflict"
2. LDB Locking affecting performance
--------------------------
I found the LDB locking which a persistent recordset connection something that wasn't mentioned on this post so far, the above site has a link of what to do but basically you bind a permanatly open table to a tiny table on your BE and that means that access stops accessing the lockfile multuiple times for the same user reducing lock file access & related performace issues.
Im only just trying it now so I can't comment on how useful this is.
Direct link on fixing the 'lockfile' issue here:
http://www.granite.ab.ca/access/performanceldblocking.htm
<<I found the LDB locking which a persistent recordset connection something that wasn't mentioned on this post so far, >>
It was mentioned and it really can give you a big boost in performance depending on usage of the app.
Jim.
It was mentioned and it really can give you a big boost in performance depending on usage of the app.
Jim.
This is incredible - I just followed these 2 points :
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
and a form that used to take 1-2 minutes to open now opens in about 10 seconds. Everyone should check these out..
Matt.
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
and a form that used to take 1-2 minutes to open now opens in about 10 seconds. Everyone should check these out..
Matt.
This thread/question/whatever helped me out, so I thought I would leave a tip that really helped me. My Access 2k forms were getting very slow in the FE after I made some changes to the linked database on the BE. I used the linked table manager to refresh all tables and table locations and it really sped things up. Went from 35+ sec to ~2.
Might seem like an obvious fix to the elite EE users, but it tripped me up. Cheers,
Walt
Might seem like an obvious fix to the elite EE users, but it tripped me up. Cheers,
Walt