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 ..:)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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.
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 .....??
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.
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.
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.
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.
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.
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)
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?
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?
<<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.
<<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.c
I found this link with some great info on Opportunitic Locking issues.
http://www.granite.ab.ca/a
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/a
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/a
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
Business Accounts
Answer for Membership
by: ornicarPosted on 2002-02-07 at 01:48:17ID: 6785104
Yes, A2K is slower than 97.
The most slow down I experienced is the forms opening when many forms are present in the database.
A2000 has a different way to store the code. In A97 it was stored in tables. In A2K its not.
To suppress the slow opening on forms, all their code must be stored in a module, and the events called not by a code event, but by a function like =MyFormNameOnOpen().
I did this on a database and the opening of forms went very fast. The bad new is that it needs a lot of work: One module for each form, one function for each event with a kind of naming convention, all Me. changed to Forms!.. syntax. But the result is worth the effort.
Another annoyance is the slow opening of linked tables. This can be corrected by turning off the 'subdatasheet' option. Here is some code to suppress this option on all tables. Must be run on the backend database:
Function TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String
Dim propType As Integer
Dim propVal As String
Dim strS As String
Dim i, intChangedTables
Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[NONE]"
On Error Resume Next
For i = 0 To MyDB.TableDefs.Count - 1
If (MyDB.TableDefs(i).Attribu
If MyDB.TableDefs(i).Properti
MyDB.TableDefs(i).Properti
intChangedTables = intChangedTables + 1
End If
If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(i).CreatePr
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(i).Properti
Else
If Err.Number <> 0 Then
MsgBox "Error: " & Err.Number & " on Table " _
& MyDB.TableDefs(i).Name & "."
MyDB.Close
Exit Function
End If
End If
End If
Next i
MsgBox "The " & propName & _
" value for all non-system tables has been updated to " & propVal & "."
MyDB.Close
End Function
I tried also to store all ActiveX objects on a ramdrive. It improved performance a bit when loading them, but its a hell to configure all client's computers like this.