Performance III - quick queries

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

Minimize traffic ■■

Minimize the amount of data returned by your queries. Do this by structuring then to return only the fields and records needed. Reduce the number of fields returned, and put constraints on the query by using the WHERE clause. The exception to this is when doing a simple count of records, when SELECT COUNT(*) is fastest (since it doesn't have to check for Null values in a specified field).

Don't force local query processing ■

Don't use query constructs that cause processing to be done by Access on the local computer. The following query operations force the Jet database engine to perform local data processing:

• Join operations between table that are linked to different data source (i.e. a join between a SQL table and a linked Access table).
• Join operations based on query that uses the DISTINCT keyword, or a query that contains a GROUP BY clause.
• Outer joins that contain syntax that is not directly supported by the remote database server.
• The LIKE operator used with Text or Memo fields.
• Multi-level grouping and totalling operations.
• GROUP BY clauses that are based on a query with the DISTINCT keyword, or the GROUP BY clause.
• Crosstab queries that have more than one aggregate, or that have field, row, or column headings that contain aggregates, or that have an ORDER BY clause.
• User-defined functions, or functions that are not supported by the remote server.
• Complex combinations of INNER JOIN, LEFT JOIN, or RIGHT JOIN operations in nested queries.

Indexing... again ■■

Try indexing any fields in the query that are used to set criteria, and those that you use for sorting. Also, index the fields on both sides of a join (although if you create a relationship between joined fields, the index is automatically created). As mentioned elsewhere on this page, it is possible to over-index, so tread carefully!

Access Help topic: View or edit indexes

... and use Primary Key indexes whenever possible ■

Because Primary Key indexes do not allow null values, they can be faster than other indexes.

Access Help topic: About primary keys

And for non-indexed fields... ■

Try to avoid the use of the BETWEEN, AND, IN and = operators on non-indexed fields.

Only join with compatible types ■

This sounds like dating advice, but try to ensure fields that are joined in a query have the same, or compatible, data types - your queries will thank you for it, and so will your users. For reference, an AutoNumber field is a Long Integer type.

Avoid the Immediate If ■

The IIf (Immediate If) statement can be slow as it does not employ "short-circuit" evaluation, i.e. both sides of the expression are always evaluated.

Access Help topic: IIf Function

Avoid too many Functions ■

Avoid calculated fields, or fields that use expressions in subqueries - slowness is an inevitable by-product.

Use Domain Aggregate functions sparingly ■■

Things like DMax and DLookup are generally slow, so avoid them where possible. Unless desperate, try not to use them to access data from a table that is not already in your query.

Fixed column headings in crosstabs ■

Wherever possible, use fixed column headings in your crosstab queries.

If in doubt, compile! ■■

When your db is compacted its data statistics are updated. When you then run a query, these updated statistics are compiled in the query's execution plan. This sequence of events results in the fastest possible query. Before you deploy your application, compact the database, then force each query to be recompiled (which in turn causes it to use the latest statistics) - do this by opening it in design view, saving it, and then running it.

Rush-more! ■

Jet uses Rushmore query optimization where possible. Rushmore can be applied to queries run on native Access data, and on certain linked table types. To ensure that Rushmore optimizations are used whenever possible, create indexes on all fields that are used to restrict a query's output. Queries that don't contain indexes on fields used in their restriction clauses cannot benefit from Rushmore.

If you have 1:n joins... ■

...in a query with a restriction, try moving the restriction to the other side of the join. If the restriction is on the many side, try moving it to the one side. Compare performance for both versions, and choose the fastest (obviously).

The Sub-Way: sub-queries instead of joins ■

If you have a slow query with a join, try replacing the join with a sub query. In some cases, this may run faster.