Introduction

This document provides a cornucopia of tips and tricks for making your DAO code as fast as possible. While the code samples are written and tested in Microsoft® Access 2.0, almost all of them apply equally to Microsoft Visual Basic®.

In studying the samples, it is important to keep the following in mind:

• The samples are written to be as brief and succinct as possible. As such, they sometimes sacrifice correctness for the sake of clarity. For example, although one of the very best performance improvements in this document is number 14, you won't find many examples actually using this advice because the extra lines of code required detracts from the point being made.

• There is no such thing as a hard and fast rule. All of the suggestions contained herein have been tested to confirm that they do in fact provide performance improvements. However, sometimes those improvements are marginal, and, worse, sometimes those improvements would be reversed if certain fundamental parameters were changed (e.g. number of rows). The text accompanying each suggestion attempts to draw attention to the critical details.

• The examples try to stick purely to DAO code changes that will affect performance. In particular, no attempt is made to try and point out how to tune a given query to execute faster, as those issues are germane to running queries either from code or directly within Microsoft Access. As a general rule, you will get more benefit from tweaking queries than from tweaking code. These tips assume that you've already tweaked the queries.

A common thread emerges from a close study of the examples given. Having a good understanding of the differences between tables, dynasets and snapshots is a great start to correctly predicting the performance characteristics of a given situation. This is particularly critical for working with ODBC data sources.

An equally important lesson to learn from the examples, is that when processing data, the time spent retrieving and updating data almost totally overwhelms other speed concerns. For example, it quickly became clear while preparing this presentation, that issues of, say, using a Variant data type instead of a String data type, become almost irrelevant if a field is being fetched in the same loop. This is not to say that data retrieval is particularly slow, but just that anything involving a disk drive (or even a disk cache) is at least an order of magnitude slower than doing an in-memory bit test to determine the sub-type of a variant. In short, it is far better to worry about tuning an OpenRecordset statement or pre-assigning Field objects before entering a loop, than deciding whether to Dim variables as String or Variant.

Finally, the single most important thing learnt from preparing this paper was that all the predictions and postulations about how fast a particular operation will be don't mean anything until you actual measure the speed! In their initial form, several of the tips below proved to be totally wrong. The lesson to take away is that, until you test your speed improvements in a real environment, you can't be sure of anything!