Getting the most from your queries

The performance problems of many Access applications result from query design. Database applications are all about looking at and working with data, and queries are the heart of determining what data to look at or work with. Queries are used to bind forms and reports, fill list boxes and combo boxes, make new tables, and perform many other functions within an Access application. Because they're so widely used, optimize your queries is extremely important.

A query that is properly designed can provide results minutes to hours faster than a poorly designed query that returns the same result set. Consider the following:

• When designing queries and tables, you should create indexes for all fields that are used in sorts, joins, and criteria fields. Indexes enable Jet to quickly sort and search through your database.
• When possible, use a primary key in place of a regular index when creating joins. Primary keys don't allow nulls, and they give the query optimizer more ways to use the joins.
• Limit the columns of data returned in a select query to only those you need. If you don't need the information from a field, don't return it in the query. Queries run much faster when returning less information.
  Tip: If you need to use a field for a query condition and it isn't necessary to display the field in the results table, deselect the View check box to suppress displaying the field and its contents.
• When you need to return a count of the records returned by an SQL statement, use Count(*) instead of Count([FieldName]) because Count(*) is considerably faster. Count(*) counts records that contain null fields; Count([FieldName]) checks for nulls and disqualifies them from the count. This means that Count doesn't count records that have a null in the specified field.

Tip: You may also replace FieldName with an expression in the Count function, but this slows down the function even further.

• Avoid using calculated fields in nested queries. A calculated field in a subordinate query considerably slows down the top-level query. You should use calculated fields only in toplevel queries, and even then, only when necessary.
• When you need to group records by the values of a field used in a join, specify the Group By for the field that is in the same table that you're totaling. You can drag the joined field from either table, but using Group By on the field from the table that you're totaling yields faster results.
• Domain aggregate functions (such as DLookup or DCount) that are used as expressions considerably slow down queries. Instead, you should add the table to the query or use a subquery to return the needed information.
• As with VBA code modules, queries are compiled. To compile a query, the query optimizer evaluates the query to determine the fastest way to execute the query. If a query is saved in a compiled state, it runs at its fastest speed the first time that you execute it. If it isn't compiled, it takes longer the first time because it must be compiled, but then it runs faster in succeeding executions. To compile a query, run the query by opening it in Datasheet view and then close the query without saving it. If you make changes to the query definition, run the query again after saving your changes, and then close it without saving it.
• If you really want to squeeze the most out of your queries, experiment by creating your queries in different ways (such as specifying different types of joins). You'll be surprised at the varying results.