Improve query performance

Databases, tables, and indexes

• Compact your database. Compacting can speed up queries because it reorganizes a table's records so that they reside in adjacent database pages ordered by the table's primary key. This will improve the performance of sequential scans of a table's records because the minimum number of database pages will have to be read to retrieve all of the records. After compacting the database, run each query to compile it using the updated table statistics.
• Index any field used to set criteria for the query and index fields on both sides of a join, or create a relationship between these fields. When you create relationships, the Microsoft Jet database engine creates an index on the foreign key if one does not already exist; otherwise, it uses the existing index.

  The Jet database engine automatically optimizes a query that joins a Microsoft Access table on your hard drive and an ODBC server table if the Access table is small and the joined fields are indexed. In this case, Access improves performance by requesting only the necessary records from the server. Make sure tables you join from different sources are indexed on the join fields.

• Index the fields you use for sorting.

• Use the Between...And, the In, and the = operators on indexed fields.

Fields, calculations, and criteria

• When defining a field in a table, choose the smallest data type appropriate for the data in the field. Also, give fields you'll use in joins the same or compatible data types, such as AutoNumber and Number (if the FieldSize property is set to Long Integer).
• When creating a query, add only the fields you need. In fields used to set criteria, clear the Show check box if you don't want to display those fields.
• If you use criteria to restrict the values in a field used in a join between tables with a one-to-many relationship, test whether the query runs faster with the criteria placed on the "one" side or the "many" side of the join. In some queries, you get faster performance by adding the criteria to the field on the "one" side of the join instead of the "many" side.
• Avoid calculated fields in subqueries. If you add a query that contains a calculated field to another query, the expression in the calculated field may slow performance in the top-level query. In the example below, query Q1 is used as the input for query Q2:

  Q1: SELECT IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed") AS X FROM MyTable;
  Q2: SELECT * FROM Q1 WHERE X="Order Confirmed";

  Because the IIf expression in Q1 cannot be optimized, Q2 also cannot be optimized. If an expression that can't be optimized is nested within a subquery, the entire query cannot be optimized.

  An alternative way to construct the query is as follows:

  Q1: SELECT * FROM MyTable WHERE MyColumn = "Yes";

• If expressions are necessary in the output, try to place them in a control on a form or report. For example, you could change the previous query into a parameter query that prompts for the value of MyColumn, and then base a form or report on the query. On the form or report, you could then add a calculated control that displays "Order Confirmed" or "Order Not Confirmed" depending on the value in MyColumn.

  Construct the query as follows:

  PARAMETERS [To see confirmed orders, enter Yes. To see unconfirmed orders, enter No.] Text;
SELECT *
FROM MyTable
WHERE MyColumn = [To see confirmed orders, enter Yes. To see unconfirmed orders, enter No.];

  In the calculated control on the form or report, enter:

  =IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed")

• When grouping records by the values in a joined field, specify Group By for the field that's in the same table as the field you're totaling (calculating an aggregate on). For example, if you create a query that totals the Quantity field in the Order Details table and groups by OrderID, it's recommended that you specify Group By for the OrderID field in the Order Details table. If you specify Group By for the OrderID field in the Orders table, Microsoft Access must join all the records first and then perform the aggregate, instead of performing the aggregate and then joining only the necessary fields.

  For greater speed, use Group By on as few fields as possible. As an alternative, use the First function where appropriate.

  If a totals query includes a join, consider grouping the records in one query and adding this query to a separate query that will perform the join. This improves performance in some queries.

• Avoid restrictive query criteria on calculated and nonindexed fields whenever possible.

Expressions and Rushmore optimization

Overview of Rushmore optimization

Microsoft Access can optimize simple expressions or complex expressions in the Criteria row of the query design grid, or in a WHERE clause in an SQL SELECT statement. For certain types of complex expressions, Access can use Rushmore, a data access technology used by the Microsoft Jet database engine, to achieve a greater level of optimization. In addition, the Count function is highly optimized for queries using Rushmore.

Rushmore queries will work with Access tables, as well as with Microsoft FoxPro and dBASE tables (.dbf files). You can't use Rushmore with ODBC data sources, since Access sends these queries to the ODBC data source instead of processing them locally.

Simple optimizable expressions

A simple optimizable expression can form an entire expression or can appear as part of an expression. A simple optimizable expression takes one of the following forms:

IndexedField ComparisonOperator Expression

[Order Date] = #09/15/96#

or

Expression ComparisonOperator IndexedField

#11/1/96# < [HireDate]

In a simple optimizable expression:

• IndexedField can either be a field that has its own index or a field that's the first field in a multiple-field index.

• ComparisonOperator must be one of the following: <, >, =, <=, >=, <>, Between…And, Like, In.

• Expression can be any valid expression, including constants, functions, and fields from other tables.

If you have created indexes for the LastName, Age, and HireDate fields in the Employees table, the following are simple optimizable expressions:

[LastName] = "Smith"

[Age] >= 21

#12/30/90# < [HireDate]

Employees.[LastName] = Customers.[LastName]

[LastName] In ("Smith", "Johnson", "Jones")

[Age] Between 18 And 65

Complex expressions

A complex expression is created by combining two simple expressions with the And or the Or operator. A complex expression takes one of the following forms:

SimpleExpression And SimpleExpression

or

SimpleExpression Or SimpleExpression

A complex expression is fully or partially optimizable depending on whether one or both simple expressions are optimizable, and which operator you used to combine them. A complex expression is Rushmore-optimizable if all three of the following are true:

• The expression uses And or Or to join two criteria.

• Both criteria are made up of simple optimizable expressions.

• Both expressions contain indexed fields. The fields can be indexed individually or they can be part of a multiple-field index.

Note   You can optimize multiple-field indexes if you query the indexed fields in the order they appear in the Indexes window, beginning with the first indexed field and continuing with adjacent fields (up to and including 10 fields). For example, if you have a multiple-field index that includes the LastName and FirstName fields, you can optimize a query on LastName or on LastName and FirstName, but you can't optimize a query on FirstName.

The following different combinations of simple expressions in query criteria indicate the level of query optimization.

Fully optimizable query result (using Rushmore)

• (optimizable expression) And (optimizable expression)

• (optimizable expression) Or (optimizable expression)

Partially optimizable query result (doesn't use Rushmore)

• (optimizable expression) And (not optimizable expression)

Not optimizable query result

• (not optimizable expression) And (not optimizable expression)

• (not optimizable expression) Or (not optimizable expression)

• Not (optimizable expression)

• Not (not optimizable expression)

You can also use parentheses to group combinations of simple expressions. The preceding rules also apply to combinations of expressions grouped with parentheses.

Once you've combined simple optimizable expressions into complex expressions, these complex expressions can, in turn, be combined to form even more complex expressions that might be optimizable according to the preceding rules.

These examples assume that you created indexes for the LastName and HireDate fields (simple expressions using them are optimizable) but not the MiddleInitial or FirstName fields (simple expressions using them are not optimizable). The following examples of combined simple expressions show the extent to which the result is optimized.

Fully optimizable query result (using Rushmore)

[LastName] = "Smith" And [Hire Date] < #12/30/90#

[LastName] = "Smith" Or [Hire Date] < #12/30/90#

Partially optimizable query result (doesn't use Rushmore)

[LastName] = "Smith" And [MiddleInitial] = "C"

[FirstName] = "Terry" And [MiddleInitial] = "C"

Not optimizable query result

[LastName] = "Smith" Or [MiddleInitial] = "C"

[FirstName] = "Terry" Or [MiddleInitial]= "C"

General suggestions

• If the RecordSource property for a form or report is set to an SQL statement, save the SQL statement as a query, and then set the RecordSource property to the name of the query.
• For bulk update queries against ODBC data sources, optimize performance on the server by setting the FailOnError Property to Yes.
• If your data doesn't change often, use make-table queries to create tables from your query results. Use the resulting tables rather than queries as the basis for your forms, reports, or other queries, and make sure you add indexes according to the guidelines recommended here.
• Avoid using domain aggregate functions, such as the DLookup function, to access data from a table that's not in the query. Domain aggregate functions are specific to Microsoft Access, which means that the Microsoft Jet database engine can't optimize queries that use them. Instead, add to the query the table that the function was accessing, or create a subquery.
• If you are creating a crosstab query, use fixed column headings whenever possible.