How to Optimize Queries and Recordsets

[ADezii]

1. Create Indexes on all Columns used in 'ad hoc' Query Joins, restrictions, and sorts (Jet already creates Indexes for Enforced Relationships).
2. Use Primary Keys instead of Unique Indexes wherever possible.
3. Use Unique Indexes instead of Non-Unique Indexes whenever possible.
4. Include as few Columns as possible in the result set.
5. Refrain from using Expressions, such as IIf(), in Queries.
6. Use Count(*) instead of Count([column]).
7. Use the Between Operator in restriction clauses rather than open-ended >, >=, <, <= restrictions (Between 35 And 50 rather than >=35).
8. Normalize your Tables.
9. Avoid using Outer Joins if possible since a complete scan of the outer Table is required.
10. For nontrivial Queries, use saved Queries instead of SQL since they are already optimized.
11. If you create a Recordset based on an SQL string using ADO, specify the adOpenDynamic Cursor Type value. Because Jet doesn't support this Cursor, it will handle this specifically, thus providing the best performance for SQL strings.
12. When you create an ADO Recordset retrieving Jet data, do not specify adUseClient for the Recordset's Cursor Location.
13. Manually recompile Queries when the size of Tables, or the presence or type of Indexes, has changed.
14. When possible, use Action Queries instead of looping through Recordsets in VBA to Update or Delete batches of data.
15. When you need to use a DAO Snapshot Recordset, or a Static Recordset using ADO, use a Forward Scrolling Snapshot, or a Forward Only Recordset using ADO.
16. When you only wish to add new Rows to a Recordset, open the Recordset using the dbAppendOnly option.
17. When creating Queries using Client/Server sources, consider using Pass-Through Queries.
18. When running very large Action Queries, set the Use Transaction property of the Query to False.
19. If you are going to add many single Rows using DAO or ADO code, try and open the Recordset using dbOpenTable or its ADO equivalent, adCmdTableDirect.
20. It is usually faster to Update data in a Non-Indexed Column. You may want to drop the Indexes, perform the Updates, and then re-add them.
21. Don't over-Index, but Index Fields that will be used for sorting, searching, or joining.
22. Do not Index Columns that contain highly duplicated information.

[Denburt]

Very interesting article, I do have a question or two for clarification and I would also like to add a thing or two (Hope you don't mind).

5. Refrain from using Expressions, such as IIf(), in Queries.

I do understand that using Functions and conditional statements do not process well in an underlying query or even sub queries however if it is in the direct query that is used as a recordset or recordsource would there be much of a time difference or other effect verses using it in the form or report itself?

6. Use Count(*) instead of Count([column]).

Interesting point I thought should be expanded on. :

Originally Posted by
• Count(*) counts all rows returned.
• Count([Column Name]) counts all rows where [Column Name] is not NULL.

10. For nontrivial Queries, use saved Queries instead of SQL since they are already optimized.

By this I am sure that you are referring to using Saved Queries versus SQL strings in combo Boxes, Form and Report Recordsources etc.

13. Manually recompile Queries when the size of Tables, or the presence or type of Indexes, has changed.

Just wow (missed this one somewhere along the way) I will be adding a subroutine to do this right away. If all goes well i may post it in the articles section shortly.

Information about query performance in an Access database
For 2000 - 2002 - 2003 A short snippet of the article:

After you compact your database, run each query to compile the query so that each query will now have the updated table statistics.

The article also stated that there are many reason to recompile the queries such as adding a number of records etc. a very interesting read.


Found this in a MS Access 97 article for Optimization.
A short snippet of the article:

Compile your saved queries before delivering your application. Do this by opening them in datasheet view and immediately closing them. Doing so will save the query plan with the query. This plan tells Jet how to process the query in the most efficient way. If you can, avoid using SQL strings in module code that are constructed and run on the fly. When these are run, they have to be compiled. Also be aware that, over time, the plan saved with the query may become obsolete as the data in the MDB file changes. You may want to recompile queries after compacting your database.

[ADezii]

Very interesting article, I do have a question or two for clarification and I would also like to add a thing or two (Hope you don't mind).

5. Refrain from using Expressions, such as IIf(), in Queries.

I do understand that using Functions and conditional statements do not process well in an underlying query or even sub queries however if it is in the direct query that is used as a recordset or recordsource would there be much of a time difference or other effect verses using it in the form or report itself?

6. Use Count(*) instead of Count([column]).

Interesting point I thought should be expanded on. :



10. For nontrivial Queries, use saved Queries instead of SQL since they are already optimized.

By this I am sure that you are referring to using Saved Queries versus SQL strings in combo Boxes, Form and Report Recordsources etc.

13. Manually recompile Queries when the size of Tables, or the presence or type of Indexes, has changed.

Just wow (missed this one somewhere along the way) I will be adding a subroutine to do this right away. If all goes well i may post it in the articles section shortly.

Information about query performance in an Access database
For 2000 - 2002 - 2003 A short snippet of the article:


The article also stated that there are many reason to recompile the queries such as adding a number of records etc. a very interesting read.


Found this in a MS Access 97 article for Optimization.
A short snippet of the article:

Don't mind the questions at all:

05. Using Expressions in Queries may be detrimental to the speed of your Queries. Because Jet cannot use an Index in any way with a Calculated Column, you may be causing sequential scans of your data by including an Expression in a Query. Even if a Row is excluded from output because of a restriction you placed in the Query, all Expressions for that Row are still evaluated. If you have a Table containing 1,000 Rows and you've only requested 1 Row back, if your Query includes an Expression and you're filtering based on that Expression, Jet must calculate the Expression for all 1,000 Rows.

06. Jet has built in optimizations that make Count(*) much faster than column-based counts.

10. Access creates hidden QueryDefs for SQL statements it finds in RecordSource and RowSource properties of Forms and Reports, but it won't create QueryDefs for SQL statements that are embedded in VBA code. Here is where the problem lies.

13. Manually recompile Queries when the size of Tables, or the presence or type of Indexes, has changed. The reasoning behind this should be fairly obvious.

I hope all your questions have been answered, if not let me know and I'll do my best to attempt a better response.