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, 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: