Improving the performance of Access Applications

Access applications are common throughout businesses world wide and work perfectly well for small applications used by a handful of users. However when the number of people requiring simultaneous access to the system grows the performance of these applications quickly grinds to a halt and becomes near unusable. The best solution in this scenario is to rewrite the software using a Client-Server / Multi-Tear model but doing so can take more time than a business can afford.
So what else can be done to lift the performance of an ailing Access Application?

Multi-user Access applications operate in a manner not dissimilar to how a stand alone application works and the strategy for improving performance is much the same. Most encountering the problem will first try to increase the amount of bandwidth between the 'client' and 'server'. However unless the application is being used over a low bandwidth connection (such as ADSL) or the application is requesting large amounts of data which would take several seconds to transfer little, if any, noticeable improvement is made. The bottle neck in applications of this type is the software itself and the way in which it works.

If after compacting and cleaning an Access database you still find your application is still running intolerably slow then you have reached a tipping point whereby the hardware you are using no longer has the horsepower needed.
Access applications generally start to slow down because each of the 'clients' maintain shared or exclusive locks on parts of the datafile while it is working on the data. As the number of simultaneous users grow the more locks are encountered forcing the application to wait until it is released. Improving performance at this stage must focus on reducing the time a lock is held on a part of the file.

A good place to start is with the machine hosting the datafile. Should this machine have an old hard disk which has slow seek times and low bandwidth then replacing this hardware with a faster drive (possibly a RAID 5 volume) will have a noticeable effect. Actually hosting the datafile itself does not require much processing power, only enough to load the data from the hard disks and place the data on the network. Upgrading the machines processor and/or mainboard, except for extremely underpowered systems, will do nothing for the performance of the Access Application.

You should next move to all systems making use of the application. It is these computers which request the locks on the datafile and, while the amount of time they hold a lock on the file depends on what the application is doing, it is largely dictated by the amount of processing power available. Most Access applications do not store data locally on the 'client' but read and write everything to the 'server' so replacing hard disks on these machines will often have no impact on an Access application unless the hard disk itself is causing the machine's performance to suffer.
Here the best gains are to be made by increasing the amount of processing power and memory available. This will allow the Access application to load more data into memory without using the swap file so heavily and execute the query in less time – therefore reducing the amount of time a lock is required on the datafile.
It is important to note that just one machine which does not have enough processing power can affect everyone else using the application as an exclusive lock (generally used while writing data to the file) forces everyone to wait until the lock is released. So if an underpowered machine holds such a lock the higher powered machines must wait until the slower machine completes its operation.

Ultimately there is a limit to what upgrading hardware can do to improve performance. If upgrading the hardware of computers on your network is still not delivering the results you need you are left with only one option – to run the application on one computer only and use RDP to access the application.
The server hosting the application will require the most powerful hardware available but noticeable gains will be made by running the application off one machine. This works simply because most of the changes made to the file is done in the servers memory which is then committed to the disk a short time later. Reading and writing of the file is handled much better from a single machine than from many machines when data cannot be cached by the machine hosting the data.

Article by tc software.