Information about query performance in an Access database (209126)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000
This article was previously published under Q209126
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 97 version of this article, see 112112.

INTRODUCTION

This article discusses how to optimize query performance in Microsoft Access 2000, in Microsoft Access 2002, and in Microsoft Office Access 2003. The following topics are included:
  • Query Optimizer for the Microsoft Jet database engine
  • Query timing
  • Analyzing performance
  • Tips to improve query performance
This article assumes that your database has local tables instead of linked (or attached) tables. If your tables are linked, this information still applies. However, there are additional issues that affect query performance with linked tables. For more information about improving performance with linked tables, you can search the Microsoft Knowledge Base by using the following string:

odbc and optimizing and tables

MORE INFORMATION

Query Optimizer for the Microsoft Jet database engine

The Jet database engine contains several components, but the most important component to queries (and the most complex) is the Optimizer. The Optimizer is cost-based. This means that the Optimizer assigns a time cost to each query task and then chooses the least expensive list of tasks to perform that generates the intended result set. The longer a task takes to perform, the more expensive that task is.

To decide what query strategy to use, the Optimizer uses statistics. The following factors are some of the factors that these statistics are based on:
  • The number of records in a table
  • The number of data pages in a table
  • The location of the table
  • Whether indexes are present
  • How unique the indexes are
Based on these statistics, the Optimizer then selects the best internal query strategy for dealing with a particular query.

The statistics are updated whenever a query is compiled. A query is flagged for compiling when you save any changes to the query (or its underlying tables) and when the database is compacted. If a query is flagged for compiling, the compiling and the updating of statistics occurs the next time that the query is run. Compiling typically takes from one second to four seconds.

If you add a significant number of records to your database, you must open and then save your queries to recompile the queries. For example, if you design and then test a query by using a small set of sample data, you must re-compile the query after additional records are added to the database. When you do this, you want to make sure that optimal query performance is achieved when your application is in use.

Note You cannot view Jet database engine optimization schemes, and you cannot specify how to optimize a query. However, you can use the Database Documenter to determine whether indexes are present and how unique an index is.

For additional information about the problem that may occur when you use the Database Documenter in Access 2000, click the following article number to view the article in the Microsoft Knowledge Base:

207782 ACC2000: Error using Database Documenter if objects are open

Query timing

There are two significant time measurements for a Select query:
  • Time to display the first screen of data
  • Time to obtain the last record
If a query returns only one screen of data, these two time measurements are the same. If a query returns many records, these time measurements can be very different.

If the two measurements are the same when you view a Select query in Datasheet view, you see a screen of data and a total number of records that are returned by the query, such as "Record 1 of N." If it is faster for the Jet database engine to display the first screen of data than to complete the query and then retrieve the last record, you see a screen of data but no N in "Record 1 of N". The N value is blank until the query is complete, or until you scroll down to the last record.

This behavior is the result of the Jet database engine selecting one of two performance strategies:
  • Complete the query, and then display data
  • Display data, and then complete the query
You cannot control the strategy that is used. The Jet database engine selects the strategy that is most efficient.

Analyzing performance

If you are using Microsoft Access 7.0, Access 97, Access 2000, Access 2002, or Access 2003, you can use the Performance Analyzer to analyze queries in your database. Because the query performance analysis is closely tied to the Jet database engine, the Performance Analyzer suggests adding indexes only when the indexes will actually be used by the Jet database engine to optimize the query. This means that the Performance Analyzer can provide performance tips that are more specific to your database than the general suggestions listed below in the "Tips to improve query performance" section.

To run the Performance Analyzer in Access 7.0, in Access 97, in Access 2000, in Access 2002, or in Access 2003, follow these steps:

On the Tools menu, click Analyze, and then click Performance.

Tips to improve query performance

To improve query performance, try these tips:
  • Compact your database

    When you compact your database you can speed up queries. When you compact your database, the records of the table are reorganized so that the records reside in adjacent database pages that are ordered by the primary key of the table. This improves the performance of the sequential scans of records in the table because only the minimum number of database pages now have to be read to retrieve the records that you want. After you compact your database, run each query to compile the query so that each query will now have the updated table statistics.
  • Index a field

    Index any field that is used to set criteria for the query fields and the index fields on both sides of a join. Or, create a relationship between these fields. When you create a relationship, the Jet database engine creates an index on the foreign key if one does not already exist. Otherwise, the Jet database engine uses the existing index.

    Note The Jet database engine automatically optimizes a query that joins an Access table on your hard disk and an ODBC server table if the Access table is small and if the joined fields are indexed. In this case, Access improves performance by requesting only the required records from the server. Make sure that tables you join from different sources are indexed on the join fields.
  • Select the smallest data type that is appropriate

    When you define a field in a table, select the smallest data type that is appropriate for the data in the field. Also, make sure that fields that you plan to use in joins have the same data types or compatible data types, such as Autonumber and Number (if the FieldSize property is set to Long Integer).
  • Add only the fields that you must have

    When you create a query, add only the fields that you must have. In fields that are used to set criteria, click to clear the Show check box if you do not want to display those fields.
  • Save the SQL statement as a query

    If the RecordSource property for a form or for 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.
  • Avoid calculated fields

    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 following example, 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 cannot be optimized is nested in a subquery, all the query cannot be optimized.

    An alternative way to construct the query is as follows:

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

    If expressions are required in the output, try to put the expressions in a control on a form or on report. For example, you can change the previous query to a parameter query that prompts for the value of MyColumn, and then base a form or a report on the query. On the form or on the report, you can then add a calculated control that displays "Hello" or "Goodbye," depending on the value that is 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 on report, type:

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

  • Specify Group By

    When you group records by the values in a joined field, specify Group By for the field that is in the same table as the field that you are totaling (calculating an aggregate on). For example, in the Northwind.mdb sample database, if you create a query that totals the Quantity field in the Order Details table and then groups by OrderID, you can 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, Access must join all the records first and then perform the aggregate, instead of performing the aggregate and then joining only the required fields.

    For greater speed, use Group By on as few fields as possible. Alternatively, use the First function if you can.

    If a totals query includes a join, consider grouping the records in one query and then adding this query to a separate query that performs the join. When you do this, performance may be improved with some queries.
  • Avoid restrictive query criteria

    Avoid restrictive query criteria on calculated fields and on non-indexed fields if you can. Use criteria expressions that you can optimize.
  • Test your query performance in a field that is used in a join between tables

    If you use criteria to restrict the values in a field that is 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 on the "many" side of the join. In some queries, you may realize faster performance by adding the criteria to the field on the "one" side of the join instead of on the "many" side of the join.
  • Index sort fields

    Index the fields that you use for sorting.
  • Use make-table queries to create tables

    If your data seldom changes, use make-table queries to create tables from your query results. Use the resulting tables instead of queries as the basis for your forms, your reports, or your other queries. Make sure that you add indexes according to the guidelines that you read in this article.
  • Avoid using domain aggregate functions

    Avoid using domain aggregate functions, such as the DLookup function to access data from a table that is not in the query. Domain aggregate functions are specific to Access, and this means that the Jet database engine cannot optimize queries that use domain aggregate functions. Instead, add the query to the table that the function was accessing or create a subquery.
  • Use fixed column headings

    If you are creating a crosstab query, use fixed column headings whenever possible.
  • Use operators

    Use the Between...And operator, the In operator, and the = operator on indexed fields.
  • Optimize performance on the server

    For bulk update queries against ODBC data sources, optimize performance on the server by setting the FailOnError property to Yes.

REFERENCES

For more information about optimizing performance in Microsoft Access 2000, click Microsoft Access Help on the Help menu, type optimize performance in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about optimizing performance in Microsoft Access 2002, click Microsoft Access Help on the Help menu, type Improve performance of an Access database in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about improving performance in Access 2003, click Microsoft Office Access Help on the Help menu, type Improve performance of an Access database in the Search for box in the Assistance pane, and then click Start searching to view the topic.

For additional information about using indexes in Access 2000, click the following article number to view the article in the Microsoft Knowledge Base:

209564 ACC2000: Compound indexes must restrict first indexed field

Modification Type:MinorLast Reviewed:7/16/2004
Keywords:kbQuery kbhowto kbPerformance kbinfo kbusage KB209126 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.