Queries:Performance Tips

Here is a link to a Microsoft Knowledge Base Article on improving performance of queries.

The body of it follows:

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 on linked tables. For more information about improving performance on linked tables, please search on the following string in the Microsoft Knowledge Base:

odbc and optimizing and tables

MORE INFORMATION

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 one to four 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 additional records are added to the database. Doing so ensures optimal query performance when 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 additional information about the Database Documenter, click the article number below 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, and 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 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 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 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 chooses whichever is most efficient.

Analyzing Performance

To run the Performance Analyzer in Microsoft Access 7.0, Microsoft Access 97, or Microsoft Access 2000, on the Tools menu click Analyze, and then click Performance.

Tips to Improve Query Performance

To improve query performance, try these tips:

1. 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 improves the performance of sequential scans of a table's records because only the minimum number of database pages now 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.
2. 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 disk 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 that tables you join from different sources are indexed on the join fields.

3. When defining a field in a table, choose the smallest data type appropriate for the data in the field. Also, give fields that you plan to use in joins the same or compatible data types, such as Autonumber and Number (if the FieldSize property is set to Long Integer).
4. When creating a query, add only the fields you need. In fields used to set criteria, click to clear the Show check box if you don't want to display those fields.
5. If the RecordSource property for a form or report is set to a SQL statement, save the SQL statement as a query, and then set the RecordSource property to the name of the query.
6. 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 cannot 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 can 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 can 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;

7. SELECT *
   
8. FROM MyTable
   
9. WHERE MyColumn = [To see confirmed orders, enter Yes. To see unconfirmed orders, enter No.];
12. In the calculated control on the form or report, type:
15. =IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed")
19. 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'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 is 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.
22. For greater speed, use Group By on as few fields as possible. As an alternative, use the First function where appropriate.
25. If a totals query includes a join, consider grouping the records in one query and adding this query to a separate query that performs the join. Doing so improves performance in some queries.
26. Avoid restrictive query criteria on calculated and nonindexed fields whenever possible. Use criteria expressions that are optimizable.
27. 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 realize faster performance by adding the criteria to the field on the "one" side of the join instead of the "many" side.
28. Index the fields you use for sorting.
    
29. 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 that you add indexes according to the guidelines recommended here.
30. 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.
31. If you are creating a crosstab query, use fixed column headings whenever possible.
32. Use the Between...And, the In, and the = operators on indexed fields.
33. For bulk update queries against ODBC data sources, optimize performance on the server by setting the FailOnError property to Yes.

209564 ACC2000: Compound Indexes Must Restrict First Indexed Field