Microsoft Access Performance FAQ
(Last updated 2006/01/23)
Try the following suggestions as originally suggested by Frank Miller of
Microsoft PSS and extensively updated by me. Almost all of these tips
also apply to Microsoft Access 97.
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
- 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. (New2003-11-06)
- How to speed up complex forms and reports with many records each
with subreports.
- Wireless Access Point channel conflict
When every user is slow opening up the MDB before the first line of code
is run then it likely needs a decompile.
Access 2000 slow when using an Access 97 backend.
Any of the following tips can also apply in this situation. But in
particular the Sub Datasheet Name property set to nothing can be a
problem as it appears Access 2000 will default this value to [Auto].
LDB locking which a persistent recordset connection fixes
Refreshing table links can also be quite slow
Refreshing the links to tables can be quite slow even in Access 97. This
can get much worse for the second and subsequent users into a shared MDB
on a server. Once you've successfully refreshed the first table open a
recordset based on that table. Once you've finished refreshing all the
links close that recordset.
Then open a bound form or keep this recordset open if so desired
depending on your preference for better overall performance.
Sub Datasheet Name property set to [Auto]
ACC2000: Slower Performance on Linked Tables - 261000 indicates that if
the database has many linked tables that also have many relationships,
and the table that you are opening has its sub datasheet Name property
set to [Auto], this can make the table slow to open. Subdatasheets are a
new feature in Access 2000 Therefore, you are more likely to notice this
behaviour after you convert a database from an earlier version.
It is recommended that we set the sub datasheet Name property on each
table in the back-end database to [NONE]. Making this change in the
front end won't help if it even works at all.
Note that this has been mentioned as a problem with Access 2000 or newer
front ends linking to an Access 97 backend. It would appear that Access
2000 defaults this property to [Auto] if this property isn't set. So
run the code in the above mentioned Q261000 article in the Access 97
backend. You cannot run this code in the front end.
Track name AutoCorrect
Tools >Options >General >>.Track name AutoCorrect info should be off.
ACC2000: Slow Performance Opening Object with Name AutoCorrect - 200600
Slow Performance When User Opens an Object with Name AutoCorrect Enabled
- 290181
One symptom is that the MDE can be much quicker when it comes to opening
forms than the MDB. One report stated the difference was less than one
second versus four seconds.
Speed up your Access 2000 Forms (New2003-12-17)
New format of Access 2000 MDB
Access 2000 development does experience a performance decrease (and a
related increase of the database size) as compared to Access 97. This is
caused by the new way Access 2000 stores project items. Project items
consist of Forms, Reports, Macros and Modules. In previous versions,
each object had its own record in the system table. If a change was made
to an object only that one record in the system table was updated.
With the move to include the Visual Basic Editor interface, Access now
stores all project items as one blob within approximately one record in
the system table. If there are lots of code, forms and reports, then
making a change to 1 object causes us to rewrite the majority of the
blob that consists of all the project items. As a result, more is being
written to disk then was done in the past.
Some changes to the database cause Access to make a copy of the project
items instead of replacing the old project which can cause an increase
in database size. If we have a large project and we end up copying it
then we double the size of the project within the database. For example,
lets say we have 10 MB project and perform an action that causes us to
make a copy of the project instead of replacing it, the database will
grow by 10 MBs. Compacting the database at this point should recover the
project no longer being used and should reclaim some space (if not all
10 MBs).
The best choice to reduce the impact of this change is work on all the
database files located on the local system and not on the server. Also
make sure you have the fastest available reasonably priced (don't go
spending lots of money on SCSI if you don't need to) hard drive
controller systems and hard drives. Also make sure you have the latest
drivers. I'm quite happy with the performance of my tower systems IDE
100 controller and hard drives. Much less so with my laptop which is an
IDE 33.
For more information see ACC2000: Saving Objects in DB Slower Than in
Earlier Versions - 246306
Place backend MDB on the root of the network share rather than several
folders down.
The problem is likely related to server security as each directory you
navigate must be checked against the domain security system. This may
be particularly acute in combo boxes and subreports when using a FE/BE
system as I've noticed these appear to be poorly optimized.
Alternatively I'd suggest having the network people setup a share right
on the directory of your BE for your use. Instead of using
\\Server\Dir1\Subdir2\Subdir3\subdir4\subdir5\back end.mdb you'd use
\\server\Sharesubdir5.
They can can append a $ to the end of the share name to make it a hidden
share so as to not confuse people. You would also need to use a $ at the
end of the share name as well. As in \\server\Sharesubdir5$.
I'd read a credible posting a number of years ago indicating that
someone who was working on a dialup networking analyzed the packets and
realized this was a big problem. Novell report the same problem in MS
access database run from NetWare server excessively slow. However this
could have been fixed in newer Novell OSs as well as various service packs.
There was a posting the same day I wrote the above paragraph indicating
this is still a problem. Access 2000/Windows 2000-Slow
Performance....kind of solved
PB stated that shortening the path from 75 characters to 31 characters
and removing four directory levels changed "from 50 seconds to load for
the first time and was accelerated to unbelievable about 15 seconds."
"About 3 1/2s instead of 15s is a real progress." They also shortened
the name of the back end.
Slower performance in Access-based or Jet database-based programs after
you upgrade from Windows NT 4.0 to Windows 2000 or to Windows XP - 891176
Shorten the name of the backend MDB
Yes, hard as this is to believe this has also been posted as helping.
Also see the above mentioned 891176 KB article.
Virus scanning
Ensure your virus scanning software only checks local drives and not
network drives. One report stated twenty second queries took five
minutes or fifteen times as long. Let the server's ant-virus software
monitor the server.
Antivirus software update
If the slowdown is sudden with no obvious changes in environment, either
software or hardware, then take a look at your anti-virus software. A
recent update may have accidentally added MDB scanning or otherwise do a
bad job in scanning MDB files. For example each time a client FE starts
up and opens a connection to an MDB file on the server then the
antivirus software scans the entire backend MDB on the server.
(One particular software vendor mentions this as a problem on
2003/01/17. However I'm not mentioning the vendor specifically as this
is a problem that could've happened to any vendor. And it should be
fixed by the time you read this.)
System utilities
The following is but one example. There are others I've seen in the
past but didn't think to record them.
Windows and applications run slower or stop responding after installing
GoBack 3.x Personal Edition
Outlook 97 Journaling
It's been so long since I've seen this problem I'd forgotten about it
until a recent newsgroup posting. Outlook 97, by default, saves a log
of all changes to all Word 97, Excel 97, probably PowerPoint 97 and
certainly Access 97. See OFF97: Opening and Closing Programs or Files
May Be Slow -167081. Also see OL97: Office Programs Stop Responding
While Outlook Is Busy - 167975 and OL97: Outlook Starts Slowly with
AutoJournal Feature - 166850 for hints of just how bad this can be.
Queries up to five times slower if user defined functions and Jet 4.0
SP4 or 5
ACC2000: Queries Slower After You Install MS Jet 4.0 SP4 or SP5 - 302496
You shouldn't be using Jet 4.0 SP4 or 5 anyhow because SP6 is much
more stable.
Find and replace
Try clearing the check box for "Search Fields As Formatted" in the Find
and Replace box. (New2003-09-06)
Use of DSum, DCount, DLookup, etc in form after splitting
One recent posting indicated that performance really slowed down after
splitting the MDB into a FE/BE. Turns out the problem was using a DCount
in the form. Replace these with your own custom function. Thanks to
Susan for posting the solution to her problem.. (New2003-11-06)
Miscellaneous Performance Suggestions
Use UNION ALL in Union queries rather than just UNION.
Delete and recreate table links rather than refreshing the links. In
some situations Access can cache too much information about the link
connection slowing things down excessively. The problem I have with
this approach is if the routine is interrupted for whatever reason you
can lose the link to the table(s) you have removed but not yet
recreated. This may be particularly useful after converting an app
from Access 97 to newer.
Links to tables on MDB which are not accessible. You may have some
tables linked to a secondary MDB for which the server is no longer
available or drive letter is no longer mapped.
Various Novell clients, including 3.1 sp2, cause Database query in MS
Access is unacceptably slow. Note that some Novell clients can also
cause corruptions. Some older MS Novell client versions, 2.10, 2.11,
2.5, can cause performance problems; MS Access and slowness - TID2946448
Inserting/Updating/Deleting lots of records using VBA code? Then HOWTO:
Speed Up Data Access by Using BeginTrans & CommitTrans - 146908 may help.
Wireless Access Point channel conflict You shouldn't be using Access
over a wireless network as wireless networking is prone to momentary
interruptions. And if the users are indeed mobile it will get worse.
That said see if there is another wireless network on your same channel.
If so there could be lots of traffic conflicts.
Shared file access is delayed if the file is open on another computer -
150384 (New2006-01-17) This has been reported several times as
helping with performance however I haven't yet tried it. What the
article doesn't make clear is that the registry settings are only
changed on the server. This does apply in small network scenarios
where a Windows NT 4.0/2000/XP workstation is used as a server.
Thanks to Jan from CZ for reminding me about this KB article.
Microsoft Knowledge Base Articles.
The following Microsoft articles address performance improvements that
can be obtained when using Access 2000. Each of these suggestions should
be reviewed and applied as appropriate to obtain maximum performance
from Access 2000. They will almost certainly help in Access 2002 and
newer as well. Some will make a difference in Access 97 too.
* ACC2000: Tips for Improving Sub form Performance - 209113
* ACC2000: How to Optimize Queries in Microsoft Access 2000 - 209126
* ACC2000: How to Speed Up Iterative Processes in Visual Basic - 210408
* ACC2000: Opening Form 100s of Times Affects System Resources - 248910
* HOWTO: Improve Performance of Applications Using Jet 4.0 - 240434
* Slow Performance When User Opens an Object with Name AutoCorrect
Enabled - 290181
* How to optimize Office Access and Jet database engine network
performance with Windows 2000-based and Windows XP-based clients -
889588 (New2005-05-17)
*
Your access to network resources is slower in Windows XP than in
earlier versions of Windows This fix is included in the massive
Windows XP SP2 patch.
*
High Rate of Collisions on 100-Megabit Networks - 315237 While
unlikely definitely worth checking. On the subject of collisions you
should also check the network hub/switch. If the collisions light is
flickering much then have a switch put in place. Now you haven't been
able to buy a hub for a few years but there could still be some old junk
sitting around forgotten about.
[ Access | Main ]
Comments email Tony Search Contact Privacy Policy
Website copyright © 1995-2006 Granite Consulting