Making SQL queries run faster

Queries are very useful in Microsoft Access. You can use them to find and retrieve information and to make changes to large amounts of data. A badly-designed query can be slow on a large database. Try these techniques to make them run more quickly.

Query Design

A complicated topic but the basic guideline is to process as little data as possible:

• Only include the fields that you need.
• Only include the records that you need.

Double-clicking on the asterisk in the Query Designer to select all fields is quick and easy. You might do it thinking that the extra fields will be useful at some later stage in the design. Resist the temptation and take care to pick just the fields that you want.

Compact

Access databases contain a lot of wasted space which grows every time that you add, edit, or delete a record. Select Compact Database from Database Utilities on the Tools menu to reclaim the wasted space in the database.

The process moves data around on disk and may take several minutes on a large database. Nobody can view the data whilst the compaction is running so you should make sure that you are the only user before starting. If another user has the database open then you'll see a warning message and compaction will not start.

Indexes

Indexes allow the Access SQL engine to find records more quickly. You should create an index for every fields which you use in any of these situations:

• linking two tables together
• selecting records
• sorting records into order

Use the Design View of a table to create an index.

Wild cards

Be careful when using a Wild card in a query. Wild cards add flexibility but they can make the selection very much slower. If the target of the SQL selection criterion ends with an asterisk like this:

then Access will be able to use an index to find all entries starting with 'F' and no harm is done.

If the wild card is anywhere other than at the end of the expression then the Access database will not be able to use an index and the selection will have to be done by an exhaustive search. Comparisons like these will be very slow:

No index can help you look up all entries ending with 'F' or whose third letter is 'F' so Access must fall back on a brute force search through the entire table.

Optimiser

Access includes a Query Optimiser which works out the most effective strategy for retrieving the records. The information that the Optimiser uses can get out of date and this might make the Optimiser choose the wrong strategy, especially if many records have been added or changed. Opening the query in Design View and saving it will update the statistics.