Performance VIII - hasty hardware

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.

Make the most of the cache ■

Use cache memory wherever possible when using external SQL data sources. Access forms and reports have automatic caching mechanisms. When using recordsets in your code, try using the CacheStart, CacheEnd and FillCache methods to maximize cache effectiveness.

Access Help topic: FillCache method

Increase RAM ■■

Increase the amount of RAM on your computer. The minimum acceptable amount Access 2000 is 64MB, although 128MB is preferable and 256MB is better yet. The basic message here is: the more RAM, the better.

Avoid NTFS compression ■

If you are using NTFS compression, move your database to a non-compressed drive. NTFS adds a significant amount of overhead during disk operations, and Access applications are very disk-intensive.

Get a faster processor ■■

Okay, so it's stating the obvious but the faster your PC's processor the better. As little as 100MHz can give fair performance for 95/97; I'm currently running 2000 with 2.3GHz. Like RAM, the message here is: the more the merrier!

Keep enough local disk space free ■

Access requires a fair amount of disk space to operate, especially with large databases. Operations such as running large action queries, adding lots of data, importing data, and compiling and saving module code can use a lot of additional space on a temporary basis. Additionally, transactions, and compacting the database can use a lot of disk space. As a minimum, try to keep ten times the size of your largest database available in free local storage.

Optimize that disk ■

Keep your disk in clean and tidy by deleting surplus files and emptying your Recycle Bin. Look at Disk Defragmenter and Disk Cleanup within Windows, or investigate clean-up tools like the always excellent (and snappily titled) freeware Crap Cleaner.

Virtual Memory ■

Although the Windows default virtual memory settings are okay for most systems, you may want to consider changing the settings if you don't have a lot of space on the disk being used for virtual memory, or if you have a local drive that is faster than the one being used by default for virtual memory. Exercise caution and change in small increments though.

Strip your wallpaper ■

If your PC is really struggling, you may find the tiniest performance hike by removing desktop wallpaper, or switching to a small tiled bitmap.

Avoid disk compression software ■

Don't use disk compression software on the drive where you have installed Access, or where your database file(s) are located. Disk compression has a big negative impact on db performance.

Disable Office startup programs ■

I can't guarantee this applies to all versions, but in my experience Office installs one or more startup programs in your Start Menu Startup folder (e.g. Office Fast Start and Office Find Fast indexing utilities). Consider removing these, as they use memory. Find Fast runs continuously, so is especially damagaing to performance.

Don't journal ■

If you have Outlook installed (not Outlook Express), disable journalling unless it is absolutely essential to you - otherwise Outlook watches out for every time you open an Access database, and logs it, inevitably slowing things down. To disable this annoying function, select Tools, Options, Preferences, Journal options from the Outlook menu.

Don't spring too many leaks ■

I don't care what our friends at Redmond might say, almost all Windows applications "leak" memory. This is due to data structures, variables, and other memory consumers that are not correctly released by the application. By closing and reopening Access now and again, you allow Windows to reclaim any leaked memory.

Install Windows locally ■

Your OS should be installed on a local fixed drive on your computer. Because of the number of components involved in running Windows, a network installation causes relatively worse performance, especially for resource-hungry database app's.

Keep databases local too ■

Where possible, keep your databases on a local drive instead of on a network drive - 9 times out of 10, local disk access is faster than network access.

And install Access locally too ■

Most applications designed for PCs are faster when run from local drives than from a networked location, and Access is no exception.

Close other applications ■

Okay, it's an obvious one but you can free up memory by closing applications that you aren't using.

Review running Services ■

A whole host of services are running all the time in the background of your Windows OS - stop any you don't need (but set them to start automatically when needed) to free up system resources, but do so with caution and understanding - it's worth taking a look at this basic but comprehensive services guide before you change anything.