Performance VII - vigorous VBA

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Compile an MDE ■■■

If possible, make an MDE file out of your database; an MDE file is compiled, so your code runs faster. Additionally, since no source code is stored in the MDE file, the database loads faster and uses less memory. Later versions of Access even have a wizard to help you make this split (Tools, Database Utilities, Make MDE file).

Access Help topic: About MDE files

Achieve the Compiled state ■■

Sounds Zen, but isn't. Module code is saved in two states in your database: source and compiled. The source state consists of the contents of your actual modules, with full text including white space, procedure and variable names, and comments. The compiled state is the executable version of your code; comments and white space are removed, and a stream of executable instructions produced. And the source state is sloooower than the compiled. This is because when you run a procedure, VBA checks to see if the parent module is compiled. If it is, the code simply runs. If it is not compiled, VBA compiles it by loading the code into memory, performing a syntax check, and compiling it into an executable format. If all these operations succeed, it can then run the code... hence the slow-down. So clearly you want your database to keep all code in a compiled state, so it runs as fast as possible. To do this, from a VBA code editor window click the Debug menu and select either Compile and Save All Modules or Compile Project, depending on your version of Access.

Access Help topic: Compile

And avoid Decompilation ■■

So how do you avoid loss of the compiled state? Modifying any code or code-bearing objects (e.g. forms, reports, controls) causes decompilation. But don't worry too much, your database doesn't need to be compiled in development - it only really requires the performance benefits of the compiled state when it is actually deployed to users. Therefore, when you are ready to deliver your database for testing or live use, put it into the compiled state using the steps outlined above.

ActiveX controls need References ■

If you're using an ActiveX control, your database should have a Reference to the ActiveX (OCX) file. This allows you to use early binding to bind variables to the control's objects, making it load and run faster. In most cases, this is handled for you: when you insert an ActiveX control into a form or report, Access automatically creates a Reference for that control, but you need to do this manually if using an external control in raw VBA (Tools, References).

Be explicit ■

Always explicitly declare variables - ensure this by always putting Option Explicit phrase at the top of each module.

Access Help topic: Option Explicit Statement

Choose the right types ■

Use the most efficient variable type possible when declaring variables. For example, don't use a Long Integer when a Byte will do. Avoid Variant types whenever possible because they can be inefficient and slow.

Access Help topic: Data Type Summary

Bind early ■

Avoid using the Object data type. Instead use the specific data type for the object you're working with. This allows for early binding, which can be substantially faster in many cases.

Assign things to variables ■■

If you are going to refer to a property, control or some other object more than once, assign it to an object variable. The performance hike will vary depending on how slow the statements you're replacing with the variable are, and how many statements are being replaced.

Access Help topics: Dim, Set

Use the Me keyword ■

Use the Me keyword instead of Form!FormName to refer to a form in that form's module.

Avoid the Immediate If ■

The IIf (Immediate If) statement can be slow as it does not employ "short-circuit" evaluation, i.e. both sides of the expression are always evaluated.

Access Help topic: IIf Function

Use string versions of functions where you can ■

Use the string version of functions (e.g. Left$()) when working with strings, and the non-string version (e.g. Left()) when working with variants.

Calculate with Integers and Longs ■

Instead of Doubles, use the Integer or Long data types whenever possible in your mathematical calculations.

Use dynamic arrays ■

Instead of fixed arrays, use dynamic arrays with the Erase and ReDim statements - this makes better use of your available memory.

Clean up after yourself ■

Eliminate unused procedures, variables and other code constructs. These elements use memory unnecessarily, and slow down the execution of your other code.

Stop painting ■■

Turn off screen painting during repetitive operations that update the screen, as this can have a big impact on performance - this is done by setting the Application.Echo property to false. The size of performance gain you get will depend on the type of video card in your PC. Don't forget to turn screen painting back on when your code has finished though!

Access Help topic: Echo Method

Don't go to infinity, or beyond ■

In other words, avoid infinite recursion. It sounds obvious, but if you have code that can call itself without having some type of short-circuit mechanism, you'll run into nasty "Out of Stack Space" errors.

Organise your code logically... ■

Organize and structure you modules to take advantage of the fact that when a procedure is loaded from a module, the entire module is loaded into memory. By placing related procedures in the same module, you can reduce the number of loads VBA has to make.

...and have fewer modules ■

Organize code into fewer procedures and modules. VBA allocates and manages memory for every procedure, so by rationalising several short, similar procedures into one, you can have faster code. remember though that this may make your code less manageable.

Use Bookmarks ■

Use bookmarks to move among records instead of using the FindNext method: it's faster becuase it's a direct move rather than a series of sequential reads.

Access Help topic: Bookmark property

Seek and ye shall Find ■

Whenever possible, use Seek instead of Find. It's faster as it uses indexes more efficiently.

Access Help topic: Seek method

If you must use FindRecord and FindNext... ■

...do so on indexed fields, to maximise the speed and efficiency of these operations.

Access Help topic: Find methods

Think carefully about Transactions in code ■

There shouldn't be any need to wrap update code in a BeginTrans...CommitTrans pair, as Jet has been optimized to do this since Access 95. Unless you have a series of updates that you need to commit, or potentially rollback, as a single transaction there should be no need to use BeginTrans...CommitTrans.

Access Help topic: Transactions

Constant-ly Variable ■

If you're using data that isn't going to change, put it in a constant instead of a variable. This allows VBA to compile the value into the constant when the underlying module is compiled, making the execution of that code faster.

Code isn't always better ■

Don't write code if a query would be better. Access is, after all, a database product and can handle 90% of all the data operations you will ever need... so why write recordset code for an operation that Jet can handle (and intelligently optimize) for you?

Reduce OLE references ■■

Every time you reference a VB object, method or property, you initiate one or more calls the OLE's Idispatch interface... and each call takes time. As such, it follows that by reducing the number of calls you can make your code run faster. These calls can be minimised, or optimised at least, by:

• Using object variables instead of directly referring to objects
• Using the With statement and For Each construct to minimize object references
• Ensuring references are outside loops
• Referring to a member of collection by its index number, rather than its name.

Declare String data carefully ■

Stack and heap memory is allocated differently according to the type of strings you create. By understanding some basic principles, you can handle strings more efficiently. Use the following guidelines to minimise stack memory usage:

• Local fixed-length strings less than or equal to 64 characters use 2 bytes for each character in the string. They don't use heap memory.
• Local fixed-length strings longer than 64 characters use 4 bytes of stack memory for a pointer to the variable in heap memory and 2 bytes of heap memory for each character in the string.
• Local variable-length strings use 4 bytes of stack memory for a pointer to the variable in heap memory, and a variable amount of heap memory according to the length of the string.

So... if your code used a large number of fixed-length strings of 64 characters or less, you can reduce stack usage by changing the strings to local variable-length strings or making them static fixed-length strings.

Close your database sometimes! ■

VBA dynamically loads code into memory as needed on a module level. If a function is called or a variable is used, the entire module containing that function or variable is loaded into memory. As you use your application, you keep loading code into memory... but VBA doesn't support dynamic unloading of these modules. Because of this, RAM will begin to be used up. For best performance then, periodically close your db to unload the modules. Note: you don't have to close Access itself, just the database itself, unless you have library database code loaded, in which case you should bail out of Access too. Oh yeah, you should always close your db after a Compile All Modules command, becuase this, by definition, loads all of your code into memory!

Remove Comments From Non-MDE Databases ■

If you are not deploying your database application as an MDE file, consider removing white space and comments from your delivered code. This will result in less space used, and offer a marginal performance increases. Of course, keep a development copy of your database that retains the white space and comments.

Access Help topic: modules, optimizing code

ActiveX Controls Should Have References ■

If you are using an ActiveX control, your database should have a Reference to the ActiveX (OCX) file. This allows you to use early binding to bind variables to the control's objects, making it load and run faster. In most cases, this is handled for you: when you insert an ActiveX control into a form or report, Access automatically creates a Reference for that control.

Don't Use Expressions To Determine Loop Boundaries ■

If use loop constructs in your VBA code, such as For...Next, Do...While, etc. don't force VBA to evaluate the boundaries of the loop each time. For example, instead of saying:

For intCounter=0 To Forms.Count - 1
...
Next intCounter

use this:

intCount=Forms.Count - 1
For intCounter=0 To intCount
...
Next intCounter

In the second example, VBA only has to determine the value of Forms.Count once. In the first, example, the value needs to be determined for each iteration of the loop.

The Big Bang (!) ■

Use bang (!) instead of the dot (.) operation to refer to collection members - ! is faster.

Integer division ■

If you're dividing one number by another, and both are integers, use integer division. In other words, int1 \ int2 is faster than int1 / int2. This is becuase Access doesn't have to work out what type of numbers are involved in the calculation before performing it.

Use Snapshots When Appropriate ■

Don't open Dynaset type recordset object on SQL database tables unless you need to add or edit records, or need to see the changes made by other users. Instead, consider using Snapshot recordsets which can be faster to scroll through. Of course, Snapshot recordsets can take longer to open since they require a full read of the source data.

Access Help topic: OpenRecordset Method

Use Dynasets for Large Record Sets ■

If you need to retrieve a large number of records, use a Dynaset instead of a Snapshot. Snapshot type recordsets must load all records from the data source before becoming available, whereas Dynasets are available as soon as the first 20 or so records are retrieved. Also, when using a Snapshot against large ODBC data sources, you run the risk of running out of disk space on your local computer. This is because all data is downloaded into RAM until the amount of RAM is exhausted. Then, the database engine creates a temporary database to store the contents of the snapshot. In a nutshell, when you open a snapshot, you need at least as much disk space as the largest table you are opening.

Access Help topic: OpenRecordset method

Use FailOnError For Bulk Updates ■

If you are using bulk update queries, optimize performance on the remote server by setting the FailOnError property of the Querydef object, or query to Yes.

Access Help topic: FailOnError property

Use ODBCDirect ■

ODBCDirect gives you almost direct access to server data through ODBC. In many cases, it is a faster and more flexible way to hit server data than that traditional Jet/Linked table technique. This feature, available in Access 97, can make client/server applications much faster.

Access Help topic: ODBC, ODBCDirect

Make It Look Faster ■

If you have exhausted all other performance optimization techniques, consider making your application "look" faster. Do this by displaying status messages and progress meters as your application loads forms, runs queries, and performs any other operation that may take a bit of time. While this doesn't make your application run faster, it appears to run faster.

Access Help topic: optimizations, performance

Minimize Record Navigation ■

Avoid record navigation wherever possible on linked tables. Only use the PageUp and PageDown movements, and the Move last movements when absolutely necessary.

Access Help topic: linked tables, optimizing performance

Release Locks ASAP ■

To improve multi-user concurrency, assume that other users will be trying to edit data in the same linked tables that you are using. In other words, keep records locked only as long as is necessary.

Access Help topic: linked tables, optimizing performance

Keep Static Data Local ■

Keep static data, such as lookup tables, on the local machine. Update the local tables as necessary from the server. For example, a lookup table containing the two-letter abbreviations for American states is not likely to change anytime soon. When such a table is used in a data entry application, it is a huge performance bottleneck to retrieve that data from the server everytime it is needed. Instead, copy that table to your application's local database.