Access Efficiency

<< Click to Display Table of Contents >>

Navigation:  Editorials > Building Applications >

Access Efficiency

 

Peter Vogel

 

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

less data.

 

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

broadband queries.

 

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:

Access Efficiency