Here, Peter Vogel looks at the single most important
change that you can make to your applications to make
them run faster. It’s also the one mistake that gets made
the most often.
I would say that most of my Access consulting work
comes from clients who have applications that are
running too slowly. So far, in every case where I’ve
been brought in, I’ve been able to significantly improve
the speed of the application. One of the reasons that
I’ve been so successful is that most of my clients have
made the same mistake: They’re retrieving too much
data. I’d say that was the reason for every client, but
my memory isn’t what it should be, and there’s the
possibility that I’ve forgotten the one exception to this
rule. This is the first area that you should concentrate
on when trying to speed up your application.
I know that this isn’t what you hear from experts
when it comes to improving performance. Experts will
tell you to make better use of indexes, use SQL in place
of recordset processing, convert SQL statements in your
code to queries in your database, and so on. In fact, I’ve
provided that advice myself on more than one occasion.
But these aren’t fundamental improvements.
For instance, as I pointed out in last month’s
Working SQL column (“Efficient SQL”), indexes are
often ignored by the DBMS when processing data. As
far as storing your SQL statements as queries—yes, that
does save you the cost of compiling your query when
you execute it. Quite frankly, however, the compile time
for most of the queries that you’re using (especially the
ones in your Recordsource properties) is probably so
small that you can’t see the improvement.
While all of these tips are good advice and well
worth following, they aren’t fundamental to making
your application run faster. At the risk of giving up a
good part of my consulting practice, here’s the real
secret to getting your application to run faster: Get
The typical mistake
There are specific cases when developers retrieve too
much data. I was asked to review one application that
ran for four hours, used two tape drives, and generated
temporary work files that filled two disk drives.
Looking at the program, I discovered that it retrieved a
row from a table, then retrieved some related rows from
another table, then retrieved some rows from a third
table that were related to the rows from the second
table. The application then went back and got the next
row from the very first table and repeated the process.
After every row in the first table was processed, the
application looked at the data that it had retrieved. It
began this second pass by checking a field from the first
table and discarding all the rows from all the related
tables, based on that value. About 75 percent of the
extracted data was thrown away in the second pass.
I rewrote the program to check the field in the first
table before retrieving any other data. When I was
done, the application ran in 90 minutes and used one
small work file. Obviously, this is a special case, and
there’s not much in general rules that you can draw
from this. Moving into Access, however, there are some
common failures that you can guard against.
The most common mistake that I see developers
make when retrieving too much data is using what I’ll
call an “unrestricted SQL query” in the Recordsource
property of a form. An unrestricted SQL query is one
that retrieves every row in a table (or most of them).
The extreme form of an unrestricted query is just a
table name. Equally guilty of the crime of retrieving too
much data (and slowing down an application) is the
SQL statement with no Where clause.
The opposite of an unrestricted query is what I call
a “targeted SQL query.” A targeted SQL query has a
Where clause that, ideally, retrieves a single row from a
table using the table’s primary key. Targeted SQL
statements aren’t restricted to retrieving a single row.
They do always contain Where clauses that carefully
restrict the number of rows to retrieve.
A distant second to the unrestricted query is the
“broadband query.” A broadband query retrieves more
fields than are necessary. However, the performance
impact on your application that’s inflicted by a
broadband query is much less than the impact of an
unrestricted SQL query. In this month’s Download file
(available at www.smartaccessnewsletter.com), you’ll
find an Access database that demonstrates the
difference in speed between unrestricted, targeted, and
One of the ways that developers end up with this
problem is by binding a form to a table. The form, of ...
Read More Here: