EXAMINING THE ARCHITECTURE OF THE QUERY RESOLUTION PROCESS

Before you spend quality time tuning the performance of queries, it's important that you understand the process that resolves the request. From the first instant you decide to pose a question to the database in terms of a query, you begin to follow a series of steps that you hope will result in an accurate, timely result. When you begin to optimize the query, it helps to understand these steps and know which part of the process you're optimizing.

Defining the Query

Many developers don't include a definition as part of query resolution, but it does affect how the query is resolved. When deciding how to pose the query to the database, you've probably already determined which tables to use and how to relate them. It's very important to review your thinking on this and make sure that you're asking the right question.

Compiling

The SQL statement is compiled when it's stored in the QueryDef. This process is usually very quick, even when large numbers of records are involved. The time spent during the compile process isn't significant.

Preparing the Execution Plan (Optimization)

Jet lists the available indexes and tables that you can use to resolve the query. Even for queries that return large numbers of records or require complex joins, Jet looks at each possible way to retrieve the records. The time for each part of the query is estimated based on record counts and statistical information in the index. Some statistics used in this process include the following:

• The size of the table—If the table is small, it might be faster to read the data from the table than to use indexes. The size of a table is related to the number of records and the amount of information in each record. This statistic is based on the number of pages the table takes up, not the number of records.

• The size of the index—This again isn't based just on the number of records but on the size of the indexed fields, which could be quite large when compound indexes are used.

• Rushmore technology—The optimizer, discussed fully in the next section, looks at the potential for merging two or more indexes to help solve parts of the query without reading the base table directly.

After many different possible resolutions are worked out, a cost is assigned to each. The plan that's expected to take the least time to complete is chosen as the winner, and the query proceeds. The final step is when Jet actually begins retrieving records and building the snapshot or dynaset.