ACC: How to Optimize Queries in MS Access v. 2.0, 95, and 97

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article discusses how you can optimize query performance in Microsoft Access 2.0, 7.0, and 97. The topics include the Microsoft Jet database engine's query Optimizer, query timing, analyzing performance, and design tips for improving 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 impact query performance on linked tables. For more information about improving performance on linked tables, please search on the following words in the Microsoft Knowledge Base:

   ODBC and Optimizing and Tables

MORE INFORMATION

The Query Optimizer

The Microsoft Jet database engine contains several components, but the most important to queries (and the most complex) is the Optimizer. The Optimizer is "cost-based," meaning that it assigns a time cost to each query task and then chooses the least expensive list of tasks to perform that generates the desired result set. The longer a task takes to perform, the more costly or expensive it is considered to be.

To decide which query strategy to use, the Optimizer uses statistics. 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 or not indexes are present, how unique the indexes are, and so on. Based on these statistics, the Optimizer chooses the best internal query strategy for dealing with a particular query.

The statistics are updated whenever a query is compiled. A query is flagged as needing to be compiled when you save any changes to the query (or its underlying tables) and when the database is compacted. If a query is flagged to be compiled, the compiling and updating of statistics occurs the next time the query is run. Compiling usually takes from 1-4 seconds.

If you add a significant number of records to your database, you should open and save your queries to recompile them. For example, if you design and test a query using a small set of sample data, you should recompile the query after more records are added to the database. This ensures optimal query performance once your application is in use.

NOTE: You cannot view Jet database engine optimization schemes or specify how to optimize a query. However, you can use the Database Documenter to find out whether indexes are present and how unique an index is. For more information about the Database Documenter, search on "Database Documenter" using the Microsoft Access 97 Help Index.

NOTE: The Database Documenter is called the Database Documentor in Microsoft Access 2.0 and 95.

Query Timing

There are two significant time measurements for a Select query: time to display the first screenful of data and time to obtain the last record. If a query returns only one screenful of data, these two time measurements are the same. If a query returns many records, then 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 screenful of data and a total number of records returned by the query such as, "Record 1 of N." If it is faster for the Jet database engine to display the first screenful of data then to complete the query and retrieve the last record, you see a screenful of data but no "N" in "Record 1 of N". The "N" value is blank until the query is complete or you scroll down to the last record.

This behavior is the result of the Jet database engine choosing one of two performance strategies: complete the query, and then display data; or display data, and then complete the query. You cannot control which strategy is used; however, the Jet database engine will choose whichever is most efficient.

Analyzing Performance

If you are using Microsoft Access 7.0 or Microsoft Access 97, 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 will suggest 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 which are more specific to your database than the general suggestions listed below in the "Tips to Improve Query Performance" section of this article.

To run the Performance Analyzer in Microsoft Access 7.0 or Microsoft Access 97, 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. 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.

  Also, compacting the database flags all queries as needing to be compiled and regenerates the table statistics used in the query optimization process. Because the statistics are cached in memory, the statistics can become out-of-date over time, typically because of transactions being rolled back or because you turned off your workstation without closing the Microsoft Access database.

• When you join tables, try to index the fields on both sides of a join. This can speed query execution by allowing the query optimizer to use a more sophisticated internal join strategy.
• If you use criteria to restrict the values in a field used in a join, 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 fields as much as possible. If a database is not updated frequently, then an index should be placed on all fields that are used in a join or in a restriction. With the inclusion of Rushmore query optimization technology in the Jet database engine version 2.0 (and higher), queries are able to take advantage of multiple indexes on a single table. This makes indexing many columns advantageous.
• Use make-table queries to create tables from your query results if your data does not change often. Then, you can base your forms, reports, or other queries on the new tables.
• Try to construct your queries so that Rushmore technology can be used to help optimize them. Rushmore is a data-access technology that permits sets of records to be queried efficiently. With Rushmore, when you use certain types of expressions in query criteria, your query will run much faster.

  Rushmore does not automatically speed up all of your queries. You must construct your queries in a certain way for Rushmore to be able to improve them.

  For more information about how to take advantage of Rushmore technology, search for "Rushmore queries" using the Microsoft Access 97 Help Index.

• Use the BETWEEN...AND, the IN, and the EQUALITY (=) operators on indexed columns.
• Redesign queries that use NOT IN because this is difficult to optimize. For example, the following query

        SELECT Customers.*
        FROM Customers LEFT JOIN Orders ON Customers.[Customer ID] =
        Orders.[Customer ID]
        WHERE ((Orders.[Customer ID] Is Null));
  

  will perform better than:

        SELECT Customers.* FROM Customers WHERE Customers.[Customer ID]
        NOT IN (SELECT [Customer ID] FROM Orders);
  

  NOTE: The Find Unmatched Query Wizard uses the first technique in the above example.

• If you use the LIKE operator with parameters, try concatenating the query criteria in code. Because the value is unknown at the time the query is compiled, indexes will not be used. For more information about creating queries in code, please see the following articles in the Microsoft Knowledge Base:

        ARTICLE-ID: Q117544
        TITLE     : ACC2: Query by Form (QBF) Using Dynamic QueryDef (2.0)
  
        ARTICLE-ID: Q136062
        TITLE     : ACC: Query by Form (QBF) Using Dynamic QueryDef
                    (95/97)
  
        ARTICLE-ID: Q136460
        TITLE     : ACC2: DLookup() Usage, Examples, and Troubleshooting
                    (2.0)
  
        ARTICLE-ID: Q136122
        TITLE     : ACC: DLookup() Usage, Examples, and Troubleshooting
                    (95/97)
  
  

• If you use the LIKE operator with an asterisk (*) to find approximate matches, use only one asterisk at the end of character string to ensure that an index is used. For example, the following criteria uses an index:

        Like "Smith"
        Like "Sm*"
  

  The following criteria does not use an index:

        Like "*sen"
        Like "*sen*"
  

• When creating a query, add only the fields you need. In fields used to set criteria, clear the Show check box if you do not want to display those fields.
• Avoid restrictive query criteria on calculated and non-indexed columns whenever possible.
• Avoid calculated fields in nested queries. 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]="H","Hello","Goodbye") AS X
            FROM MyTable;
  
        Q2: SELECT * FROM Q1 WHERE X="HELLO";
  
     Because the IIF() expression in Q1 cannot be optimized, Q2 also cannot
     be optimized. If an expression is buried deeply enough in a query tree,
     you can forget that it is there. As a result, your entire string of
     queries cannot be optimized.
  
     A better way to write the example query above is as follows:
  
        Q1: SELECT *
            FROM MyTable WHERE MyColumn = "H";
  
     If expressions are necessary in the output, try to place them in a
     control on a form or report.
  
  

• When grouping records by the values in a joined field, specify Group By for the field that is in the same table as the field you are totaling (calculating an aggregate on). For example, if your query totals the Quantity field in an Order Details table and groups by OrderID, specify Group By for the OrderID field in the Order Details table, not the OrderID field in the Orders table.
• For greater speed, use Group By on as few fields as possible. As an alternative, use the First() function where appropriate.

  For example, if your query is based on Customers and Orders, and you are grouping by Customer ID, Customer Name, and Customer City, you could use First() for Customer Name and Customer City instead of Group By, because the value will be the same for all rows with the same Customer ID.

• If a totals query includes a join, try 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. For example, instead of the following query

        SELECT Customers.[Company Name], Customers.[Contact Name],
        Max(Orders.[Order Date]) AS [MaxOfOrder Date]
        FROM Customers INNER JOIN Orders ON Customers.[Customer ID] =
          Orders.[Customer ID]
        GROUP BY Customers.[Company Name], Customers.[Contact Name];
  
     break the query into two separate queries, as follows:
  
       Q1: SELECT Orders.[Customer ID], Max(Orders.[Order Date]) AS
           [MaxOfOrder Date]
           FROM Orders
           GROUP BY Orders.[Customer ID];
  
       Q2: SELECT Customers.[Company Name], Customers.[Contact Name],
           Q1.[MaxOfOrder Date]
           FROM Q1 INNER JOIN Customers
           ON Q1.[Customer ID] = Customers.[Customer ID];
  
     Note that Microsoft Access usually "collapses" queries where possible.
     Other than situations specifically noted in this article, it should not
     make any difference whether you have a query based on another query, or
     create one query.
  
  

• Use COUNT(*) rather than COUNT([Column Name]) to determine the number of records in a table. This is because there are special optimizations in the Microsoft Jet database engine that allow COUNT(*) to run much faster than COUNT([Column Name]) in some situations.
• Avoid using domain aggregate (totals) functions, such as the DLookup() function, in a query that accesses table data. Instead, add the table to the query or create a subquery.
• When defining a field in a table, choose the smallest data type appropriate for the data in the field. Also, give fields you use in joins the same or compatible data types.
• Use field sorting judiciously, especially with nonindexed fields.
• If you are creating a crosstab query, use fixed column headings whenever possible.

REFERENCES

For more information about optimizing performance in Microsoft Access 97, search the Help Index for "optimization, system" or ask the Microsoft Access 97 Office Assistant.

For more information about query performance tips, see the "Querying Tips, Tricks, and Optimizations" document from Tech*Ed 95 (TE9511.EXE). For details about how to obtain the TE9516.EXE file, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q129530
   TITLE     : ACC2: Tech*Ed 95 Sessions on Query Tips and Tricks

For details about how to obtain the TE9516.EXE file, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q129612
   TITLE     : ACC2: Tech*Ed 95 Sessions on Database Optimization
               Techniques

   ARTICLE-ID: Q98793
   TITLE     : ACC: Compound Indexes Must Restrict First Indexed Field

   ARTICLE-ID: Q128388
   TITLE     : ACC2: Rushmore Query Optimization Paper Available on MSL