Ways to optimize query performance

There are many things you can do to make your query run faster. In addition to the following tips, you can use the Performance Analyzer to analyze specific queries in your database. For information on using the Performance Analyzer, click .

• 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.

  Note   The Microsoft Jet database engine automatically optimizes a query that joins a Microsoft Access table on your hard drive and an ODBC server table if the Microsoft Access table is small and the joined fields are indexed. In this case, Microsoft 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.

• 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 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 more information, click .
  
  
• Avoid calculated fields in subqueries. If you add a query containing 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 "Hello" or "Goodbye" 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, in the Northwind sample database, 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. For more information on the First function, click .

  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.

  Use criteria expressions that are optimizable. For information on optimizing criteria expressions using Rushmore technology, click .

• 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.
  
  
• Index the fields you use for sorting.
  
  
• 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 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. For more information, click .
  
  
• Use the Between...And, the In, and the = operators on indexed fields.
  
  
• For bulk update queries against ODBC data sources, optimize performance on the server by setting the FailOnError Property to Yes. For more information, click .

Note   For tips on optimizing external SQL database performance .