Performance I - amazing Access

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Don't AutoCorrect ■■■

In Tools, Options, General, uncheck "Track name AutoCorrect info". You'll find huge improvements in the time it takes your database to do virtually anything. Note that this function (which cascades changes you make to object names) can be useful in a development environment, but should be turned off in a production environment.

Access help topic: AutoCorrect options

Compact and repair ■■■

Compact and/or repair your database regularly (in Access 2000 and above, one action does both). Compacting your database reclaims unused space and makes nearly everything faster. Also, compact anytime you import/delete objects in your database, or compile and save VBA code. If you find this an onerous chore, investigate admin scheduling tools like the Handy Access Launcher. And don't forget to compact your MDW files sometimes too!

Access help topics: Compact, Repair

Analyze this! ■■

Access has its own performance analyzing capabilities (Tools, Analyze, Performance). Select All Objects Types, check Select All and hit OK - Access will recommend optimization techniques for anything and everything in your database, which might give you some ideas. Be wary of implementing too many indexes though - Access is forever recommending them and you might have good reasons not to put too many in.

Access Help topic: Performance Analyzer

Power up the Jet engine ■■

There are many Registry settings you can play with to optimize the Jet database engine, including these:

• Threads: the number of operating system threads available to the Jet database engine.
• MaxBufferSize: the data buffer used by Jet.
• UserCommitSync: tunes the performance of explicit transactions.
• ImplicitCommitSync: tunes the performance of implicit transactions.
• FlushTransactionTimeout: tunes the performance of asynchronous write operations.
• ExclusiveAsyncDelay: tunes the performance of asynchronous mode writes to exclusively opened databases.
• SharedAsyncDelay: tunes the performance of asynchronous mode writes to databases opened for shared access.
• PageTimeout: the delay for checking other user's changes to the database.
• LockDelay: how long Jet waits between retries on locked pages in a shared database.
• MaxLocksPerFile: the maximum number of locks can be placed on a database.
• RecycleLVs: determines how memo, OLE and hyperlink data pages are recycled.

Now I don't pretend to know the best settings for all of these, and if you're unsure you're advised to leave the defaults alone (this is good advice for any changes you might ever make to any part of the Registry). But if you do want to experiment, these keys can be found in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0 (for Jet 4) and can be edited either with RegEdit or, if you're familiar with good old DAO, the DAO SetOption command in VBA (or SaveSetting in later versions of Access). Don't forget to back-up the Registry before you start experimenting.

Access Help topics: Registry, SetOption method, SaveSetting method

Do the splits! ■■■

You can improve performance (and simplify maintenance) by splitting your database in two. The "application" front-end (FE) of your database holds all objects except tables, and is linked to the "data" back-end (BE) that contains the actual tables. This can give particular gains in a multi-user, networked environment, as the FE can be installed on each user's workstation with only the BE on a server. Later versions of Access even have a wizard to help you make this split (Tools, Database Utilities, Database Splitter).

Access Help topic: Splitting an Access database

Keep your Options open! ■■

Avoid lock conflicts by experimenting with the Refresh Interval, Update Retry Interval, Number of Update Retries and ODBC Refresh Interval Settings - you'll find these in Tools, Options.

Be Exclusive ■

If you are opening a database that no other users need to use, open it in Exclusive mode. To do this, check the Exclusive checkbox in the Open Database dialog.