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.
Subdatasheet Name ■■■
The default setting for the Subdatasheet Name property of a table is [Auto]. Set it to [None] for all your tables that don't have or need sub-datasheets.
Access Help topic: SubdatasheetName Property
Persist links to ODBC tables ■■
If you are going to be accessing a SQL database table, link the table permanently. This makes opening that table much faster in subsequent attempts. This is because linked tables cache a lot of information about the source table in your database, making the retrieval of the same structural information unnecessary after the linked table is created. A good way to achieve this is to create a linked table with one small field, then create a small form with a single control that is bound to this form. When your database opens, open the form hidden and keep it open all the time.
Access Help topic: linked tables
Normalize data ■■■
Correctly normalize your table structure to remove redundancies and repeated data. You can use the built-in Table Analyzer (Tools, Analyze, Table) to help you split your tables into related tables if you're having problems. A better approach is to understand the theory...
Access Help topic: Table Analyzer
But occasionally you might need to Denormalize ■
Although you should normally normalize (!) your data for best performance, you may want to experiment with denormalizing some of your data if you have frequently run queries whose joins could benefit from such data restructuring.
Don't forget your keys! ■■■
In my view, every table should have a primary key; this allows your db to quickly access specific records. Also, you can't create secondary indexes in a table until you've defined a primary key.
Access Help topic: About primary keys
Choose the right types ■■
By choosing the most efficient data type for each field, you can decrease both the disk space used to store data, and the time it takes to retrieve, manipulate, and write data. The general guideline is to choose the smallest data type possible to store a particular type of data. In other words, if you have a numeric field that is always between 1 and 100 set it as a Byte data type, not the default Long Integer - a Byte is 1 byte in size, whilst a Long Integer is 4 bytes.
Access Help topic: FieldSize property
Enjoy beautiful relationships ■■
Relationships allow joins between tables to work faster. Also, relationships that implement referential integrity allow updates and deletions to be cascaded without having to write code to do this. Bonus!
Access Help topic: Relationships
Indexing - get the balance right ■■
If you search on a field, or use it in a join, index that field for big performance gains. Remember though that every index you create adds to the time it takes the database engine to update, delete and add records, so experiment to make sure you get the right balance. A couple of pointers: firstly, don't apply indexes to fields that contain lots of identical data (e.g. Yes/No field) as this is almost always bad news performance-wise. Also, try to keep indexes as simple as you can - when creating a multi-field index, use only as many fields as are absolutely necessary.
Access Help topic: View or edit indexes
DataEntry mode ■
If you are only going to be adding records, use the Data Entry command on the Records menu. This is more efficient for adding records because existing records are not read from the database.
Access Help topic: Data Entry mode