USING UNCONVENTIONAL OPTIMIZATION TECHNIQUES
When you begin with a clean database, it's easy to optimize a database. Sometimes when dealing with a system that someone else has created, it takes some creative—or unconventional—optimization techniques. When cleaning up and adding indexes to a table to increase the performance of queries, for example, you might screw up performance in importing or executing bulk queries to those same tables. The following sections look at this situation, and more.
Understanding Some Performance-Tuning Pitfalls
The biggest mistake in performance tuning is not measuring the gains. Before starting a performance-tuning pass on a database, make sure that an accurate list of processing and response times is available, and then measure the differences after the optimization pass.
Increasing performance in one area generally means giving something up in another area. For example, adding an index speeds up access to the table based on information in that index. The new index, however, also increases the time it takes to add records to the database. Pay close attention to the possible tradeoffs being made during the optimization pass.
In one situation, a form was running very slowly after it was changed to read-only. After some research, I determined that making it read/write allowed the query to run much faster. The form was changed back to read/write, and all the controls on the form were locked to prevent accidental editing of the data.
Diagnosing Slow Queries
Developers who work with Access over a period of time develop a good feel for how soon to expect results from a query. Sometimes queries run much more slowly than they should and need to be investigated. Before you start to diagnose the problem, try to resolve it by making a backup copy of the database and then repairing and compacting it. More than a few times a few hours have been wasted trying to solve query problems that were caused by a bad index and easily corrected with a repair and compact. Before you start spending a lot of time diagnosing a problem, try the easy solution.
Next, a breakpoint is set in the code and the SQL statement is examined in the Debug Window. If the SQL statement calls a VBA function, the VBA function is usually removed and returns the raw data. Then move the VBA function to code or to a control source on a form to eliminate the function during the select process. If that isn't enough to correct the problem, highlight it and paste it into the SQL view of the query design grid.
Now switch to design view and run the query. Make sure that the performance of the query is really the problem you're looking for, and then proceed with optimizing it.
Note
Before switching to Design view, take note of the fields being selected from the table. If you see SELECT *, replace the lone asterisk (*) with tablename.* so that it properly shows in the query design grid when you go into Design view.
Look at the join fields and make sure that they're either indexed or have relationships created for them. Look for criteria in the grid and see whether the fields are included in indexes that would help the Jet optimization process.
Note
When you're working in the query design grid, performance of the Jet optimization might be slightly different than running from VBA code. Run the query from the Immediate window or code and then from the query design grid to make sure that you have similar performance before you begin optimizing. Sometimes it helps to size down all the fields to fit onscreen so that they all display when the query runs.
After you cover all the standard optimization techniques, you need to begin removing tables from the query until the query performance is dramatically changed. When the table associated with the problem is identified, research the indexes on the fields from that table and the joins being performed. Keep in mind that building compound indexes might help when you're selecting and ordering on different fields in the same table.
After you play with the query a bit, you'll most likely find the root of the problem. At that point, try one of the optimization techniques mentioned earlier in the section “Understanding Optimization Techniques” to help speed up the query.
Resolving Ambiguous Field References with the Query Design Grid
A good way to avoid getting the Ambiguous field reference message is to fully qualify the names of all objects in SQL statements. If errors are encountered during query execution, fall back on the reliable query design grid. Use the same method as described in the preceding section for optimizing queries by using the query design grid, and you should be able to spot the problems right away.
This technique also works well when you get the Parameters not supplied message and you can't visually determine which field or table name isn't spelled correctly. Follow the same technique as described earlier in the section “Understanding Optimization Techniques” and keep eliminating tables and fields until the problem is resolved. Then go back and investigate the last field or table that was removed.