Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

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 get the results that 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

Understanding the Query Compilation Process

The statistics just listed are updated whenever the query is compiled. For a query to be compiled, it must be flagged as needing to be compiled. The flag can be any of the following occurrences:

• Changes are saved to the query.

• Changes are saved to any tables underlying a query.

• The database is compacted.

After Jet flags a query 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.

NOTE

Because a Query Plan is based on the number of records in each table included in the query, you should open and save your queries each time the volume of data in a table changes significantly. This is especially true when you're moving your query from a test environment to a production environment. If you test your application with a few records in each table and the table's production data soon grows to thousands of records, your query will be optimized for only a few records, and won't run efficiently. I handle this problem by compacting the production database on a regular basis.

Analyzing a Query's Performance

When you're analyzing the time it takes for a particular query to run, it's important to time two tasks:

• How long it takes for the first screen of data to display

• How long it takes to get the last record in the query result

The first measurement is fairly obvious; it measures the amount of time it takes from the moment the Run button is clicked on the toolbar until the first screen of data is displayed. The second measurement is a little less obvious; it involves waiting until the N value in Record 1 of N displays at the bottom of the query result. The two measurements might be the same, if the query returns only a small number of records. The Jet Engine decides whether it's more efficient to run the query and then display the query results, or to display partial query results while the query continues to run in the background.Chapter 17, "Optimizing Your Application."

Things You Can Do to Improve a Query's Performance

You can do many things to improve a query's performance. These include, but aren't limited to, the following techniques:

• Index fields on both sides of a join. If you establish a permanent relationship between two tables, the foreign key index is automatically created for you.

• Add to the query grid only the fields you actually need in the query results. If a field is required for criteria, but it doesn't need to appear in the query result, clear the Show check box on the query grid.

• Add indexes for any fields that you are using in the sort order of the query result.

• Always index on fields used in the criteria of the query.

• Compact the database often. During compacting, Access tries to reorganize a table's records so that they reside in adjacent database pages, ordered by the table's primary key. Jet rebuilds the query plans, based on the current amount of data. These side effects of the compacting process improve performance when Jet is scanning the table during a query.

• When running a multitable query, test to see whether the query runs faster with the criteria placed on the one side or the many side of the join.

• Avoid adding criteria to calculated or non-indexed fields.

• Select the smallest field types possible for each field. For example, create a Long Integer CustID field rather than specifying the CompanyName field as the primary key for the table.

• Avoid calculated fields in nested queries. It's always preferable to add calculations to the higher-level queries.

• Rather than including all expressions in the query, consider placing some expressions in the control source of form and report controls. If you do this, the expression will need to be repeated and maintained on each form and report.

• Use Make Table queries to build tables out of query results based on tables that rarely change. In a State table, for example, rather than displaying a unique list of states based on all the states currently included in the Customer table, build a separate State table and use that in your queries.

• When using Like in the query criteria, try to place the asterisk at the end of the character string rather than at the beginning. When you place the asterisk at the end of a string, as in Like Th*, an index can be used to improve query performance. If you place the asterisk at the beginning of a string, as in Like *Sr, Jet cannot use any indexes.

• Use Count(*) rather than Count([

  fieldname ]) when counting how many records meet a particular set of criteria. Count(*) simply tallies up the total number of records, but Count([

  fieldname ]) actually checks to see whether the value is Null, which would exclude the record from the total computation. Furthermore, as mentioned in the next section on Rushmore technology, the Count(*) function is highly optimized by Rushmore.

• Use Group By as little as possible. When possible, use First instead. For example, if you're totaling sales information by order date and order number, you can use First for the order date and group by order number. This is because all records for a given order number automatically occur on the same order date.

• Use Rushmore technology to speed query performance whenever possible. Rushmore technologya data-access technology "borrowed" from Microsoft's FoxPro PC database engineimproves the performance of certain queries. The following section discusses Rushmore technology.

One of the most important things to learn about the tips listed here is that they shouldn't be followed blindly. Query optimization is an art, not a science. What helps in some situations might actually do harm in others, so it's important to perform benchmarks with your actual system and data.

Rushmore Technology

Rushmore is a data-access technology that can help improve processing queries. You can use Rushmore technology only when you include certain types of expressions in the query criteria. It won't automatically speed up all your queries. You must construct a query in a certain way for the query to benefit from Rushmore.

Rushmore can optimize a query with an expression and a comparison operator as the criteria for an Indexed field. The comparison operator must be <, >, =, <=, >=, <>, Between, Like, or In.

The expression can be any valid expression, including constants, functions, and fields from other tables. Here are some examples of expressions that Rushmore can optimize:

[Age] > 50
[OrderDate] Between #1/1/98# And #12/31/98#
[State] = "CA"

Rushmore can also optimize queries that include complex expressions combining the And and Or operators. If Rushmore can optimize both expressions, the query will be fully optimized. However, if Rushmore can only optimize one expression, and you combine the expressions with an And, the query will be partially optimized. If Rushmore can fully optimize only one expression, and you combine the expressions with an Or, the query won't be optimized.

Important Notes About Rushmore

You should remember a few important concepts about Rushmore:

• Queries containing the Not operator can't be optimized.

• The Count(*) function is highly optimized by Rushmore.

• Descending indexes cannot be used by Rushmore unless the expression is =.

• Queries on open database connectivity (ODBC) data sources can't use Rushmore.

• Rushmore can use multifield indexes only when the criteria are in the order of the index. For example, if an index exists for the LastName field in combination with the FirstName field, the index can be used to search on LastName or on a combination of LastName and FirstName, but it can't be used in an expression based on the FirstName field.