UNDERSTANDING OPTIMIZATION TECHNIQUES

The following sections cover some simple techniques to optimize performance in Access. Included are tricks dealing with relationships, indexes, whole database structures, and performance tuning.

Increasing Performance with Table Relationships

To be certain that Access has the greatest possible choice when determining indexes to use, you should create relationships between tables that will be joined. When you set up relationships in Access, not only are you defining referential integrity and cascading operations, but you're also creating indexes on the tables.

Some indexes are exposed when the indexes collection for a table is viewed. The Foreign property indicates an index that was created because of a relationship defined in the database. Other internal indexes are created when tables are joined and can be found by searching MSysObjects (the hidden system table where Access stores information on its objects) for type 8 objects, or by looking at MSysRelationships.

Adding Indexes

Indexing every field might seem like a good idea, but keep in mind the drawbacks to this approach:

• Adding new records becomes increasingly slower as you add indexes to your table, especially when your table has large numbers of records. This also means more index locks and more multiuser locking issues for the database to manage. As a result, performance will suffer.

• The size of your database grows for each index. With large Access databases, this growth can sometimes be a problem when disk space is limited.

• Creating unnecessary indexes can fragment your base table data more than necessary. The rule of thumb here is to add indexes when they solve an optimization problem. Don't just add indexes to every field and call it an optimization pass.

• You can have only 32 indexes on a table, so use them wisely. In practice, this isn't much of a drawback; only in rare cases should you find the need for more than 32 indexes.

With these restrictions in mind, begin optimization by looking at some preliminary points that set the foundation for optimizing individual queries:

• Build relationships that represent your data and are part of the most common join operations on which the queries will be based. Build a list of the different queries that will be used in the system. Put the list in order of priority: Queries that interactive users will run often and queries that must provide a high level of performance are at the top of the list; reports that are run overnight or at times where system impact is low go at the bottom of the list.
  
  
• Go through the prioritized list of queries and begin adding indexes for the fields that are part of join clauses not included in the relationships you set up. Don't add just one index for each field in the join; instead, look at the entire join and the sort fields to determine whether multiple fields are involved. If the join occurs across two fields, you should build one compound index that includes both fields. If one field is used the most for sorting after the join, include it as the first field.
  
  
• Add additional indexes for single fields and combinations of fields found in WHERE, GROUP BY, and ORDER BY clauses. Carefully consider how each query will be used. Here are three queries and some good choices for indexes:
  
  

  • Customer=100

  • InvoiceDate > #1/1/95

  • Customer=100 And InvoiceDate > #1/1/95#

  The first two queries could be optimized by putting an index on Customer and a second index on InvoiceDate. The third query might use one of the two indexes and then scan the table data to apply the rest of the criteria. Jet also can invoke Rushmore and query against both indexes, returning only the records found in both index scans.
  
  
• To provide Jet with a faster alternative, add a compound index on Customer,InvoiceDate, and the query would be resolved based on the compound index very quickly. If the table isn't used for other queries involving Customer and the 32-index allocation has been used up, dropping the Customer index might be necessary because the compound index can be used for customer retrieval against the table.
  
  

Tweaking the Database Structure to Affect Performance

On one of my projects, an Access database served as a referral list to be searched by many different desktops. A main record was linked to more than 15 child tables by using an integer as the join field, and data was displayed from all 15 child tables on forms after the query ran. Some child tables consisted of 10,000 records of detail. A mainframe supplied the data, and users would never be updating the information directly.

The main requirement was to make the system fast. My first version had a completely normalized schema and was perfect in all ways. The queries ran well, but too many tables were involved in most joins, and I was summarizing the same base table information repeatedly. The users wanted to see detail and were selecting summary information.

After some serious soul-searching, I summarized many of the tables into two main tables that were built as the data was downloaded from the mainframe. Now, most queries could be answered by joining only two or three tables, which increased the performance dramatically. But when query performance was increased, the customers began complaining about the display performance.

Summarizing was taken one step further, and almost all the display data was stored in the main referral record. This meant creating large memo fields with imbedded carriage returns so that the data could be displayed in text boxes instead of subforms that were linked to the child tables.

After I implemented the changes, the final data display didn't require the links to so many tables and therefore ran much faster when users navigated from one record to another in the results list.

The moral of this story is not to overlook changing the structure of the database when you're looking for ways to optimize query performance. In this case, the relational design was nearly abandoned in the name of better performance.

Optimizing Join Performance

Join performance can vary widely based on the types and numbers of fields used in the join. Access works best when it can join on small numeric fields. It's also wise to join on fields that have the same data type to avoid data conversion during the join operation.