More Responsive Screens |
 |
by Max Defreitas |
"It's too slow!"
One of the more common complaints about Microsoft Access databases is that some screens are slow to respond. This is often misinterpreted as poor performance on the part of the database engine but the real culprit is usually the screen design.
Here are a few techniques that developers can use to improve screen responsiveness.
Fetch fewer records
It is far too easy to create a screen that fetches all the records in a table. Such a time consuming exercise is seldom necessary and serves only to make screens appear sluggish. Users are usually interested in a few or perhaps only one specific record. They may simply wish to add a new record. In either case, it is a waste of time to fetch enormous quantities of data. If you are on a local area network, you will not only slow down your own application but probably annoy most of your cohorts by greatly increasing network traffic.
The solution is to open screens, or forms as they are called in Microsoft Access, with queries that contain `where clauses' that use specific criteria to limit the number of records retrieved.
Tip: Open forms with criteria that return no records at all. Allow the user to enter criteria in the form header. It is quicker to refresh an existing form after the user enters criteria than it is to load a new form while fetching data.
Tip: Set the record source dynamically on bound forms, where the data fields are bound directly to the controls that display them on the screen. In a button or event procedure call Me.RecordSource = Q where Q is either the name of a stored query or a new SQL string and Me refers to the current form. The form will refresh and display new data every time you change the record source.
Fill those drop-down lists when you need them.
Entering criteria almost always requires drop-down lists to avoid mistakes and tedious guessing. Unfortunately, drop-down lists are notoriously slow. A few drop-down lists can cause the opening of a form to become intolerably glacial. The reason is that drop-down lists always fetch as much data as they possibly can even if you limit the number of records they display.
Every drop-down list takes its time to fetch data and delays the loading of the form even if the user has no need to use the list. The obvious solution is to populate the drop-down lists when the user activates them.
Tip: Use an event procedure or a button to set the row source for the drop-down list. For example, Me!myDrop List.Row Source = Q where Q is, once again, either the name of a query or an SQL string.
Tip: Drop-down lists themselves will be more responsive if they return fewer records. Try cascading criteria so that successive lists are limited by the selection in a previous list. The row source query for a list could depend on the item selected in a previous list as in this example: Q = " SELECT Field1, Field2 FROM Table1 WHERE Field3 = " & Me!DropList1.
The terrible secret.
Many of these techniques are derived from antediluvian mainframe days when data was squirted slowly through coaxial cables to dumb green and black terminals. These techniques are even more necessary today as we build forms for internet browsers and equip World Wide Web servers with databases. We developers can no longer afford to be even a little sloppy.
Max Defreitas, HAL-PC member, is a Microsoft Certified Systems Engineer and Product Specialist in SQL Server with MSI, a Microsoft Solution Provider in Houston. Once a chemical engineer and a former IBMer, he grew up to develop database applications with Visual Basic, Access and C++. He can be reached at netbase@zapware.com.
E-mail me at webmaster@hal-pc.org with any comments you have and tell me what you want to see here.
Back to the User Journal Home Page