|
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.
| 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.
|
|
