The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
March 2012

The Need for Speed - Part Two: Rev up Slow Queries

Sitting on the side of the highway waiting to catch speeding drivers, a State Police Officer sees a car puttering along at 22 mph. He thinks to himself, "This driver is just as dangerous as a speeder!" He turns on his lights and pulls the driver over. Approaching the car, he notices that there are five old ladies, two in the front seat and three in the back, wide eyed and white as ghosts. The driver, obviously confused, says to him, "Officer, I don't understand, I was doing exactly the speed limit! What seems to be the problem?"

"Ma'am," the officer replies, "you weren't speeding, but you should know that driving slower than the speed limit can also be a danger to other drivers."

"Slower than the speed limit? No sir, I was doing the speed limit exactly... Twenty-two miles an hour!" The old woman says a bit proudly. The officer, trying to contain a chuckle explains to her that 22 was the route number, not the speed limit. A bit embarrassed, the woman grinned and thanked the officer for pointing out her error.

"But before I let you go, Ma'am," says the officer, "I have to ask... Is everyone in this car OK? These women seem awfully shaken and they haven't muttered a single peep this whole time."

"Oh, they'll be all right in a minute officer. We just got off Route 119."

All kidding aside, although speed may not be appropriate in all places, too much speed is rarely a problem for databases. This month, we will continue our series on speeding up your application, this time focusing on one of the most pervasive roadblock to speed: Slow queries.

In this Issue
  • Tip of the Month - Find Tables, Forms, and Reports the Easy Way
  • Getting Under the Hood
  • Avoid DLookup, DSum, etc.
  • Beware of Calling VBA functions
  • Replace Complicated Queries with Temporary Tables

  • Getting Under the Hood

    If you find that your program stalls when you run a certain report or slows when you pull up a specific form, the query (or SQL statement) behind the form or the report is more than likely the problem.

    Tuning these queries can result in dramatic pickups in your application. The best way to speed things up is to get under the hood and look at what is actually going on behind the scenes. I will be using forms for my examples, however the principles also pertain to reports. To get to the query behind your form, open it in design view, bring up the properties window, click on the data tab, and look at the Record Source.

    This will be either the name of a table, the name of a query, or a dedicated SQL statement. If you have chosen to base your form on a table, then every single record in the table is coming into your form. This is not a problem, unless you have a table with tens of thousands of records. If you do have that many records, then you want to switch things to a query that will bring to the user only those records that are of interest.

    If you have a query or a dedicated SQL statement, the question then becomes how do you speed those up?

    If you see any of the following, then it is likely you have found the true source of the problem:

    • A column that is the result of a lookup or summing function.
    • A column that calls a VBA Function.
    • Many tables, all connected in your query.
    • Queries that call other queries (that may in turn call yet more queries).

    Avoid DLookup, DSum, etc.

    For queries that have many rows, just avoid the dsum, dlookup, etc. The base code behind the lookup and summing functions is notoriously slow. You are better off creating a query and joining that query into your query. This is not an ideal situation, but can be an easy way to see if that will speed up performance.

    Beware of Calling VBA functions

    Queries that call functions you have written in Visual Basic will call the function for every single row in your query. So if you are bringing 1000 records to the form, then your function will be called 1000 times. If you can avoid calling the function, that is the best thing to do. If that is not practical, then you will want to take a look at the code in the function to see where there are opportunities to gain speed.

    There is certainly an art to speeding up functions. Some techniques to try include eliminating or tightening up chunks of logic, and shortening loops. At the end of the day, you will have to make changes to the function and then test it against records to find the configuration that work best for you with your particular data.

    Replace Complicated Queries with Temporary Tables

    When Access brings multiple tables into a query, it will do much more than what appears on the surface. It will go through a process of creating combinations of rows from each of the tables and then will carry those combined rows down for whatever links you put into your query. As you can imagine, if you have many tables involved, or tables with many rows, the potential to eat up computing resources gets high very fast.

    If you see any the following situations in your queries, then temporary tables is likely to be your best bet for speeding things up:

    • Queries that call queries that call queries.
    • Queries with many tables.
    • Queries that are so complicated that it is difficult to tell what is happening.

    These are all issues that become good candidates to be replaced by a single temporary table. The performance will be considerably faster if you take the complicated query and break it down into a series of steps. The most effective way to do this is to create and manipulate temporary tables with the use of the following query tools: Make table queries, append queries, and update queries.

    The concept here is that you first create an initial population from a single table that represents keys to the records that you want to bring back to your form. Then for each piece of data that you need to show the user, you run a query to update the data in the temporary table. Using this method, only one or two tables are ever involved in the process. Although there may be numerous steps, each individual step will be fast and you will achieve better performance.

    With this approach, you also gain the advantage of having a much easier environment to debug when things don't go according to plan. You can run your queries one at a time, inspect the data at the end of each step, and nail down where things are not going according to plan.

    You, of course, will have to come up with some method of running those queries that will create the initial temporary table and then update the rows as needed. This can be as simple as DoCmd.OpenQuery "QueryName". Or could be as complex as the approach that I use, which is a table of SQL statements that I then drive from code. I will dig into that approach in a future Wizard.

    However, know that replacing a single complicated query with a temporary table, followed by a series of update steps can be the most effective way to speed up slow databases.

    Tip of the Month - Find Tables, Forms, and Reports the Easy Way

    In the past, I have told you about using the Search Pane to find objects in Access (see July 2011).

    Although it is a very effective method, if you are just trying to get to a form or report that you recently used or created, you can get to it with blissful ease. In the search pane, you can choose to search by either created date or modified date. When you make this selection, the objects that you have you most recently created or modified float to the top of the list. Using this tip, you will find you spend more time being productive and less time searching.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886