2003-12-17)(Last updated 2013-01-05)
|
|
Some of these were originally suggested by Frank Miller of Microsoft Support and have been extensively updated and added since. Almost all of these tips also apply to Microsoft Access 97 and all newer versions of Access.
The most common performance problems in Access are:
- LDB locking which a persistent recordset connection fixes
- Subdatasheet Name property set to [Auto] should be [None]. (Access
2000 and newer. See my note in the section below.)
- Track name AutoCorrect should be off. (Access 2000 and newer)
Other reasons are
- Creating indexes for filtering and selection criteria
and sequencing.
- Speed up
your Access Forms by loading the form, subform, combobox and listbox
record sources at run-time.
- New format of Access 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
- Disable SMB Signing
- How to speed up complex forms
and reports with many records each with subreports.
- Wireless networking - Don't do it
- Set Can Grow and Can Shrink on forms section properties to No
- Access 2007 design view performance on Vista vs XP
- Windows Vista, ODBC and SQL Server 2005
- Playing music severely degrades network transfer performance in Windows
Vista
-
Hewlett-Packard (HP) machines
with Digital Persona Biometric device
When every user is slow opening up the front end (FE) database file before the first line of code is run then it likely needs a decompile.
Any of the following tips can also apply in this situation. But in particular the SubDatasheet Name property set to nothing can be a problem as it appears Access 2000 will default this value to [Auto].
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.
ACC2000: Slower Performance
on Linked Tables - 261000 and
Slow
performance on linked tables in Access 2002, in Office Access 2003, and in
Office 2007 indicates that if the database has many linked tables that
also have many relationships, and the table that you are opening has its subdatasheet 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 subdatasheet 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.
Note that if you have Name AutoCorrect on this will change the Subdatasheet property back to [Auto] for each table to which you make a change. Thanks to Tom Wickerath for pointing this out.
However based on my personal research I'm no longer convinced this is a serious problem. It may be a problem in datasheet view but doesn't appear to be a problem on continuous forms.
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.
There are many web pages on this topic both Access specific and general relational database systems so I'm not going to get into the details here.. However you should have indexes on all fields that are used for filtering and selection criteria and sequencing on forms and reports. Access does automatically create indexes on primary keys, foreign keys and other fields as per the Autoindex on Import/Create in the Tools >> Options >> Tables/Queries tab.
The problem with too many indexes is that this will slow down record insert and field updates as the indexes have to be updated. This can be significant. For example when doing a bulk loading of records, such as when converting a system, it can be very beneficial to delete all the indexes, load the records and create the indexes fresh again.
Note that in some performance tuning I just did for a client adding an index on a boolean field in a "master" table containing 800 records dropped the form load time from 30 seconds to 3 seconds. The continuous form was based on a table with 800,000 records although only a few hundred or thousand records were retrieved and displayed.
2003-12-17)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
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\backend.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.
Yes, hard as this is to believe this has also been posted as helping. Also see the above mentioned 891176 KB article.
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.
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.)
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
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.
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.
Try clearing the check box for "Search Fields As Formatted" in the Find and Replace box.
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.
SMB signing can decrease performance so much that a simple file copy can take four times as long. This has been reported as somehow being turned on by group policy during some maintenance by Sandra Daigle, former Access MVP. Microsoft network client: Digitally sign communications (always) Although you'll want to disable it of course.
The following newsgroup posting indicated that it took almost fifty times as long to open an MDB due to SMB signing. Very slow response when MDB stored on Small Business Server 2003 network share This article shows a registry key on the server that can be updated. (The window available showing the exact registry key was truncated in IE7 when I updated this article. However if you do the cut & paste to notepad you will get out the entire key. The article displayed properly in Firefox.)
Also see Issues that you may encounter when you open or are using a Microsoft Office Access database on a computer that is running Windows Vista - 935370
"If I disable autotuning on the TCP stack in Windows Vista I no longer suffer from this problem. ... until I upgrade to Windows server 2008 I won't be enabling autotuning on my Vista client." See the thread Access 2007 Design view performance on Vista vs XP initiated and finished by cpf_profiles See Receive Window Auto-Tuning on Vista for more technical information including KB articles.
If your system is painfully slow when updating forms or
reports in design view try creating a local printer and setting that as the
default printer. This could be a PDF printer such as the open source
PDFCreator
or free
CutePDF Writer. This may be more applicable to Access 2007 than
previous versions. Alternatively just create a generic printer.
(2009-04-20)
For the best connection string for a DSN-less connection to SQL Server 2005,
switch providers to the SQL Native Client with the following:
"ODBC;Driver={SQL Native Client};Server=<servername>;DATABASE=mydatabase;Trusted_Connection=Yes"
by Rick Byham of Microsoft. See the thread
Vista, ODBC and SQL Server 2005
Thanks to David for the posting Vista network problem And an official Microsoft blog posting by Mark Russinovich Vista Multimedia Playback and Network Throughput
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 Networking You shouldn't be using Access over a wireless network as wireless networking is prone to momentary interruptions and can cause corruptions. And if the users are indeed mobile it will get worse. Instead use Terminal Server or SQL Server or similar. That said see if there is another wireless network on your same channel. If so there could be lots of traffic conflicts. Even adjacent channels can overlap their frequency ranges
I also found the speed of 54 mbps wireless networking to be about five times slower than 100 mbps wired networking. Granted this may have been caused by a consumer grade wireless access point.
Shared file access is delayed if the file is open on another computer - 150384 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.
User profile Rohan reported one computer was slow on the network compared to the others. The IT department stated it was the user profile. I'm assuming the IT department deleted the profile and started fresh again.
Set Can Grow and Can Shrink on forms section properties to No. Note that this is not the control properties but the section properties. Reported in the thread slow form rendering when using Access 2007 by cranes_fan at yahoo.com.
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.
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.
Optimizing Microsoft Office Access Applications Linked to SQL Server
HP machines running
Auto FE Updater
distribute new and updated Front End databases to your users with several mouse
clicks.
Granite Fleet Manager
- the best designed fleet maintenance tracking and
management system available
| Comments email Tony | Search | Contact | Tony's Blog | Privacy Policy | Table of Contents |
Website copyright © 1995-2013 Tony Toews |