Performance matters, even in a locally stored single-user database. No one wants to waste time waiting for forms and reports to populate. You don't build these slow databases, users do -- but you might need to support them. Users build them and then they call you: My database is too slow! There are things you could do to help, but the good news is that you can offer a few tips that might help them improve performance on their own. All of the following tips are simple enough that most users can put them to good use with decent results.
Note: This article is also available as a PDF download.
1: Disable or eliminate subdatasheets
Access displays subdatasheets between related tables by default. Unfortunately, they slow things down. If you work a lot in Datasheet view and seeing related data is valuable, it's a great feature. Most of us, however, don't need to see data presented this way. Advise users to disable this feature, as follows:
- Open the table in Design view and then open the table's Property sheet by pressing [Alt]+[Enter] or by clicking Property Sheet in the Show/Hide group on the Design tab.
- Change the SubdatasheetName property from [Auto] to [None].
2: Let Access advise
Access has two utilities that offer suggestions for improving performance. The Performance Analyzer lists potential problems and provides suggestions for correcting them. You can analyze all objects or just specific objects in a database. Another insightful utility is the Table Analyzer. This tool will help users properly normalize their data, and a properly normalized database performs better overall. These utilities are simple to run and users should be able to apply the resulting suggestions with little or no help from you.
In Access 2007/2010, both tools are on the Database Tools tab in the Analyze group. In Access 2003, choose Analyze from the Tools menu. These utilities will usually catch something -- in a user-built database, they might find a lot. Advise users to run the Table Analyzer before building forms and reports, so they can build them on properly normalized tables.
3: Reduce the size of graphics
Embedding graphics is a surefire way to consume resources and degrade performance. Linking is usually better than embedding, but users won't always know how to link to graphic files outside the database. When this is the case, Access can reduce the bloat that comes with embedded graphics. Advise users to set the following option when they embed graphics:
- In Access 2007, click the Office button and then click Access Options. In Access 2010, click the File tab and choose Options (under Help).
- Choose Current Database in the left pane.
- In the Application Options section, click the Preserve Source Image Format (Smaller File Size) option under Picture Property Storage Format.
- Click OK.
In mdb versions (Access 2003 and earlier), Access stored two sets of each graphic, which significantly increased the size of the database -- the original and a bitmap image Access actually displayed. The Preserve Source Image Format setting stores only one file and dynamically generates a bitmap when needed. The savings are substantial, but users won't know about it. Advise users to set this option during the design/development process. It won't convert existing graphics -- it works only with new graphic files, as you embed them.
4: Split the database
By default, Access stores your data and your user interface objects (forms, reports, and so on) together. Some developers claim that splitting these single files into two, one with the data and one with the user interface objects, improves performance. Users work in the front end, which contains the user interface objects that retrieve data, as needed, from a linked back end. There are a number of reasons to split a database, as you can read about in 10+ reasons to split an Access database, but managing the data and the database itself are greater incentives than performance. Users can sometimes improve performance by splitting a database (but that isn't always the case).
If a database has only one user, you can still split it, but tell users to store both ends on the local system to avoid the network bottleneck. The idea might intimidate some of your users, but encourage them to try it -- they'll be surprised how easy it really is.
5: Improve performance for split databases
As stated in #4, splitting a database sometimes slows things down. When that happens, there are simple things that users can do to improve performance to a linked backend:
- Keep record navigation to a minimum. Moving from record to record against data in a linked table can slow things down.
- A browsing form retrieves data across your network connection and consequently, can be slow. When entering data, set the form's Data Entry property to Yes.
- Use queries to limit the data retrieved in order to reduce the amount of data Access retrieves from the linked table(s). Limit both fields and records.
- Create local tables for static data, such as state abbreviations, ZIP codes, and so on.
6: Compact regularly
Compacting reclaims resources you free up by deleting objects and records. It also reorganizes records and updates statistics. Compacting almost always improves performance. If users don't compact regularly, the change will be noticeable. Compact And Repair Database is in the Tools group on the Database Tools tab. In Access 2003, it's on the Tools menu. Users can also have Access compact automatically, as follows:
- In Access 2010, click the File tab and then click Options (under Help); in 2007, click the Office button and then click Access Options.
- Choose Current Database in the left pane.
- Check the Compact On Close option in the in the Applications Options section.
- Click OK.
In Access 2003, do the following:
- Choose Options from the Tools menu.
- Click the General tab.
- Check the Compact On Close option.
- Click OK.
Instruct users to set this option so the database will compact every time they close it. Be sure users know to back up their database before compacting.
7: Keep controls to a minimum
Users might think that more is better, but a simple form is usually faster than a complex one with dozens of controls. A busy form can take a while to load and respond to requests. Advise users to create numerous task-specific forms instead of one complex form.
8: Base bound forms and reports on queries
Users tend to save queries for forms and reports based on more than one table. Recommend that they base all bound forms and reports on a query instead. Then, they can use the query to limit the number of fields and records used to populate the form or report. The less data, the faster the form or report will perform.
9: Be smart about indexing
Indexing isn't an art, but it can be confusing -- and over-indexing is as bad as inadequate indexing. Although there are many rules about indexing, they all boil down to one simple guideline that most users can understand: Apply an index to fields you sort or search regularly, unless the values are repetitive. If users are unsure, remind them to run the Performance Analyzer (#2).
10: Keep tables narrow
The number of records stored in a table has less impact on performance than the number of fields in the table. Advise users to keep fields to a minimum. Normalizing goes a long way (and the Table Analyzer can help there). But sometimes, even a properly normalized table can have dozens of fields. If you suspect that a wide table is a performance problem, advise the user to remove seldom-used fields to a new table. Then, force a one-to-one relationship between the tables. If the number of fields is your true culprit, this arrangement should improve performance. Recommend this solution to only your most knowledgeable Access users, though. It isn't difficult, but it does require knowledge of normalization and table design.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.