Microsoft Jet 3,5 Performance Overview and Optimization Techniques. Non-configurable Performance Settings, страница 9

Avoid Embedding Expressions in Queries

Probably the biggest performance issue that we have seen from customer databases results from the use of expressions in queries. Having an expression in a query prevents Jet’s query optimizer from recognizing the column in the expression, thus not using the column for index or Rushmore optimization. This alone can cause a substantial performance hit. In addition, expressions are evaluated for every row, not just the rows that are returned. This can cause substantial overhead. One customer’s query went from two minutes to two seconds by removing the embedded expressions. The best place to put expressions is at the form or report level because then expressions are only evaluated for the rows returned.

Cautiously Use Indexes

While having indexes can typically decrease data-retrieval times, they always carry a cost in maintenance and concurrency issues. Below is a test showing the throughput differences when randomly updating one row of data in one table from six workstations.

By simply adding an index to the column that was being updated, overall throughput diminished over five times!  The question then becomes: When should a column be indexed? There is no concrete answer for this, as it depends on the type of application. The first rule of thumb is that highly duplicated data types should not be indexed (for example, Boolean data types, and columns that represent gender, state abbreviations, or country codes). The second rule of thumb is to not add indexes to columns simply to force Rushmore to use more than one index. An example of this would be indexing a column called City and a column called ZipCode in a customer table when the application is always going to be using both columns for retrieval purposes. In this instance, ZipCode is going to be the most unique index and would return a faster result set if City was not indexed. This is because Rushmore need not use the index on City, thus reducing overall I/O. Of course, if both values were not always being entered and they were used alternatively and equally, then having an index on both columns would probably be advantageous. Rushmore is best utilized when combined indexes generate a unique result set.

It is also important to remember that indexes create concurrency issues, as one index page represents many data pages. Therefore, modifying an index page can cause users with data on an entirely different data page to be locked out when trying to update the indexed column. This is illustrated in the chart above.

To see this behavior, open the Northwind database in Microsoft Access 97 and turn pessimistic locking on. Update a value in one indexed field in the Customer table but don’t move to the next record. On another workstation, open the Customer table and try to edit another value in the same indexed field that the other workstation is editing. Next try updating a value in an non-indexed field in the Customer table. What will become evident is that substantially more records of data are locked when you try to update a value in an indexed field than when you try to update a value in a non-indexed field.

While we are not stating that developers should not index, we are saying that developers and database administrators should be aware of the pros and cons of indexing.

Use SQL DML Statements Instead of DAO Looping Constructs

In the majority of cases (Jet 3.5 only), it is better to use a single SQL DML statement than a looping DAO construct. Below are two examples of code, with a chart following showing the time differences.