Share this Page URL

Chapter 12. Advanced Query Concepts > Optimizing Queries - Pg. 316

Advanced Query Concepts 316 Figure 12.19. The result of a Totals query showing the top 10 percent of the sales amounts. NOTE You might be surprised to discover that the Top Values property doesn't always seem to accurately display the correct number of records in the query result. This is because all records with values that match the value in the last record are returned as part of the query result. In a table with 100 records, for example, the query asks for the top 10 values. Twelve records will appear in the query result if the 10th, 11th, and 12th records all have the same value in the field being used to determine the top value. Optimizing Queries The Microsoft Jet Engine includes an Optimizer that looks at how long it takes to perform each task needed to produce the required query results. It then produces a plan for the shortest path to getting the results you want; this plan is based on several statistics: · · · · · · The amount of data in each table included in the query How many data pages are in each table The location of each table included in the query What indexes are available in each table Which indexes are unique Other statistics The Query Compilation Process These statistics are updated whenever the query is compiled. For a query to be compiled, it must be flagged as needing to be compiled--this happens when any of the following occurs: · Changes are saved to the query. · Changes are saved to any tables underlying a query. · The database is compacted. After a query has been flagged as needing to be compiled, it isn't compiled until the next time the query is run. During compiling, which takes one to four seconds, all statistics are updated, and a new optimization or Query Plan is produced.