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

Query Timing

Analyzing Performance

Tips to Improve Query Performance

• 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:
  
  Q117544 ACC2: Query by Form (QBF) Using Dynamic QueryDef (2.0)
  
  Q136062 ACC: Query by Form (QBF) Using Dynamic QueryDef (95/97)
  
  Q136460 ACC2: DLookup() Usage, Examples, and Troubleshooting (2.0)
  
  Q136122 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:

Q129530 ACC2: Tech*Ed 95 Sessions on Query Tips and Tricks

For more information about optimizing database performance, see the "Database Optimization Techniques" document from Tech*Ed 95 (TE9516.EXE).

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

Q129612 ACC2: Tech*Ed 95 Sessions on Database Optimization Techniques

For more information about using indexes, please see the following article in the Microsoft Knowledge Base:

Q98793 ACC: Compound Indexes Must Restrict First Indexed Field

For more information about Rushmore technology, see the "Rushmore Query Optimization" document (RUSHMR.EXE). For details about how to obtain the RUSHR.ZIP file, please see the following article in the Microsoft Knowledge Base:

Q128388 ACC2: Rushmore Query Optimization Paper Available in Download Center

Additional query words: speeding improving

Keywords : kbusage GnlOthr
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto