Access 2002 Desktop Developer's Handbook

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Aa188211.odc_4009c15cover(en-us,office.10).gif

Chapter 15: Application Optimization

Paul Litwin, Ken Getz, and Mike Gunderloy

December 2002

Applies to:
    Microsoft® Access® 2002

Buy this book

Summary: This article presents an excerpt from the book Access 2002 Desktop Developer's Handbook by Paul Litwin, Ken Getz, and Mike Gunderloy. Learn how to make applications run more quickly. (58 printed pages)

Contents

Tuning Your Application's Performance
Understanding How the Jet Query Engine Works
Configuring the Jet Engine
Microsoft's Unsupported Jet Optimization Tools
Speeding Up Queries and Recordsets
Speeding Up Forms
Speeding Up Reports
Optimizing VBA's Use of Modules and Compilation
Speeding Up VBA: Testing Hypotheses
Summary

  • Making your applications run more quickly
  • Understanding how the Jet engine optimizes queries
  • Understanding and optimizing VBA's module loading
  • Timing and comparing methods for solving a problem

As with any large Microsoft® Windows® development environment, you can make choices when writing your own Microsoft Access® applications that will affect the performance of your application. How you create your queries, how you organize your tables, and how you write VBA code can all affect your application's speed. This chapter presents a number of issues you need to consider when optimizing your applications.

Tuning Your Application's Performance

The faster your application performs, the more usable it will be. No user (or developer) likes a slow application. Getting extra performance, however, sometimes requires that you make trade-offs, and it may affect other aspects of the application's usability, stability, and maintainability. It's important to keep these other issues in mind as you tune your applications for speed.

Some of the many aspects of performance tuning are outlined here:

  • Access configuration
  • Database design
  • Query design
  • Forms design
  • Reports design
  • Single-user versus multiuser, file-server versus client/server application design
  • VBA coding

To create applications that perform well, you will have to address many, if not all, of these areas. Depending on the design of your application, some issues will be less important than others. For example, an application that has only one or two simple reports may not need much attention paid to this component. On the other hand, the same application may need a lot of attention in the areas of query and form tuning.

Note:   Although we've provided some limited multiuser and client/server performance tips in this chapter, look in Access 2002 Enterprise Developer's Handbook for additional performance suggestions specific to those areas. In this chapter, as in this entire book, we've focused on issues involved in using the Jet database engine.

Understanding How the Jet Query Engine Works

One of the potentially biggest bottlenecks in your Access applications is query execution. Any time your application creates a recordset, whether it is by executing a query, opening a form, printing a report, or opening a recordset in code, you are running queries.

The Jet query engine is responsible for the interpretation and execution of queries. Before you can optimize your queries, you need to understand how the Jet query engine works. Jet processes queries in four steps:

  1. Definition
  2. Compilation
  3. Optimization
  4. Execution

Query Definition

You can define queries using one of several mechanisms: QBE, SQL, or DAO/ADO. Whichever method you use to create the query definition, the query eventually gets converted to SQL, and it is passed to the Jet query optimizer, which then compiles and optimizes the query.

Query Compilation

Before Jet can optimize a query, it must parse the SQL statement that defines the query and bind the names referenced in the query to columns in the underlying tables. The Jet query engine compiles the SQL string into an internal query object definition format, replacing common parts of the query string with tokens. The internal format can be likened to an inverted tree: the query's result set sits at the top of the tree (the tree's root), and the base tables are at the bottom (the leaves).

Query definitions are parsed into distinct elements when compiled. These elements include:

  • Base tables
  • Output columns (the fields that will appear in the query's result set)
  • Restrictions (in QBE, the criteria; in SQL, WHERE clause elements)
  • Join columns (in QBE, the lines connecting two tables; in SQL, the fields in the JOIN clause)
  • Sort columns (in QBE, sort fields; in SQL, the fields in the ORDER BY clause)

Each of these elements comes into play as the query optimizer considers different execution strategies, as described in the following sections.

Query Optimization

The query optimizer is the most complex component of Jet. It's responsible for choosing the optimum query execution strategy for the compiled query tree. The Jet query engine uses a cost-based algorithm, costing and comparing each potential execution strategy and choosing the one that's fastest. Jet calculates the cost for two major operations in the execution of queries: base table accesses and joins.

Base table access plans

For each table in the query, the Jet query optimizer must choose a base table access plan. The three ways of accessing the rows in a table are

Table scan

Scanning a table record by record without use of an index. This may be necessary if a restriction column is not indexed or if the restriction is not very selective (for example, a large percentage of the base table rows are being requested). Each data page is read only once for a table scan.

Index range

Reading records in a table using an index over one of the single-table restrictions (query criteria). A data page may be read more than once for an index range.

Rushmore restriction

A Rushmore restriction is used when there are restrictions on multiple indexed columns. By using multiple indexes, Jet is able to considerably reduce the number of data pages it needs to read. In many cases, Jet can execute Rushmore queries without reading any data pages. (Of course, Jet still has to read index pages, but reading only index pages is almost always more efficient.)

Rushmore query optimizations

Jet 4 includes support for Rushmore query optimizations. In Jet 1.x, Jet could use only one index for a base table access. Using techniques borrowed from FoxPro, all versions of Jet since 2 have been able to use more than one index to restrict records. Rushmore-based query optimization is used on queries involving restrictions on multiple indexed columns of the following types:

Index intersection

The two indexes are intersected with And. Used on restrictions of the form:

WHERE Company = 'Ford' And CarType = 'Sedan'

Index union

The two indexes are unioned with Or. Used on restrictions of the form:

WHERE CarType = 'Wagon' Or Year = '1997'

Index counts

Queries that return record counts only (with or without restrictions). Used for queries of the form:

SELECT Count(*) FROM Autos

You can execute many queries much more quickly using the Rushmore query optimizer than you can with the other methods. Rushmore can't work, however, if you don't build multiple indexes for each table. It also doesn't come into play for those queries that don't contain index intersections, index unions, or index counts.

Join strategies

For queries involving more than one table, the optimizer must consider the cost of joins, choosing from the following five types of joins:

  • Nested iteration join
  • Index join
  • Lookup join
  • Merge join
  • Index-merge join

The Jet query optimizer uses statistics about the tables (discussed in the next section) to determine which join strategy to use. Each possible join combination is considered to determine which will yield the least costly query execution plan. The five join strategies are contrasted in Table 15.1.

Table 15.1. Jet Query Join Strategies

Join Strategy Description When Used
Nested iteration join "Brute-force" iteration through the rows in both tables. Only as a last-ditch effort. May be used when there are few records or no indexes.
Index join Scans rows in the first table and looks up matching rows in the second table using an index. When the rows in the second table are small (or no data needs to be retrieved from this table) or when the rows in the first table are small or highly restrictive.
Lookup join Similar to the index join except that a projection and sort on the second table are done prior to the join. When rows in the second table are small but not indexed by the join column.
Merge join Sorts rows in the two tables by the join columns and combines the two tables by scanning down both tables simultaneously. When the two tables are large and the result set needs to be ordered on the join column.
Index-merge join Similar to a merge join, except that indexes are used to order the two tables. Instead of a merge join when each input is a table in native Jet database format. Each input must have an index over its join column, and at least one of the indexes must not allow nulls if there is more than one join column.

Query statistics

When evaluating various base table access plans and join strategies, the Jet query optimizer looks at the following statistics for each base table:

  • Number of records in the base table.

  • Number of data pages occupied by the base table. The more data pages that need to be read, the more costly the query.

  • Location of the table. Is the table in a local ISAM format or is it from an ODBC database?

  • Indexes on the table. When looking at indexes, the optimizer is concerned with:

    Selectivity

    How "unique" is the index? Does the index allow for duplicates? A unique index is the most highly selective index because every value is distinct.

    Number of index pages

    As with data pages, the more index pages, the more costly the query.

    Whether nulls are allowed in the index

    Nulls in an index may rule out the usage of an index-merge join.

Putting it all together

In determining the optimum query execution plan, the Jet query optimizer iterates through the various combinations of base table access plans and join strategies. Before choosing a join strategy, the optimizer selects a base table access plan. The optimizer then stores the estimated number of records returned and a cost indicating how expensive it would be to read the table using that plan. Next, the optimizer generates all combinations of pairs of tables and costs of each join strategy. Finally, the optimizer adds tables to the joins and continues to calculate statistics until it finds the cheapest overall execution plan.

The query optimizer also considers the type of result set when costing various join strategies. When returning a keyset-type recordset (also called a dynaset), Jet often favors join strategies that are efficient at returning the first page of records quickly, even if the chosen execution strategy is slower at returning the complete result set. For keyset-type recordsets, this tends to rule out joins that require sorting, such as lookup and merge joins.

For queries based on many tables, the time spent estimating the cost of all potential join combinations could easily exceed the time spent executing any given execution strategy. Because of this, the query optimizer reduces the potential number of joins it needs to consider by using the following rule: Consider joining only the results of a join to a base table. The query optimizer will never consider joining the results of one join to the results of another. This considerably reduces the potential number of joins Jet needs to look at.

After a query has been compiled and optimized by the Jet query optimizer, two additional steps are taken prior to the execution of the query.

For queries involving external data sources, the remote post-processor determines how much of a query can be sent to the back end for processing by the database server application. The goal here is to send as much of the query as possible to the server, taking advantage of the server's abilities in executing queries involving server tables. This reduces the number of records that need to be sent across the network. The remote post-processor identifies those parts of the query tree that can be satisfied by server queries and generates the server SQL strings for each remote query.

Finally, the post-processor takes the compiled query tree and moves it to a new, cleaner, and smaller execution segment. This is the final step prior to query execution.

Query Execution

Once the optimizer has determined the optimum query execution plan, the query engine runs through the final query tree and executes each step to return the recordset.

You can direct Jet to create either a keyset or a static or snapshot type of recordset. When Jet runs a keyset-based query, it creates a set of unique key values called a keyset in memory that points back to the rows in the underlying tables. This keyset-driven cursor model is very efficient because Jet needs to read only these key values and store them in memory (overflowing to disk if necessary). The values of the other columns in the keyset aren't read until needed (such as when a user scrolls the datasheet to that screen of keyset rows), minimizing the time needed to execute the query.

For snapshot-based queries, Jet must run the query to completion and extract all the query's columns into the snapshot. When the query contains many columns, it's likely that Jet won't be able to fit the entire snapshot into memory, requiring Jet to overflow the result set to disk, substantially slowing the query. Since Jet reads only the key values of keysets into memory, the same keyset-based query might fit entirely in memory, resulting in a significant performance boost. On the other hand, queries with a small number of columns and rows will likely execute more quickly as snapshots.

Forcing Jet to Recompile and Optimize a Query

Queries are compiled and optimized the first time you run the query. They are not recompiled until you resave and rerun the query. Make sure you run all queries at least once before delivering an application to users. This will eliminate subsequent compilations. Save the query in Design view and then run it without saving it again. You shouldn't save the query after running it or it may be saved in an uncompiled state.

Because Jet makes optimization decisions based on the size of source tables and the presence of indexes when you compiled the query, it's a good idea to force Jet to recompile a query after you've altered indexes or significantly changed the schema or number of rows in the tables. You can force recompilation by opening the query in design mode, saving it, and then reexecuting it.

Tip:   You may find that simply opening the query in design mode and then saving doesn't reoptimize a query. To be completely sure, you can open the SQL view of the query, modify the SQL (add and then remove a space, for example), and then save the changes.

Taking Advantage of Rushmore

Rushmore is a technology provided by Jet that improves performance by creating bitmaps of index values so that index lookups with multiple fields involved can be extremely fast. There is no way to turn Rushmore on or off. Jet takes advantage of Rushmore optimizations anytime you have criteria that reference multiple indexed columns from the same table. If you have queries that don't include multiple restrictions or that contain restrictions on columns for which you haven't created indexes, Rushmore won't be used. Thus, it's important to create indexes on all columns that are used in query restrictions.

Rushmore works for both native and attached Access tables, as well as for attached dBASE tables. Queries involving ODBC, Btrieve, Paradox, or other ISAM tables do not benefit from Rushmore.

Keeping Statistics Accurate

The costing algorithms the query optimizer uses are dependent on the accuracy of the statistics provided by the underlying engine. Statistics for non-native tables will, in general, be less accurate than for native Access tables. For native Access tables, statistics may be inaccurate if many transactions are rolled back. Statistics can also be wrong if Jet (or the database application calling Jet) terminates abnormally without being able to update statistics to disk.

Tip:   To force Jet to update the statistics in a Jet database, you should regularly compact the database. Compacting the database may also speed up queries because it forces Jet to write all the data in a table to contiguous pages. This makes scanning sequential pages much faster than when the database is fragmented. Before compacting a database, it's a good idea to also run a disk defrag utility so that Jet can store the newly compacted database in contiguous space on disk. It also can't hurt to make a backup of your database before running any compaction process.

Configuring the Jet Engine

The Jet 4 engine is an advanced desktop database engine that automatically optimizes many data access operations without user intervention. There are, however, several exposed Jet engine Registry settings you can adjust to help tune Jet for your unique situation. Make any changes to these settings with care because a change to one of the settings may have the exact opposite of your intended effect—that is, it may actually slow down the application. In addition, any change may have side effects that negatively impact concurrency or application robustness.

The Jet 4 Registry settings are summarized in Table 15.2. You can alter the Jet Registry settings in four ways:

  • Use RegEdit to directly edit the default Registry settings.
  • Create a user profile with Registry settings that override the default settings and start Access using the /profile command-line option.
  • Use the SetOption method of the DAO DBEngine object from your application to temporarily override the default or application Registry settings.
  • Use a Jet engine–specific property of the ADO Connection object to temporarily override the default or application Registry settings.

Table 15.2. Jet Engine Registry Settings

Key Description   Key Location DAO SetOption Constant ADO Connection property
SystemDB Path and filename of the security workgroup file. access_path\ system.mdw Engines folder1 N/A Jet OLEDB:System Database
CompactByPKey If set to 1, Jet reorders records in primary key order during a compact operation; if set to 0, Jet places records in natural order (the order in which the records were originally entered). 1 Engines folder1 N/A N/A
PrevFormat-CompactWith-UNICODE- Compression If set to 1, when compacting previous versions of databases to Jet 4, Jet enables the compression attribute on all appropriate string columns. When set to 0, Jet won't set the compression attribute. 1 Engines folder1 N/A N/A
PageTimeout Length of time in milli-seconds a nonread-locked page is held in the cache before being refreshed. 5000 Jet 4.0 folder2 dbPage- Timeout Jet OLEDB:Page Timeout
UserCommitSync If set to Yes, the system waits for explicit record write operations to complete before continuing processing; if set to No, Jet operates asynchronously when committing explicit transactions. Yes Jet 4.0 folder2 dbUser-CommitSync Jet OLEDB:User Commit Sync
ImplicitCommitSync If set to Yes, the system waits for implicit record write operations to complete before continuing processing; if set to No, Jet operates asynchronously when committing implicit transactions.3 No Jet 4.0 folder2 dbImplicit-CommitSync Jet OLEDB:Implicit Commit Sync
SharedAsyncDelay Time in milliseconds that Jet waits before committing implicit transactions3 in a shared environment. 0 Jet 4.0 folder2 dbShared-AsyncDelay Jet OLEDB:Shared Async Delay
ExclusiveAsyncDelay Time in milliseconds that Jet waits before committing implicit transactions3 when the database is opened exclusively. 2000 Jet 4.0 folder2 dbExclusive-AsyncDelay Jet OLEDB: Exclusive Async Delay
FlushTransactionTimeout If nonzero, the number of milliseconds before starting asynchronous writes (if no pages have been added to the cache); a nonzero value disables the ExclusiveAsyncDelay and SharedAsyncDelay settings. 500 Jet 4.0 folder2 dbFlush-Transaction-Timeout Jet OLEDB:Flush Transaction Timeout
MaxBufferSize Size of Jet's cache in kilobytes; must be 512 or greater. ((total available RAM–12MB) / 4)+ 512KB Jet 4.0 folder2 dbMax-BufferSize Jet OLEDB:Max Buffer Size
MaxLocksPerFile Maximum number of locks requested for a single transaction; if the number of locks exceeds MaxLocksPerFile, the transaction is split and committed partially. This setting prevents problems that can occur with Novell NetWare 3.1 servers. 9500 Jet 4.0 folder2 dbMaxLocks-PerFile Jet OLEDB:Max Locks Per File
LockDelay Delay in milliseconds that Jet waits before retrying lock requests. 100 Jet 4.0 folder2 dbLockDelay Jet OLEDB:Lock Delay
LockRetry Number of times to repeatedly attempt to lock a page. 20 Jet 4.0 folder2 dbLockRetry Jet OLEDB:Lock Retry
PagesLockedToTableLock Number of concurrent page locks after which Jet locks the entire table. If set to 50, on the 51st page lock, Jet attempts to lock the entire table. If unsuccessful, Jet attempts again on the 101st page lock. 0 Jet 4.0 folder2 N/A N/A
RecycleLVs When set to 1, Jet will recycle long value (memo, OLE, and binary) pages. 0 (disabled) Jet 4.0 folder2 dbRecycleLVs Jet OLEDB:Recycle Long-Valued Pages
Threads Number of background threads Jet uses. 3 Jet 4.0 folder2 N/A N/A

1The Engines folder is located at the \HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines key in the Registry.

2The Jet 4.0 folder is located at the \HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Jet 4.0 key in the Registry.

3See Chapter 2 in Access 2002 Enterprise Developer's Handbook for a discussion of transactions.

See the User Profiles online help topic for more details on creating and using user profiles.

Using the DAO SetOption Method

You can use the DAO SetOption method to fine-tune performance on an application basis or even within the context of an application. For example, to improve performance for a batch of updates, you might lengthen the SharedAsyncDelay setting to one second using the following:

DBEngine.SetOption dbSharedAsyncDelay, 1000

There is no corresponding GetOption method.

Using the ADO Jet Engine–Specific Connection Properties

Like the DAO SetOption method, you can use one of the Jet engine-specific Connection properties to fine-tune the performance of an application if you're using ADO. For example, here's the equivalent ADO code to lengthen the SharedAsyncDelay setting to one second:

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Properties("Jet OLEDB:Shared Async Delay") = 1000

You can read, as well as write, the Jet engine–specific Connection properties; however, the values are not accurate until you first set the properties.

For example, if you execute the following code prior to setting the value of the Jet OLEDB:Shared Async Delay property, Access writes a value of zero to the Immediate window even though the Jet 4 default is a value of 500:

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Debug.Print cnn.Properties("Jet OLEDB:Shared Async Delay")

Not all Registry keys are settable using DAO or ADO. Those keys that can be set have constants listed in Table 15.2. Any settings are in effect until you change them or close DBEngine or the Connection object. In other words, these are temporary settings that are in effect only for the current session; they are not written to the Registry.

Microsoft's Unsupported Jet Optimization Tools

Microsoft introduced two (officially) undocumented and unsupported Jet optimization tools in Access 95 that are still available but still unsupported:

  • The ShowPlan option
  • The ISAMStats method

Both these tools are part of Jet itself; you don't need any additional DLLs or other programs to make them work.

The ShowPlan Option

Jet includes an undocumented Registry setting you can use to turn on the logging of query optimization plan information to a text file. To enable this option, you must create the following Registry key using the RegEdit program:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug

Add the JETSHOWPLAN string value to this key and set it equal to "ON". (The JETSHOWPLAN string is case sensitive; be sure to enter it exactly as it appears here.) When you restart Access and open a database, Jet begins to log query optimization plan information to the file ShowPlan.out, in the current folder. (Jet only writes information to the ShowPlan.out file as it creates the query plan. You may need to open a query in Design view, modify it, and save the query in order to force Jet to re-create the plan for the query.) Figure 15.1 shows the Registry after you've successfully added the appropriate Registry key and value.

Tip:   Based on early testing, it appears that if you use the ShowPlan option under Windows XP, you'll find the ShowPlan.out file in the My Documents folder. This location may change between the time we wrote this and the time Windows XP changes, but this was the behavior at the time of testing.

Tip:   If you're leery of modifying the Registry yourself, you can simply double-click ShowPlanOn.reg, which is provided on the companion CD. This file adds the appropriate key and value to the Registry for you. To turn off the ShowPlan option, double-click the ShowPlanOff.reg file. This will change the "ON" to "OFF" in the Registry. If you want to remove the Registry key, you'll need to do that manually.

Click here for larger image.

Figure 15.1. Add the Debug key and JETSHOWPLAN string value to enable the ShowPlan option (click picture for larger image).

A sampling of a ShowPlan log is shown here:

—- qtotEmployeeDinnerSales —-

- Inputs to Query -
Table 'tblMenu'
Table 'tblEmployee'
    Using index 'PrimaryKey'
    Having Indexes:
    PrimaryKey 7 entries, 1 page, 7 values
      which has 1 column, fixed, unique, primary-key, no-nulls
Table 'tblOrder'
    Using index 'PrimaryKey'
    Having Indexes:
    PrimaryKey 171 entries, 1 page, 171 values
      which has 1 column, fixed, unique, clustered and/or counter, _ primary-key, 
        no-nulls
    OrderTakerId 171 entries, 1 page, 7 values
      which has 1 column, fixed
    CustomerId 171 entries, 1 page, 11 values
      which has 1 column, fixed
Table 'tblOrderDetails'
    Using index 'MenuId'
    Having Indexes:
    MenuId 465 entries, 1 page, 18 values
      which has 1 column, fixed
- End inputs to Query -

01) Restrict rows of table tblMenu
      by scanning
      testing expression "Unit="Dinner""
02) Inner Join result of '01)' to table 'tblOrderDetails'
      using index 'tblOrderDetails!MenuId'
      join expression "[tblMenu].[MenuId]=[tblOrderDetails].[MenuId]"
03) Inner Join result of '02)' to table 'tblOrder'
      using index 'tblOrder!PrimaryKey'
      join expression "[tblOrderDetails].[OrderId]=[tblOrder].[OrderId]"
04) Inner Join result of '03)' to table 'tblEmployee'
      using index 'tblEmployee!PrimaryKey'
      join expression "[tblOrder].[OrderTakerId]=[tblEmployee].[EmployeeId]"
05) Group result of '04)'

You'll likely find the earlier discussion of Jet query optimization helpful in interpreting the ShowPlan results. Examining the ShowPlan log for poorly performing queries may help you in determining how best to optimize these queries.

ShowPlan is completely undocumented and unsupported by Microsoft and should be treated like any other unsupported feature: with care. Here are some of the issues to consider when using it:

  • If you close a database and open another database without exiting and restarting Access, the query plans for the new database will not be logged.
  • The plans for some queries Access uses internally will appear in the log.
  • The logging of plan information may adversely affect performance.
  • The log file may get very large. You'll need to empty it out every so often.
  • ShowPlan doesn't log the plan information for parameter queries or subqueries and may incorrectly log the information for other queries.

To stop the logging of ShowPlan information, set the JETSHOWPLAN Registry key to "OFF". (You can also double-click the ShowPlanOff.reg file, as discussed in a previous tip.)

The ISAMStats Method

Jet 4 includes an undocumented technique that you can use to return a variety of pieces of information relating to disk reads and writes. If you're using DAO, you can use the ISAMStats method of the DBEngine object. If you're using ADO, you'll need to create a recordset by calling the OpenSchema method of a Connection.

The ISAMStats method is useful when you use it to compare two possible ways of doing something. For example, if you wished to determine which of two different ways of creating a query was faster, you could use ISAMStats to determine the number of disk reads performed by each version of the query. Of course, you could always just time each query; using ISAMStats, however, may enable you to detect smaller differences that may not show up in timing comparisons. Why would you care about differences that won't show up in timing tests? You may wish to perform tests using a small subset of data or using a fast development machine that you'd like to project to larger recordsets or slower target machines.

Using the ISAMStats DAO method (and its corresponding technique when working with ADO), you can retrieve information on six important statistics:

  • Disk reads
  • Disk writes
  • Reads from cache
  • Reads from read-ahead cache
  • Locks placed
  • Locks released

Working with ISAMStats

To make it simpler for you to retrieve the various ISAMStats values, we've provided an enumerated type (IsamStats) and a user-defined type (IsamStatsType) in basISAMStats:

Public Enum IsamStats
    isReads = 0
    isWrites = 1
    isCacheReads = 2
    isReadAheadReads = 3
    isLocksPlaced = 4
    isLocksReleased = 5
End Enum

Public Type IsamStatsType
    Reads As Long
    Writes As Long
    CacheReads As Long
    ReadAheadReads As Long
    LocksPlaced As Long
    LocksReleased As Long
End Type

You can use the IsamStats enum with the DAO ISAMStats method to retrieve the particular item you're interested in. (See the section "Using DAO" that follows.) You can also use it with ADO to retrieve the particular field within the ISAMStats recordset you need. (See the section "Using ADO" that follows.)

Using DAO

The basic syntax for the ISAMStats method is as follows:

lngReturn = DBEngine.ISAMStats(option, [reset])

where option is a Long integer representing one of the options from Table 15.3 and reset is an optional Boolean value that, when set to True, tells Jet to reset the counter for this particular option.

Table 15.3. ISAMStats Options

Value IsamStats Enum Value Option
0 isReads Disk reads
1 isWrites Disk writes
2 isCacheReads Reads from cache
3 isReadAheadReads Reads from read-ahead cache
4 isLocksPlaced Locks placed
5 isLocksReleased Locks released

Each of the ISAMStats method options maintains a separate meter that counts the number of times that statistic occurred. The meter is reset back to zero whenever you use the reset option. To make use of ISAMStats, you need to call the method twice: once to get a baseline statistic and once to get a final statistic after running some operation. You determine the actual value of the statistic by then subtracting the baseline statistic from the final statistic. (You can alternatively reset each statistic before you measure it. Either way, you'll need to make two calls to the ISAMStats method for each option you want to measure.) For example, if you wanted to determine the number of disk reads Jet made while executing the qtotEmployeeDinnerSales query, you might use code like this:

Public Sub CountReads()

    ' Demonstrate the ISAMStats method,
    ' determining the number of reads involved
    ' in running the qtotEmployeeDinnerSales
    ' query.

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
  
    Set db = CurrentDb()
  
    Call DAO.DBEngine.IsamStats(isReads, True)
    Set rst = db.OpenRecordset( _
     "qtotEmployeeDinnerSales", dbOpenSnapshot)
    Debug.Print "Total reads: " & _
     DAO.DBEngine.IsamStats(isReads)
End Sub

Note:   The previous example resets the ISAMStats "meter" before running the query. You could, instead, retrieve the current setting before and after running the query. In that case, you would need to subtract the before and after values in order to calculate the total disk reads. You might use this technique if you wanted to accumulate all disk reads for a set of queries.

We've also included a simple function, adhGetAllDAOIsamStats (from basISAMStats), which uses the ISAMStats method to retrieve all the statistics at once. This function, shown in Listing 15.1, returns an IsamStatsType data type, with all of its members filled in. You pass in the name of a query or SQL string to execute, and, optionally, a QueryAction value (qaReturnRows or qaExecute) indicating whether the code should attempt to open a record-set or execute the query. The function fills in the IsamStatsType variable and returns it as the function's return value. Listing 15.2 shows a simple test procedure, demonstrating the use of adhGetAllDAOIsamStats. (This procedure also demonstrates the use of the parallel adhGetAllADOIsamStats function.)

Listing 15.1.

Public Function adhGetAllDAOIsamStats( _
 strQuery As String, _
 Optional qa As QueryAction = qaReturnRows) As IsamStatsType

    Dim lngStat As Long
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim ist As IsamStatsType

    Set db = CurrentDb()

    Call DAO.DBEngine.IsamStats(isReads, True)
    Call DAO.DBEngine.IsamStats(isWrites, True)
    Call DAO.DBEngine.IsamStats(isCacheReads, True)
    Call DAO.DBEngine.IsamStats(isReadAheadReads, True)
    Call DAO.DBEngine.IsamStats(isLocksPlaced, True)
    Call DAO.DBEngine.IsamStats(isLocksReleased, True)

    If qa = qaReturnRows Then
        Set rst = db.OpenRecordset(strQuery, dbOpenSnapshot)
    Else
        db.Execute strQuery
    End If

    ist.Reads = DAO.DBEngine.IsamStats(isReads)
    ist.Writes = DAO.DBEngine.IsamStats(isWrites)
    ist.CacheReads = DAO.DBEngine.IsamStats(isCacheReads)
    ist.ReadAheadReads = DAO.DBEngine.IsamStats(isReadAheadReads)
    ist.LocksPlaced = DAO.DBEngine.IsamStats(isLocksPlaced)
    ist.LocksReleased = DAO.DBEngine.IsamStats(isLocksReleased)

    adhGetAllDAOIsamStats = ist
End Function

Listing 15.2.

Public Sub DemoISAMStats()
    Dim ist As IsamStatsType

    ist = adhGetAllDAOIsamStats("qtotEmployeeDinnerSales")
    Debug.Print "qtotEmployeeDinnerSales (DAO)"
    Call ShowInfo(ist)

    ist = adhGetAllDAOIsamStats("qupdIncrementPrice", qaExecute)
    Debug.Print "qupdIncrementPrice (DAO)"
    Call ShowInfo(ist)

    ist = adhGetAllADOIsamStats("qtotEmployeeDinnerSales")
    Debug.Print "qtotEmployeeDinnerSales (ADO)"
    Call ShowInfo(ist)

    ist = adhGetAllADOIsamStats("qupdIncrementPrice", qaExecute)
    Debug.Print "qupdIncrementPrice (ADO)"
    Call ShowInfo(ist)
End Sub

Private Sub ShowInfo(ist As IsamStatsType)
    Debug.Print "============================="
    Debug.Print "Reads         : " & ist.Reads
    Debug.Print "Writes        : " & ist.Writes
    Debug.Print "CacheReads    : " & ist.CacheReads
    Debug.Print "ReadAheadReads: " & ist.ReadAheadReads
    Debug.Print "LocksPlaced   : " & ist.LocksPlaced
    Debug.Print "LocksReleased : " & ist.LocksReleased
    Debug.Print "============================="
End Sub

Using ADO

Because ADO doesn't natively support the ISAMStats method, the Jet OLE DB provider exposes this functionality in a slightly different manner. To retrieve the ISAMStats information using ADO, you must create a special recordset based on the current connection. To do this, use the OpenSchema method of the connection, indicating that you'd like a provider-specific set of information. The OpenSchema method normally requires you to specify which schema you're interested in (tables in a database, fields within a table, and so on) by specifying a constant value. Because the Jet OLE DB provider has made ISAMStats information available "under the covers"—that is, OLE DB knows nothing of this—you must specify a "magic number" (a GUID) rather than a constant value. The OpenSchema method returns a recordset filled with the information you need.

Before you can create the recordset containing the ISAMStats information, however, you'll need to reset all the information. When using DAO, you must reset each statistic individually. Using ADO, you can only reset all the statistics at once. To do that, you set a specific property of the Connection object, like this:

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

' Reset the statistics.
cnn.Properties("Jet OLEDB:Reset ISAM Stats") = 1

After you've reset the statistics, you can execute a query to refill the various statistics. Once that's done, you'll want to retrieve the various statistics. To do that, call the OpenSchema method of the Connection object, which returns a one-row Recordset object filled in with the statistics in its various fields:

Set rst = cnn.OpenSchema( _
 Schema:=adSchemaProviderSpecific, _
 SchemaID:="{8703b612-5d43-11d1-bdbf-00c04fb92675}")

Yes, you must pass that long, seemingly random GUID to the OpenSchema method; obviously, you'll be cutting and pasting this code from the sample module (basISAMStats) rather than typing it in by hand. After this method call, the recordset, rst, contains 12 columns (the values contained in most of which are undocumented), the first 6 of which contain the same information that was returned by the ISAMStats method in the previous section. You might write code like this to retrieve the six values:

ist.Reads = rst.Fields(isReads)
ist.Writes = rst.Fields(isWrites)
ist.CacheReads = rst.Fields(isCacheReads)
ist.ReadAheadReads = rst.Fields(isReadAheadReads)
ist.LocksPlaced = rst.Fields(isLocksPlaced)
ist.LocksReleased = rst.Fields(isLocksReleased)

To make it easier for you to retrieve this information using ADO, we've provided the adhGetAllADOIsamStats function (from basISAMStats), shown in Listing 15.3. As with the adhGetAllDAOIsamStats function shown in Listing 15.1, this function does the work of running the query for you, retrieves the ISAMStats information, and returns it in an IsamStatsType data structure.

Listing 15.3.

Public Function adhGetAllADOIsamStats( _
 strQuery As String, _
 Optional qa As QueryAction = qaReturnRows) As IsamStatsType

    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim ist As IsamStatsType

    Set cnn = CurrentProject.Connection

    ' Reset the statistics.
    cnn.Properties("Jet OLEDB:Reset ISAM Stats") = 1
    If qa = qaReturnRows Then
        Set rst = cnn.Execute(strQuery)
    Else
        cnn.Execute strQuery
    End If
    Set rst = cnn.OpenSchema( _
     Schema:=adSchemaProviderSpecific, _
     SchemaID:="{8703b612-5d43-11d1-bdbf-00c04fb92675}")

    ist.Reads = rst.Fields(isReads)
    ist.Writes = rst.Fields(isWrites)
    ist.CacheReads = rst.Fields(isCacheReads)
    ist.ReadAheadReads = rst.Fields(isReadAheadReads)
    ist.LocksPlaced = rst.Fields(isLocksPlaced)
    ist.LocksReleased = rst.Fields(isLocksReleased)

    Set rst = Nothing
    Set cnn = Nothing

    adhGetAllADOIsamStats = ist
End Function

Speeding Up Queries and Recordsets

With Jet's sophisticated query optimizer, you don't need to be concerned about the order of columns and tables in queries. The Jet query optimizer decides on the most efficient query strategy and reorders the query's tables and columns to best optimize the query. You can, however, help the optimizer by following these guidelines:

  • Create indexes on all columns used in ad hoc query joins (Jet already creates indexes for enforced relationships, so there's no need to create additional indexes for these types of joins), restrictions, and sorts.

  • Use primary keys instead of unique indexes whenever possible. Primary key indexes disallow nulls, giving the Jet query optimizer additional join choices.

  • Use unique indexes instead of nonunique indexes whenever possible. Jet can then better optimize queries because statistics on unique indexes are more accurate.

  • Include as few columns as possible in the result set. The fewer columns returned, the faster the query, especially if you can completely eliminate columns from a table you had included only in order to restrict returned rows.

    Warning:   If your query includes more than 50 fields or so, you'll see an impact on the speed of running the query. (When using ADO—and therefore, OLE DB—this warning is even more important.) Always limit your queries to the smallest number of fields possible—ADO retrieves all the properties of each field you include in the output of your query (DAO/Jet only retrieves the properties that you specifically access), and this can cause a performance hit.

  • Refrain from using expressions, such as those involving the IIf function, in queries. If you are using nested queries (queries based on the results of other queries), try to move up any expressions to the highest (last) query.

    Warning:   Using expressions in queries may be detrimental to the speed of your queries. Because Jet cannot use an index in any way with a calculated column, you may be causing sequential scans of your data by including an expression in a query. If possible, move these expressions to a form or report where you want to display the information. One more thing to consider: even if a row is excluded from output because of a restriction you've placed on the query, all expressions for that row are still evaluated. This means that if you have a table containing 1000 rows and you've only requested one row back, if your query includes an expression and you're filtering based on that expression, Jet must calculate that expression for all 1000 rows.

  • Use Count(*) instead of Count([column]). Jet has built-in optimizations that make Count(*) much faster than column-based counts.

  • Use the Between operator in restriction clauses rather than open-ended >, >=, <, and <= restrictions. Using Between returns fewer rows. For example, use "Age Between 35 and 50" rather than "Age >= 35".

  • When creating restrictions on join columns that are present in both tables in a one-to-many join, it is sometimes more efficient to place the restriction on the one side of the join. Other times, it might be more efficient to place the restriction on the many side. You'll have to test which is more efficient for each query, because the ratio of the sizes of the tables and the number and type of restrictions determine which is more efficient.

  • Normalize your tables, decomposing large tables into smaller normalized ones. Because this reduces the size of tables (and therefore the number of pages required to hold tables), it causes join strategies that involve table scans to execute more quickly.

  • In some instances, it might also help to denormalize databases to reduce the number of joins needed to run frequently used queries. (See Chapter 4, Database Design, for a discussion of denormalization and additional design details.)

  • When you have the option of constructing a query using either a join or a subquery, it's worth trying both options. In some cases, you will find the solution that employs the join to be faster; in other cases, the subquery-based solution may be faster.

  • Avoid using outer joins if possible because they require a complete scan of the entire preserved table (that is, the "left" table in a left-outer join).

  • For nontrivial queries, use saved queries instead of SQL because these queries will have already been optimized. (Access creates hidden querydefs for SQL statements it finds in RecordSource and RowSource properties of forms and reports, but it won't create querydefs for SQL statements you have embedded in VBA code.)

    Tip:   Although the general consensus is to use stored queries whenever possible, there are times when temporary queries (based on SQL text you enter manually) can be better. When you use a stored query, Jet stores the query plan the first time you run the query, and this plan never changes until you modify the query. If your query contains parameters that can grossly alter the number of rows to be returned by the query (that is, depending on the parameter, you might get 10 or 10 million rows back), a saved query might not be the best solution. Because Jet must always calculate the execution plan for temporary queries, you may get better performance from a temporary query if it's possible that the number of rows may be different each time you run the query.

  • If you create a recordset based on a SQL string using ADO, specify the adOpenDynamic CursorType value if possible. Because Jet doesn't support dynamic cursors (even though the constant name would indicate that it does), Jet handles this specifically, giving the best performance with SQL strings. It seems odd, using a constant supporting a specific cursor type that Jet can't handle to get the best performance with SQL strings, but that's how this particular constant has been mapped internally.

  • When you create an ADO recordset retrieving Jet data, do not specify adUseClient for the recordset's CursorLocation property. (The default is adUseServer, so you would have to specify adUseClient intentionally.) Although it may seem that because Jet isn't a client/server database engine, you should choose adUseClient, this logic is incorrect. If you choose adUseClient for the CursorLocation property, you're asking ADO to create its own cursor, in addition to the one Jet always creates. The time difference between using adUseClient and adUseServer is measurable, so don't fall into this trap unless you have some reason to use adUseClient. (Disconnected and in-memory operations, for example, require a client-side cursor, so you won't be able to avoid using adUseClient.)

  • Manually recompile queries when the size of tables or the presence or type of indexes has changed. (See the section "Forcing Jet to Recompile and Optimize a Query" earlier in this chapter.)

  • When possible, use action queries instead of looping through recordsets in VBA to update or delete batches of data.

  • When you need to use a DAO snapshot recordset (or a static recordset using ADO) and you don't need to move backward in the recordset, use a forward-scrolling snapshot (or a forward-only recordset using ADO). If you're using ADO, this is the default type of recordset you'll create. That is, unless you change properties of the recordset, you'll get a static, forward-only recordset.

  • When you only wish to add new rows to a recordset, open the recordset using the dbAppendOnly option.

    Tip:   ADO doesn't support the dbAppendOnly option, so you'll need to devise some other way to open a recordset for appending only. You might want to open the recordset using a WHERE clause that you know will return no rows (for example, asking for all customers whose CustomerID field is –1).

  • When creating queries against client/server sources, consider using pass-through queries. (Pass-through queries, however, may not always be faster than regular queries.)

  • When running very large action queries, you may wish to set the UseTransaction property of the query to False, which tells Access not to execute the action query as a single transaction. (For moderately sized action queries, setting UseTransaction to False may actually slow down the query, so be careful using this property.)

  • If you're going to add many single rows using DAO or ADO code, try and open the recordset using dbOpenTable flag (or its ADO equivalent, adCmdTableDirect). This allows Jet to perform the additions as quickly as possible.

The next few tips deal specifically with indexes and their use in your database design. Although indexes can make an enormous difference in the speed of searching and sorting, they aren't always a good thing.

  • It's usually faster to update data in a nonindexed column. If you're going to be updating or adding many rows to indexed fields, you may want to drop the indexes, perform your changes, and then re-add the indexes. This will, in general, be faster.

  • Unless you're using your data in a read-only manner, don't over-index. You should, of course, index fields that will be used for searching, sorting, or joining, but indexing all the fields (up to the 32-field limit) just because you think "one index is good, 32 must be even better" is not wise. Updates to indexed fields require updating both the field and the index and can add substantial overhead to your updates.

  • Don't index columns that contain highly duplicated information. For example, indexing a column containing only three different values will cause searching and sorting on that field to be slower than it would be if you didn't have an index at all.

    Tip:   In previous versions, Jet always created an index on foreign keys in relationships. In some cases, however, such as when your foreign key value is one of just a few values (a small lookup table, for instance), you don't really want an index. In this case, you'll need to use SQL to create your index (or you can do it when you create the table), and you'll need to include the FOREIGN KEY NO INDEX clause when creating a constraint. See Chapter 5, Access SQL, for more information on creating tables using SQL. (This feature isn't available through the Access user interface; it's only available when creating the table using SQL.)

When in doubt, experiment and benchmark various potential solutions. Don't assume one way to do it is faster just because it should be or because someone told you it was.

Speeding Up Forms

Most Access applications revolve around forms, so it goes without saying that any improvements to the performance of forms will realize large gains in the usability of your applications. The following sections detail several areas to consider when optimizing your forms.

Limiting a Form's Record Source with Large Recordsets

It's tempting to create forms in Access that are based on a huge recordset of tens or hundreds of thousands of records. However, you will quickly discover a severe performance penalty when opening such forms or attempting to navigate to a different record using the FindFirst method. The problems are exacerbated when you attempt to use these types of forms in a networked file-server or client/server environment, where forms will be retrieving remote data.

The solution is simple. Rather than giving users all the records and navigating around the form's dynaset, set up the form to serve up a single record (or some small subset of records) at a time. Then, instead of using the FindFirst method to move to a different record, change the form's RecordSource property.

Speeding Up Combo Boxes with Many Rows

Combo boxes are a great way to present a small or moderately sized list of items to a user, but you shouldn't use combo boxes to present more than a few thousand items. Consider alternative ways of presenting the same data.

Also, consider reworking the form so that the combo box contains fewer rows. You might use other controls on the form to refine the search and reduce the number of rows in the combo box's row source. For example, have the user enter an employee's territory into a territory control to reduce the number of entries in an EmployeeId combo box.

Although the AutoExpand functionality Access provides for combo boxes is very popular with users, it adds a large amount of overhead. Combo boxes will react to keystrokes more quickly if you turn off this property (see Chapter 7, Controlling Controls, for more information).

Other Form Speed-Up Tricks

Other things you can do to speed up your forms or reduce their memory usage include the following:

  • Instead of opening and closing forms, load often-used forms hidden and make them visible and invisible. This uses up more memory and system resources, so you have to balance this technique against memory considerations.
  • Consider using lightweight forms and hyperlinks for switchboard forms.
  • Reduce the complexity of forms. Break complex forms into multiple pages or multiple forms or use the native tab control.
  • Don't use overlapping controls.
  • Place controls containing memo and bound OLE objects on pages of a form other than the first page or on ancillary forms that can be popped up using a command button. This allows users to browse quickly through records when they don't need to view these complex objects.
  • If your form contains static pictures stored in unbound object frame controls, convert them to use lightweight image controls instead. To do this, right-click the control and select Change To . . . Image.
  • You may find the Performance Analyzer add-in (Tools . . . Analyze . . . Performance) helpful in locating and correcting performance bottlenecks in your application. Although its usefulness is limited, you may find it highlights problems you never thought of checking.

Speeding Up Reports

If you're creating complex reports, they might take longer to print than you'd expect. One reason for this is that Access creates a separate query for each section of the report. Many of the suggestions found in the section "Speeding Up Queries and Recordsets" earlier in this chapter also apply here because the speed of report output (or the lack thereof) is often more an issue of the underlying queries. In addition, you can try the following suggestions to improve report performance:

  • Move query expressions onto the report.
  • Avoid situations in which one query pulls the data from tables and a second query just filters the data. The more information you can pull together into one query, the better. One query uses less memory.
  • Avoid including fields in the query that aren't used in the final output of the report.
  • If you're using subreports, look at the queries on which they're based. Generally, you shouldn't use subreports when their record source is the same as the main report's record source. If the main and subform's record sources are the same, try rethinking your design so you can work without the subreport.
  • Add subreports to replace multiple expressions that call domain functions such as DLookup or DSum. By using a subreport, you can often get the same functionality you get with the slower domain functions, without using any expressions. On the other hand, if you need to look up only a single value, using a domain function may be faster.

Optimizing VBA's Use of Modules and Compilation

Instead of being a tokenized, interpreted language, as was Access Basic (used in Access 2), VBA is a compiled language. Although this advancement promises a great deal of power, it can be a problem if you aren't aware of the ramifications of using a compiled language. This discussion, through a series of questions and answers, explains the issues involved with compilation of your VBA code and what you can do to control it.

How Does VBA Load Code?

In Access 2, when you loaded an application, Access loaded all the global modules into memory at startup. Form and report modules were loaded as necessary, but the global modules were always available, having been loaded when the application loaded. This meant that although application load time might be longer, form and report loading could, at worst, cause the form or report module to be loaded. Of course, loading a large module takes time; the code must be loaded from disk and read into memory.

VBA loads code when it's needed, at execution time. Only modules called during the application's startup sequence are loaded as the application loads. That is, only modules that are needed by the startup form (or the macro called at startup) are loaded with the application. Then, as your application calls various procedures, VBA loads the appropriate modules. (In Access 95, loading a module caused VBA to also load any modules containing code or variables used by the module. This "call-tree loading" contributed to Access 95's slow load speed for forms.) Certainly, for applications with large amounts of code, this "load on demand" feature allows faster load times. As Access 95 loads each object, it might also need to load global modules called by procedures in the form or report module. This is not true for Access 97 and later: now, VBA loads only the particular module it needs at any given moment to run your code.

VBA always loads an entire module if it needs any portion of the module or if it must use any procedure in a module. The same goes for a variable in a module: if your code attempts to set or retrieve the value of a Public variable in a module, VBA must load the entire module. This, too, can contribute to application slowdowns if you haven't planned accordingly.

Tip:   If you'd like Access to load modules the way Access 95 does (that is, all of them in the call tree at once), you can still force this to happen. All you need do is refer to a single variable in each module, and you'll force VBA to load each module. If you want to preload all your modules, add a Public variable to each and attempt to retrieve the value of each in your application's startup code. That way, you'll cause VBA to load each module you reference at that time. This may seem like a lot of work, but at least in Access 97 and later you have the choice of how to handle this; in Access 95, you had no choice at all.

Why Compile Code?

VBA must compile the code at some point before it can run the code. If the code hasn't been previously compiled, VBA must compile it on-the-fly, as needed. That is, as you open forms or reports with uncompiled code, VBA must compile the form/report module before Access can open the object, and compilation takes time. You can certainly see that this would cause your forms or reports to open more slowly.

What Gets Stored When Your Code Is Compiled?

When VBA compiles your code, it stores both the original text (it doesn't store it exactly as you type it but stores a tokenized version) and the compiled version. When Access prepares to run previously compiled code, it loads just the compiled version into memory and runs that compiled code. For uncompiled code, Access must load the original version into memory and then compile the code as needed.

When Should You Compile?

The Debug . . . Compile <<Project Name>> menu item opens and compiles every module in your application, including the form and report modules. It performs a complete syntax check as well. This is the quickest way to completely compile your application, and you should perform this action before distributing any application. Unlike previous versions of Access, this menu item both compiles all modules and saves the compiled "bits" for each module.

After using this menu item, Access will now have saved both the original tokenized version and the compiled version. In addition, Access will track internally that all the modules are compiled and won't attempt to recompile anything before running your application. If you don't use this menu item, your modules' compiled state will be lost when you quit Access, and VBA will have to recompile before running the application. (If there have been no new changes to the code since it was last compiled, the Compile <<Project Name>> menu item won't be available.)

Tip:   Access 2002 makes a special optimization so that your VBA projects save faster. If you don't compile your project, the VBA editor saves only "dirty" modules and their dependencies when you save your project. Therefore, in this case, you save time by not compiling your code. Of course, when your code runs for real, you definitely want everything to be precompiled and saved in that format. But while you're developing, you'll get better save performance if you don't compile your modules. As you make changes, resist the temptation to compile your code. If you are working in a large VBA project, you'll be glad you didn't. You can accomplish this "decompilation" by using the /decompile option (see the section "Are Modules Ever Removed from Memory" a little later in the chapter for more information on this), saving the VBA project, and not recompiling while you're working. Beware that adding global objects or variables dirties the entire project, and doing so will lose your "only save dirty objects" advantage.

How Does the Compile On Demand Option Fit In?

When the VBA Tools . . . Options . . . General Compile On Demand option is checked (and that's the default for the option), VBA compiles only the code it must compile in order to run the current procedure. Although this does speed the development process somewhat (VBA isn't compiling code it doesn't need to compile for the current execution path), it's just delaying the inevitable. Sooner or later you must compile all your code. What's more, unless you understand the ramifications of this option, it can get you into trouble by leading you to believe your code is correct when, in fact, it's not.

To see the Compile On Demand option in action, follow these steps:

  1. In the VBA editor, use the Tools . . . Options . . . General menu item to make sure the Compile On Demand option is turned on.

  2. Create a new module and enter the following code into it:

    Function Test1()
        Test1 = Test2()
    End Function
    
  3. Create a second module and enter the following code (which would normally cause a compilation error because the function Test4 doesn't exist):

    Function Test2()
        Test2 = 1
    End Function
    
    Function Test3()
        Test3 = Test4()
    End Function
    
  4. In the Immediate window, type the following, causing VBA to run Test1. (Note that this doesn't trigger a compilation error, even though the code is not correct, because you've turned on Compile On Demand and you didn't demand that Test3 be compiled.)

    ? Test1()
    
  5. Go back to the Tools . . . Options . . . General dialog box and turn off the Compile On Demand option.

  6. In the Immediate window, repeat step 4. Note that there's still no error, because you're running code that's already compiled.

  7. Modify Test1 so it looks like the code below, and then run it in the Immediate window, as in step 4. Now you'll trigger a compile error because you've turned off Compile On Demand and caused the code to be recompiled.

    Function Test1()
        Test1 = Test2()
        Debug.Print 1
    End Function
    

What Causes Code to Be Decompiled?

Decompilation is the VBA programmer's curse. Compiling code can take time, and it's got to happen sometime between code changes and running the application. If you can save your code in a compiled state, you won't have to pay the compilation price at runtime. To avoid decompilation of your code, you must know when it occurs and what causes it.

Access marks your VBA, module by module, as being decompiled anytime you save a change to an object that might affect VBA code: forms, reports, controls, or modules. If you modify a form (or a report) or its controls, modify code in a global or class module, or delete or rename any of these objects, you'll cause Access to tell VBA that the specific object needs recompilation. If you make a change but don't save it, you'll preserve the compiled state.

If you change an object or its code, in addition to the object that's been changed, VBA must recompile any module or object that refers to the changed object. That is, decompilation travels "upstream," not "downstream." If module A calls code in module B and you change something in module B that's used in module A, module A will need to be recompiled. If you change something in module A, however, module B will retain its compiled state (assuming that nothing in module B calls code in module A).

VBA stores the project name (the VBA project, a value separate from the database name) as part of its compilation status. When you first create your database, Access assigns the database name as the VBA project name, by default. If you change the project name of a compiled application, Access sees it as being decompiled and forces VBA to recompile the entire application next time it's loaded. From within VBA, you can use the Tools . . . <<Project Name>> Properties dialog box to change the project name.

Warning:   Watch out for partial saves. That is, if you modify multiple VBA objects and deselect one or more items when you save the project, VBA automatically decompiles your project. Unless you're aware of this somewhat unexpected behavior, you might ship uncompiled code without knowing about it.

What Are the Effects of Compilation on Memory and Disk Usage?

Compiled applications require more disk space. As mentioned earlier in this chapter, when you compile all the modules, VBA stores both the decompiled and the compiled code in your database. On the other hand, compiled applications require less memory because VBA loads only the compiled code when it runs the application. If you attempt to compile code-on-the-fly, VBA must load the decompiled code and then compile it as it runs.

Are Modules Ever Removed from Memory?

Modules are not removed from memory until you close the application. VBA loads modules into memory as it needs them, and once a module has been loaded, it's never removed from memory. That is, VBA supports dynamic loading of modules but doesn't support dynamic unloading. Don't forget that VBA will load a module if you reference either a procedure or a variable in that module.

During the development process, this dynamic loading of modules can cause memory usage to grow and free RAM to shrink. You may want to close and reopen the database occasionally to release the memory. Using the Debug . . . Compile <<Project Name>> menu item causes all the modules to be loaded into memory; make sure you close and reopen the database after compiling all the modules.

Warning:   In previous versions of Access (we haven't yet confirmed that this happens with Access 2002, but it's worth knowing anyway), the VBA project for large databases could, on occasion, become corrupted. Weird things happen, and it's not clear why things go wrong. If you find VBA behaving oddly, and you're working with a large application, you may want to try the undocumented /DECOMPILE command-line option. To use this, first back up your database. Then, run Access from the command line, opening your database with the /DECOMPILE flag, like this: "MSACCESS YourDatabase.mdb /DECOMPILE". Access will start, load your database, and along the way, discard all the compiled "bits" of your project. Save your database and quit Access. Start Access again, load your database normally, and immediately compile your project. Quit and restart Access. Your database should be clean and ready to run. Please, back up your database before attempting this undocumented procedure.

What Can You Do to Optimize the Use of Modules?

Once you understand how VBA works with modules, you may want to take some extra steps to make your modules work as efficiently as possible. Although it may sound obvious, try to minimize the amount of extraneous code in each module. Because VBA always loads a full module to reference any procedure in that module, the less code it must load, the more quickly your forms and reports will open. Try to move unnecessary procedures from your modules and group procedures, if possible, by their use. Don't forget that referring to a variable in a module will also cause VBA to load that module. The goal is to load as little code as possible at any given moment.

Unlike its behavior in Access 95, VBA in Access 2002 will not decompile your entire application if a user adds a new form or report. Because of the upstream decompilation, you're guaranteed that no existing code relies on the new form or report, and your application will remain compiled. On the other hand, it's certainly possible for an end user to decompile specific modules by changing an existing form or report: if there's a change to an object that has code dependencies, VBA will have to decompile the code that depends on the object. The code will remain decompiled until the next time Access compiles your code, when it will save the compiled state for you.

One More Consideration: No Circular References in Libraries

Because of a design limitation of VBA, Access projects do not support circular references. No matter how you try to obfuscate the path, no procedure can call a procedure in another project that ends up calling a procedure in the original project. Even if you call procedures in other projects along the way, the procedure call will fail if it eventually calls a procedure in the original project. This will most often occur if you write library databases (add-ins, Wizards, and so on). You will need to design your library database in such a way that you don't require circular references. One unfortunate solution is to place duplicate copies of procedures in multiple modules. (See Chapter 18, Building Add-Ins, for more information on using library databases.)

What Else Can You Do?

There's no simple solution to maintaining the compiled state of your applications. You'll need to take special steps, and this will add extra overhead to your development process. The difference between running compiled code and decompiled code is quite obvious, even for moderate-sized applications, so it's worth taking the time to ensure that your applications are compiled when you need them to be.

Once you've tackled the major issues, you'll want to investigate ways to speed up the VBA code itself. The next portion of this chapter investigates some techniques you can use to improve the performance of the code. No such list could be exhaustive, and we've selected a series of tips that we've accumulated over the past few years. Some are old, some are new, but all can make a difference in how you write efficient VBA code.

Tip:   Although the conversion of a database to MDE format (using the Tools . . . Database Utilities . . . Make MDE File menu item) doesn't cause your code to run any more quickly than if you'd simply compiled your code from within the VBA editor, it does strip the source code itself from the database. (Don't let anyone convince you that creating an MDE file does something magical that normal usage doesn't—the code's compiled the same way it always was. All creating an MDE file does is toss out the source code. No more than that.) That way, Access can load the file more quickly, and, because the database uses less memory (because there's no source code loaded), there's more memory available for your application to use. The more memory, the faster the application, so converting a large application to MDE format should afford one more way of speeding its execution. Of course, once you've converted your application to an MDE file, users can never cause the code to be decompiled, so you'll never again need to worry about the application's compiled state. (For more information on using MDE files, see Chapter 18.)

Speeding Up VBA: Testing Hypotheses

As in any programming language, in VBA there are often many ways to accomplish the same task. Because you're dealing not only with a language but also with the interface and the underlying data all tied together in the programming environment, the choices are often even more complicated than with other, more standard languages. The following sections propose a series of selected optimizations, and some tips are more potent than others. Probably no single application will be able to use each of these, but you can add the ones that help to your bag of tricks as you program in VBA. You'll also find a method for timing those optimizations so you can create your own test cases.

Creating a Stopwatch

Although you could use the built-in VBA Timer function to calculate the time a specific process requires, it's not the wisest choice. Because it measures time in seconds since midnight in a single-precision floating-point value, it's not terribly accurate. Even though you'll most likely be timing intervals larger than a single second, you'll want a bit more accuracy than the Timer function can provide. The Windows API provides the timeGetTime function, which returns the number of milliseconds that have passed since Windows was started, and we recommend that you use this function instead.

Note:   Not that it matters for testing purposes, but Timer "rolls over" every 24 hours. The timeGetTime function keeps on ticking for up to 49 days before it resets the returned tick count to 0. Most likely, if you're timing something that runs for 49 days, you're not terribly interested in milli- seconds, but that's what you get.

To test each of the proposed optimizations, you need some mechanism for starting and stopping the clock. We've provided a class, StopWatch, which includes two useful methods: StartTimer and StopTimer. The StartTimer method stores the current return value from timeGetTime into a private module variable, lngStartTime. You must call this method directly before any code you want to have timed. When you're done with the critical section of code, call the StopTimer method, which returns the difference between the current time and the time when you called the StartTimer method (the elapsed time). Listing 15.4 shows the declarations and code for the timer functions. To use the StopWatch class, you can write code like this:

Dim sw As StopWatch
Set sw = New StopWatch
sw.StartTimer
' Execute the code you want to time.
Debug.Print sw.StopTimer

Tip:   Although there's no reason the code in the StopWatch class must be in a class module, putting it there adds one extra benefit—you can instantiate multiple StopWatch objects and have each StopWatch instance keep track of its own starting time. This allows you to time multiple concurrent events, if you need to do this. We haven't taken advantage of this capability in this test application, however.

Listing 15.4

Private Declare Function timeGetTime _
 Lib "winmm.dll" () As Long

Private lngStartTime As Long

Public Sub StartTimer()
    ' Start the timer, storing the value in
    ' the module global, lngStartTime.

    lngStartTime = timeGetTime()
End Sub

Public Function StopTimer() As Long
    ' End timing, and return the difference between the
    ' current time and lngStartTime

    StopTimer = timeGetTime() - lngStartTime
End Function

Getting Reasonable Results

You will find that running any given test only once doesn't provide reliable results. There are just too many external forces in play when you're running under Windows. To get a reasonable idea of the benefit of a given optimization test, you need to run the test code many times within the given test case and then run the test case as many times as necessary, averaging the results, until you get consistent results. For simplicity, each of the tests in this chapter takes as its only parameter a Long value indicating the number of times you want to run the test. Each function loops the specified number of times with the clock running and provides the elapsed time as the return value of the function. Listing 15.5 shows the entire RunTests function.

Note:   In order to get the most consistent results possible, RunTests continues to run the slower test function until the average amount of time taken to run the function reaches a consistent value. To keep things simple, the function performs a very low-tech analysis: it keeps track of the current average and the previous value. When these values get close enough together, the code stops calling the test function. Once RunTests knows how many times it needs to call the function, it simply loops, calling the faster test code the required number of times. In this way, you can get very consistent results without having to guess at the number of times to call the test functions.

Listing 15.5

Public Function RunTests( _
 strFunc1 As String, strFunc2 As String, _
 Optional lngRepeatOp As Long = 100000) As Long

    ' Run two test cases, comparing their relative timings.
    ' The assumption is that strFunc1 will be
    ' slower than strFunc2.

    Dim i As Long
    Dim lngResults1 As Long
    Dim lngResults2 As Long
    Dim lngResult As Long
    Dim lngTemp As Long
    Dim dblAvg As Double
    Dim dblPrevAvg As Double
    Dim lngCount As Long

    On Error GoTo HandleErrors

    Dim ot As OptimizingTests
    Set ot = New OptimizingTests

    ' Use this loop to both gather information
    ' about the slow version of the test, and also
    ' to figure out how many times to loop in
    ' order to converge on reasonably consistent
    ' results. Loop until either the difference
    ' in the average length of each iteration
    ' differs from the previous iteration by no more
    ' than adhcMaxDiff, or until you've hit
    ' adhcMaxIterations loops.
    lngCount = 1
    lngResults1 = 0
    Do
        Call SetStatus("Running " & strFunc1 & " Pass " & lngCount)
        lngTemp = CallByName(ot, strFunc1, VbMethod, (lngRepeatOp))
        lngResults1 = lngResults1 + lngTemp
        dblAvg = lngResults1 / lngCount
        ' If you've reached the correct difference between
        ' iterations, or you've hit 20 iterations,
        ' just exit the loop.
        If (Abs(dblAvg - dblPrevAvg) <= adhcMaxDiff) Or _
         (lngCount >= adhcMaxIterations) Then
            Exit Do
        Else
            ' Store away the current average
            ' for the next iteration, and bump
            ' the counter.
            dblPrevAvg = dblAvg
            lngCount = lngCount + 1
        End If
    Loop
 
    ' Now that you know how many times to call
    ' the function, loop through that many iterations
    ' of the faster function.
    lngResults2 = 0
    For i = 1 To lngCount
        Call SetStatus("Running " & strFunc2 & " Pass " & i)
        lngResults2 = lngResults2 + _
         CallByName(ot, strFunc2, VbMethod, (lngRepeatOp))
    Next i

    ' Don't divide by 0!
    If lngResults1 = 0 Then
        lngResult = 0
    Else
        lngResult = Int(lngResults2 / lngResults1 * 100)
    End If

    ' Show results in the Immediate window.
    Debug.Print strFunc1 & ": " & lngResults1
    Debug.Print strFunc2 & ": " & lngResults2
    Debug.Print strFunc2 & "/" & strFunc1 & " = " & _
     lngResult & "%"
    Debug.Print "============================="

ExitHere:
    Set ot = Nothing
    RunTests = lngResult
    ' Clear the status line.
    Call SetStatus
    Exit Function

HandleErrors:
    Select Case Err.Number
        Case Else
            MsgBox Err.Description & _
             " (" & Err.Number & ")", _
             vbExclamation, "Error in RunTests"
            lngResult = 0
    End Select
    Resume ExitHere
End Function

How It Works

The RunTests procedure, in basRunTests, handles the work of running both of your test cases and of comparing the timings for each. To call RunTests, pass it three parameters:

strFunc1

A string containing the name of the supposedly slower function.

strFunc2

A string containing the name of the supposedly faster function.

lngRepeatOp

A Long containing the number of times to loop, inside each function. (Optional: the default value is 100,000.)

After it has done its work, RunTests displays in the Immediate window the number of milli-seconds each test took and the percentage of time the faster function took, as compared to the amount of time the slower function took. For example, you might see the following output in the Immediate window:

Call RunTests("Test1a", "Test1b" )
Test1a: 371
Test1b: 130
Test1b/Test1a = 35%
=============================

As its return value, RunTests returns the percentage as a whole number. (That is, in the previous example, the function returned the value 35.)

In order for a procedure to work when called from RunTests, it must follow these rules:

  • Each procedure must be exposed as a method (a public function) in a class that includes all your tests. (In the sample, this class is named OptimizingTests.)
  • For each test, you must supply two procedures—one for the supposedly slower version, one for the supposedly faster—with the same name, except for an a (for slower) or b (for faster) appended to the name. For example, Test1a and Test1b would be the slower and faster versions of the Test1 case.
  • Each of your functions must accept, as its only parameter, a Long integer indicating how many times you want it to loop, internally.
  • Your functions should include a For . . . Next loop, which loops for as many times as the input parameter indicates.
  • As its return value, each function should return the number of milliseconds it took to do its work. In all the examples, the functions use the StopWatch class to do the timings.

To make it easier for you, you'll find the Test00a function in the OptimizingTests class:

Public Function Test00a(lngRepeats As Long) As Long
    ' Template for tests.

    Dim i As Long

    sw.StartTimer
    For i = 1 To lngRepeats
        ' Do something in here.
    Next i
    Test00a = sw.StopTimer()
End Function

Note:   If you're really thinking about this methodology, you'll note an important flaw in the testing harness. The For . . . Next loop itself takes time, and this time is added into the results of each of the slower and faster procedures. Because this extra time is added into each of the two procedures, it skews the results somewhat. Say, for example, a slower test took 1000 milliseconds, and a faster test took 500 milliseconds. Our example would indicate that the faster procedure took 50 percent as long as the slower procedure. In actuality, given (as a guess) that the loop itself consumed 50 milliseconds, the numbers are really 450 and 950 milliseconds, a ratio of around 47 percent. Because this value is always lower than the values we report (meaning that our values are always a little bit on the pessimistic side), we're comfortable with this small error. If you're worried about this, you could modify the test procedures to time a For . . . Next loop of the correct number of repetitions, and subtract that time from the time taken by the real test. We opted to leave this out to make test procedures more readable.

You can modify this procedure, adding the code you wish to test inside the loop. For example, the first test in the suite we've provided, Test1a, looks like this:

Public Function Test1a(lngRepeats As Long) As Long

    ' Use timeGetTime() if possible,
    ' rather than Timer(), to get current
    ' time information.

    Dim i As Long
    Dim lngTime As Long

    sw.StartTimer
    For i = 1 To lngRepeats
        lngTime = Timer
    Next i
    Test1a = sw.StopTimer()
End Function

As you can see, this procedure follows all the rules specified earlier.

Tip:   If you look carefully, you'll notice that neither Test1a nor any of the other test procedures instantiates or destroys sw, the StopWatch object. The OptimizingTests class does that itself, in its Initialize and Terminate event procedures. Rather than having each test create and destroy this object, it made sense to have one StopWatch object that all the tests share.

One big question remains: How is it possible for the RunTests procedure, given the names of test procedures as strings, to find and run the procedures? Normally, in VBA, you must know the name of the procedure you want to call at the time you write the code. There's really no late binding available for function calls.

Or is there? In VBA 6 (the version of VBA in Office 2000, Office XP, and in Visual Basic 6), you'll find a new function: CallByName. This function allows you to specify an object and a string containing the name of the method or property of the object that you want to call (or, for properties, set or retrieve). You must also specify the type of member you're calling (method, Property Get, Property Let, or Property Set, using the constants VbMethod, VbGet, VbLet, or VbSet) and can, optionally, specify a variant—which can contain an array of values—indicating the parameters for the method or property.

You can only use CallByName to work with methods or properties of an object that's in memory, so RunTests must create an instance of the OptimizingTests class before it can do any work. The code that does this, in RunTests, looks like this:

Dim ot As OptimizingTests
Set ot = New OptimizingTests

Once the object is in memory, RunTests calls its various methods using the CallByName function.

Given that the RunTests procedure receives the four parameters described earlier, it can use CallByName like this (SetStatus is a procedure, in the same module, that displays text on the demonstration form if it's open):

For i = 1 To lngReptFunc
    Call SetStatus("Running " & strFunc2 & " Pass " & i)
    lngResults1 = lngResults1 + _
     CallByName(ot, strFunc2, VbMethod, CVar(lngRepeatOp))
Next I

VBA Optimization Tips

In this section, we present a series of optimization tips, in no particular order. Some will actually make a difference in your applications; others are interesting programming tips that don't make a huge difference in your code. To test each hypothesis, we've created two similar versions of a simple function. The sample database includes the full source for both versions of each test so you can try them out yourself.

To simplify your experiments with the test cases we present here, you can use frmRunTests. Figure 15.2 shows this form in use. It includes a list box from which you can choose the specific test case to run and spin buttons allowing you to specify how many loops to execute inside the routine, as well as how many times to call each routine. The View Slower and View Faster buttons pop up forms that pull the source code for the functions directly from the OptimizingTests class module, so you can look at the code as you test. Finally, the clock button starts the test, running the slow version as many times as you request and then running the faster version the same number of times.

Aa188211.odc_4009c1502(en-us,office.10).gif

Figure 15.2. The frmRunTests form allows you to choose a specific test and run it, resulting in a comparison between the slow and fast versions.

Clearly, this method of testing is far from perfect. The order in which you run the tests might make a difference, and the host application and the operating system caches all make a difference, too. In our informal testing (and that's all this can be—measurements of relative differences), none of these factors made much difference. Reversing the order of the tests made almost no difference. Remember, the goal of these tests is to determine which of two methods is faster, not to gather exact timings. The ratios we found depend totally on the specific tests we ran and the setup of our system, but we've tried to make them representative of the kinds of improvements you'd see, too.

The results of our performance tests are summarized in Table 15.4.

Table 15.4. Summary of the Results of the VBA Performance Tests

Test Optimization Ratio of Elapsed Times (Smaller Is Better)
1 Use timeGetTime() rather than Timer 25%
2 Cache object references 40%
3 Use Len() to test for zero-length strings 60%
4 Use vbNullString instead of "" to initialize 35%
5 Be careful with string concatenation 15%
6 Use Mid$ statement rather than concatenation 55%
7 Use isCharAlphaNumeric() instead of ASCII values 55%
8 Use StrComp to compare short strings 45%
9 Use Like with wildcards 35%
10 Use "$" string functions when possible 60%
11 Use Integers instead of variants 50%
Test Optimization Ratio of Elapsed Times (Smaller Is Better)
12 Use Integer division (\) whenever possible 70%
13 Use logical assignments when possible 65%
14 Use Not to toggle between True and False 45%
15 Don't use Byte variables for speed 80%
16 Use For . . . Next rather than Do . . . Loop 50%
17 Be careful with IIf() 50%
18 Use If . . . Then rather than IIf 30%
19 Don't call DoEvents each time you loop 10%
20 Put the most likely candidate first in Select Case 15%
21 In arrays, For . . . Next faster than For Each . . . Next 75%
22 In collections, For Each . . . Next faster than For . . . Next 2%
23 Set a collection to New collection to clear it 10%
24 Use early binding 10%

Some of the comparisons are more dependent than others on the assumptions made. For example, Test12, which evaluates the difference between using Integer and real division, couldn't be constructed in too many different ways. It's doubtful you'd get results differing much from those in Table 15.4 by rewriting the code. On the other hand, Test17, which compares decisions made using IIf and the If . . . Then construct, will give widely differing results depending on the details of what you're doing in the True and False cases of the construct. Thus, it's important to be aware of the assumptions made for each test when interpreting the results.

The following sections describe each of the optimization tips tested in the OptimizingTests class module.

Note:   In general, we haven't listed the test code here. We describe the tests and the concepts involved, but you'll need to investigate the sample database to see the exact details of the tests. In any case, you're very unlikely to use the specific code we've written in your own applications; it's the concepts that count. For each test case, the name of the procedure in the OptimizingTests class is TestNa (the presumed slow version) or TestNb (the supposedly faster version), where N is the test number. For example, the code corresponding to test case 5 is in Test5a and Test5b.

Warning:   For each of the following tests, we've provided a percentage; that is, the percentage of time the faster test takes as compared to the slower test. If the slow test takes 1000 milliseconds, and the fast test takes 250 milliseconds, the percentage will appear as 25 percent. These values can and will most likely be different on your machines. We provide these values for comparison only, as a way to judge the effectiveness of the optimization. The lower the number, the better optimization provided by the specific test.

Note:   The effectiveness of each of the optimizations that follow depends on many factors, including the actual code in use at the time, the relative speed of your hard disk versus the processor in your computer, and other programs currently using Windows' memory and resources. There might be a process running in the background that you're not aware of, and Access and the database engine (whether you're using Jet, MSDE, or SQL Server) provide their own internal caching. The only sure way to provide accurate timings would be to remove all background processes and to reboot between each timing. That's not practical, so we'll mention again that the timing results presented here are for comparison purposes only. You'll need to decide for yourself in some of the marginal cases whether the optimization will really help. Therefore, a word of warning: take any suggestions about optimizations with a grain of salt. Try them out in your own applications before swearing by them.

Test 1: use timeGetTime instead of Timer

As mentioned earlier, the Windows API function timeGetTime returns the number of milli-seconds that have elapsed since you started the current Windows session. The VBA Timer function returns the number of seconds that have elapsed since midnight. If you're interested in measuring elapsed times, you're better off using timeGetTime, for three reasons:

  • timeGetTime is more accurate.
  • timeGetTime runs longer without "rolling over."
  • Calling timeGetTime is significantly faster.

Calling timeGetTime is no more complex than calling Timer, once you've included the proper API declaration for it. In the declarations section of any standard module in your application, you'll need to include the statement:

Private Declare Function adh_apiGetTime Lib "winmm.dll" _
 Alias "timeGetTime" () As Long

With that declaration in place, you can call it from any module in your application, just as though it were an internal Access function. (See Test1a/b for the full test procedures.)

Test 2: cache object references

In writing code you often need to retrieve or set properties of the various forms, reports, controls, or other objects in your application. Generally, you refer to these objects with statements like this:

strCaption = Forms("frmTest").cmdButton1.Caption

For a single reference to an object, there's not much you can do to speed up the reference. If, on the other hand, you're going to be referring to many of the properties of that object or using that object in a loop of some sort, you can achieve a substantial speed increase by pointing an object variable at that object and using that variable to reference the object.

For example, if you were going to reference many of a specific control's properties, you would be well served to use code like this rather than refer to the control with the full syntax each time:

Dim ctl as CommandButton
Set ctl = Forms("frmTest").cmdButton1

Debug.Print ctl.Name
Debug.Print ctl.Width
' etc...

In addition, using VBA's With . . . End With syntax affords the same improvements. Your code may end up being more readable if you use cached object references, but if you can use With . . . End With, it, too, can speed up your code.

In this example, we've used a particular field within an ADO recordset to prove the point. In the slower case, you'll find code like this:

Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.Source = "tblTests"
rst.Open

sw.StartTimer
For i = 1 To lngRepeats
    strName = rst.Fields(0).Name
Next I

The faster procedure caches the reference to the field, like this:

Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.Source = "tblTests"
rst.Open

sw.StartTimer
Set fld = rst.Fields(0)
For i = 1 To lngRepeats
    strName = fld.Name
Next I

Even in this simple case, where the changed code removes only a single dot within an expression, the code takes about 40 percent of the time it took originally. The more dots you remove by caching references, the bigger improvement you'll see. (See Test2a/b for the full test procedures.)

Tip:   Dots in object references will always slow down your code. You should take whatever effort you can to reduce the number of redundant dots, as demonstrated in this test.

Test 3: use Len() to test for zero-length strings

There are several ways you can check to see whether the length of a particular string is 0. One method is to compare the string to "", and another is to compare the length of the string to 0. Comparing the results of the Len function to 0 is measurably faster. You might not think that calling a function could be faster than simply comparing the string to "", but it is.

That is, given a choice, use:

If Len(strValue) = 0 Then

rather than:

If strValue = "" Then

to find out if a string contains no characters.

Because of the way VBA stores strings internally, you gain a small advantage if you compare a string's Len to 0—VBA stores the length of the string itself along with a pointer to the string buffer. This means that when you call the Len function, VBA isn't calculating the length of the string at that point, but rather uses the stored length value. In addition, each time you compare a string to "", you're asking VBA to create a string that contains no characters in order to compare it to your current string. This action, in itself, takes time. (See Test3a/b for the full test procedures.)

Test 4: use vbNullString rather than "" to initialize strings

When you want to reinitialize a string value so that it's empty, you normally use code like this:

strItem = ""

This creates a new string and copies it into the strItem variable. It turns out that a more expeditious solution is to use the vbNullString constant, a pointer to an empty string. By using:

strItem = vbNullString

VBA doesn't have to create a new string each time you make the assignment; instead, it uses its own internal pointer to an empty string, saving a substantial amount of work each time you initialize a string variable. (See Test4a/b for the full test procedures.)

Note:   Although you can use vbNullString to initialize a string variable to contain no characters, vbNullString is not itself an empty string. At least, not exactly. The vbNullString constant exists in order to make it easier to call Windows API functions that require a Null pointer (a concept that makes a lot of sense to C programmers, but not as much sense to VBA programmers). When you assign vbNullString to a string variable, VBA sees that you're attempting to assign a Null string pointer into a string variable, and therefore does the right thing—it creates a string that contains no characters for you.

Test 5: concatenation is expensive

Many times, when writing code, you have the choice whether to write a string expression as one long expression or to break it up into multiple expressions concatenated together with the "&" operator. Be careful: concatenation is a slow operation in VBA.

To test how much time concatenation takes, we've taken a single string ("ABCDEFG") and broken it up into seven fragments: "A", "B", "C", "D", "E", "F", and "G". The two lines of code:

strTest = "A" & "B" & "C" & "D" & "E" & "F" & "G"

and

strTest = "ABCDEFG"

prove that concatenation, if used only to "prettify" your code, is too expensive. In our tests, the faster version (the one without string concatenation) took only around 12 percent or so as long as the slow version. (See Test5a/b for the full test procedures.)

Test 6: use Mid$ reather than concatenation

The often overlooked Mid$ statement allows you to insert text substrings into other text values. For example, if you used the following code:

Dim strValue As String
strValue = "I like you"

Mid$(strValue, 3, 4) = "love"
Debug.Print strValue

strValue would contain "I love you" once you had finished. The alternative would be to write code like this:

Dim strValue As String
strValue = "I like you"
strValue = Left$(strValue, 2) & "love" & Mid$(strValue, 7)
Debug.Print strValue

The first alternative is significantly faster than the second. In our test case, either concatenating a series of X characters to build an output string, or using the Mid$ statement to replace each character in the output string with X, the faster version (using Mid$) took only 50 percent as long as the slower version. Of course, the relative timings might change depending on the action you were taking. In general, concatenation should be avoided in VBA. (See Test6a/b for the full test procedures.)

Test 7: use IsCharAlphaNumeric rather than ASCII values

You may find yourself needing to find out whether a particular character is an alphanumeric character (that is, checking to see whether it falls in the range of characters from A–Z, a–z, or 0–9). One standard method for doing this in VBA is to compare the Asc(UCase(character)) to the ANSI values for the ranges. The Windows API provides a function specifically for this purpose, IsCharAlphaNumeric. In addition, you can use a similar API function, IsCharAlpha, to check a character to see whether it's between A and Z. An added bonus of using the Windows API functions is that they're internationalized. Many characters outside the normal A–Z range are considered legal text characters in other countries. The brute-force comparison method would fail on such characters. To top it all off, using the API method is significantly faster than performing the comparisons yourself.

The slow version of the code might look like this:

Const cFirstChar = 65
Const cLastChar = 90
Const cFirstDigit = 48
Const cLastDigit = 57

intC = Asc(UCase(strC))
If (intC >= cFirstChar And _
 intC <= cLastChar) _
 Or (intC >= cFirstDigit And _
 intC <= cLastDigit) Then
    ' Do something now that you know the
    ' character is alphanumeric.  
End If

and the faster version might look like this:

If IsCharAlphaNumeric(Asc(strC)) Then
    ' Do something now that you know the
    ' character is alphanumeric.  
End If

(See Test7a/b for the full test procedures.)

To use IsCharAlphaNumeric, you need to include the following declaration in your application:

Private Declare Function IsCharAlphaNumeric _
 Lib "User32" Alias "IsCharAlphaNumericA" _
(ByVal cChar As Byte) As Long

Test 8: when comparing strings, use StrComp rather than converting with UCase

VBA compares strings based on the Option Compare statement at the top of each module. By default, VBA always assumes Option Compare Binary (in which case all text comparisons are case-sensitive). Because your code can't determine how VBA will compare strings, if you really need to compare a string without regard to case, you need to take extra steps.

One solution is to convert both strings you're comparing to uppercase, using the UCase function. With this technique, once both strings have been converted, you've eliminated uppercase and lowercase issues from the comparison. Another alternative is to use the StrComp function. This function allows you to specify two strings to be compared, as well as a constant indicating what method to use for comparisons: vbUseCompareOption (use the Option Compare mode), vbBinaryCompare (compare case-sensitively), vbTextCompare (compare case-insensitively), or vbDatabaseCompare (use the database comparison mode). StrComp returns 0 if the strings are equal, -1 if the first string is less, or +1 if the first string is greater.

To compare two strings without regard to case, you might write the code this way:

If UCase(strValue1) = UCase(strValue2) Then

or you might write:

If StrComp(strValue, strValue2, vbTextCompare) = 0 Then

It turns out that, for short strings, StrComp is faster. As the strings you're comparing grow in size, the difference in speed between the two techniques lessens. For huge strings, both techniques take about the same amount of time. Because most strings you'll work with are rather short, however, we recommend using StrComp for case-insensitive comparisons. (See Test8a/b for the full test procedures.)

Test 9: use the like operator instead of comparing individual characters

Imagine this scenario: you need to allow a variable to contain five characters that must be in a format like "W5TGQ", and your code can accept strings in only this format. In this case, you need all letters, except the second character, which must be a digit between 0 and 9. You could work your way through the input string, matching each character against the right conditions and exit the loop if you hit a snag, like the following code (this example uses the IsCharAlpha API function, which returns 1 if the value sent it represents a character, and 0 otherwise):

blnMatch = True
For j = 1 To 5
    intCh = Asc(Mid$(strTest, j, 1))
    Select Case j
        Case 1, 3, 4, 5
            If (IsCharAlpha(intCh) = 0) Then
                blnMatch = False
            End If
        Case 2
            If (IsCharAlpha(intCh) <> 0) Then
                blnMatch = False
            End If
    End Select
    If Not blnMatch Then
        Exit For
    End If
Next j

There are other ways you can do this as well, but one technique stands out. If you investigate the Like operator, you'll see that it provides a simple way to compare two strings using wildcards. (For complete information, check out the online help for this operator.) In this example, you can replace all the code shown above with this simple comparison:

blnMatch = _
 strTest Like "[A-Z]#[A-Z][A-Z][A-Z]"

In this case, "[A-Z]" matches any character between A and Z (and takes into account international languages that use the same alphabet as English), and "#" matches any digit (0–9). In our tests, the faster version (using Like) takes only about 35 percent as much time as the original test. Although Like (and its wildcard tests) cannot replace every string comparison, when it can, it does a good job. (See Test9a/b for the full test procedures.)

Test 10: use $ functions when possible

If you investigate all the built-in VBA functions, you'll note something strange about the string-handling functions: any function that returns a String value comes in two "flavors." For example, VBA provides both a Left and a Left$ function. Why both?

Note:   In early versions of Basic, before you could specify a variable as being of a specific type, programmers had to use type-specification characters—"$" for String, "%" for Integer, "&" for Long, and so on—rather than specific data type declarations. Basic applied that same concept to function names: functions that return strings ended with "$". We no longer use the characters indicating the data type (some people do, but we don't see any reason to), but the "$" functions remain.

The Left function returns a variant, and Left$ returns a String. If you're placing the return value into a variant, it makes sense to call Left. On the other hand, you're most likely to place the return value of Left into a string, and by calling Left (rather than Left$), you're forcing VBA to do an extra type conversion.

In our tests, simply running the following code:

For i = 1 To lngRepeats
    strValue = Left$(strValue, 3)
Next I

first without, and then with, the "$", the faster version (with the "$") took only around 55 percent as much time as the slower one (without the "$"). We recommend always using the "$" version of a string function. (See Test10a/b for the full test procedures.)

Test 11: use Long rather than Variant

Unless you specify otherwise, VBA creates all variables using its default type, Variant. To hold data of any simple type, variants must be at least as big and complex as any of the types they can contain. "Big and complex" equates with "slower," so avoid variants if at all possible. Of course, there will be many times when you can't avoid them, but if you're just ill-informed or being lazy, your code will suffer. (See Test11a/b for the full test procedures.)

Note:   If you're working with data from tables in your code, you generally must use variants. Because variants are the only data type that can hold null data, and it's usually possible for data from tables to be null, you'll avoid problems by using variants. In addition, you may find that attempting to use specific data types when working with Jet ends up slowing your code. Because Jet uses variants when it communicates with Access, when you place Jet data into specific data types, you're asking VBA to make a data type conversion, and that takes time.

Test 12: use integer division (\) when possible

Access provides two division operators, the / (floating-point division) and \ (Integer division) operators. To perform floating-point division, Access must convert the operands to floating-point values. This takes time. If you don't care about the fractional portion of the result, you can save some time by using the Integer division operator instead.

The results of this test were decidedly mixed. Using Integer division made almost no difference (although it did make a difference in early versions of Access). In some other examples—working with forms, for instance—it did make a difference. It may be that VBA is smart enough to use Integer math internally if it can tell that that's what will work most quickly. In the test procedure, the code divides each integer value by 7, either using normal or Integer division:

Dim intX As Integer

For i = 1 To lngRepeats
    ' In the slow case:
    ' intX = i / 7  
    intX = i \ 7
Next I

Our take on this: Integer division won't make much of a difference unless you're performing large numbers of divisions in which you care only about the integer portion of the result. But it can't hurt. (See Test12a/b for the full test procedures.)

Test 13: use single-line logical assignments

Like many other languages, VBA handles logical values as integers. In addition, Access performs right-to-left expression evaluation. The combination of these two features allows you to make logical assignments directly as part of an expression. For example, to make y contain True if x contains 5, and False otherwise, you could write code like this:

If x = 5 Then
    y = True
Else
    y = False
End If

This code is wordier than it needs to be. Remember, the intent is to set the variable y to True if x is equal to 5 and False otherwise. The expression (x = 5) has a truth value of its own—that is, it's either True or False. You can assign that value directly to y in a single statement:

y = (x = 5)

Although it may look confusing, VBA will interpret it correctly. Starting from the right, VBA will calculate the value of the expression x = 5 (either True or False) and assign that value to the variable y. Other languages, including C and Pascal, use distinct assignment and equality operators, making this expression a little clearer. In C, for example, the statement would read

y = (x == 5)

with the "=" performing the assignment and the "==" checking the equality.

Any place you use an expression like the If . . . Then . . . End If statement above, you should be able to replace it with a single assignment statement.

If you find these logical assignments hard to read, you may choose to skip using them, because the improvement in performance is slight (in the test case, the faster version takes around 66 percent as much time as the slow version). If, however, logical assignments seem natural to use and read, then by all means use them. (See Test13a/b for the full test procedures.)

Test 14: use the Not operator to toggle Booleans

In many circumstances, you need to toggle the state of a variable between True and False. You might be tempted to write code like this:

If x = True Then
    x = False
Else
    x = True
End If

You might think that either of the following solutions would be an improvement over the original:

If x Then
    x = False
Else
    x = True
End If

or

x = IIf(x, False, True)

Testing shows that neither is as good as the original expression (and the IIf solution is much slower). But the best solution is to use the following expression:

x = Not x

That way, if x is currently True, it will become False. If it's False, it will become True. For example, if you want to change the Enabled property of a command button to be True if it's False, and False if it's True, you can use code like this:

cmdOK.Enabled = Not cmdOK.Enabled

In our tests, using the Not operator takes around 50 percent as much time as the longer If . . . Then . . . Else statement. (See Test14a/b for the full test procedures.)

Warning:   Don't even consider using Not to toggle truth values for any type of value other than Boolean. Sooner or later, you'll attempt to use Not with a value that contains something besides True (-1) or False (0), and the result will be different than you would expect. Not is meant to operate only with Boolean values and no other data type.

Test 15: Bytes aren't faster than Longs

In an earlier tip, we tried to impress on you that using a smaller data type will result in faster performance than using a larger one, all other things being equal. This isn't always the case, however: if you're performing mathematical operations on integer values, no type will give you the same performance as using a Long variable. That is, given the choice of using a Long, Integer, or Byte data type, using Long will always give you the best performance.

Why is this so? If a Long gives you better performance than a Variant, wouldn't you expect that a Byte would give you better performance than a Long? You might think so, but in reality, your computer's internal architecture uses 32 bits (the same size as a Long) to do all its numerical processing. That is, VBA is optimized for operations involving 32 bits at once. When you use a Long data type, VBA doesn't have to do any extra processing to work with it. When using an Integer, you've added a little overhead, and when using a Byte, you've added substantial overhead. Use Long data types if you're performing calculations that don't require floating point values. (See Test15a/b for the full test procedures.)

Tip:   One place where you'll definitely need to use the Byte data type is when you're working with an array of bytes. In that case, if you're working with individual bytes within a string, or passing a value to the Windows API, you'll need to declare an array of bytes.

Test 16: For . . . Next is faster than Do . . . Loop

Although it makes perfect sense, you may not notice that using a Do . . . Loop in place of a For . . . Next loop is generally slower than it needs to be. Each pass through a Do . . . Loop that iterates a specified number of times requires you to also increment or decrement some sort of loop counter, while a For . . . Next loop does that work for you.

For example, the slower test procedure includes a loop like this:

i = 1
Do Until i > lngRepeats
    j = i
    ' Normally, you'd do something with j here.
    i = i + 1

Loop

and the faster procedure includes this equivalent loop:

For i = 1 To lngRepeats
    j = I
    ' Normally, you'd do something with j here.
Next I

The loops provide the same results, yet the second one runs (in our tests) in about 45 percent of the time of the first. Of course, you can't always replace Do . . . Loop with For . . . Next; you only want to use For . . . Next if the number of loop iterations is fixed, not based on some condition. (See Test16a/b for the full test procedures.)

Test 17: watch out for IIf—it doesn't short-circuit

Shorter code isn't necessarily faster. Although this fact is documented in the VBA online help, it's easy to miss; in the IIf, Choose, and Switch functions, VBA evaluates any and all expressions it finds, regardless of whether they actually need to be evaluated from a logical point of view. Given an expression like this:

varValue = IIf(BooleanExpression, Function1(), Function2())

VBA will call both Function1 and Function2. Not only can this lead to undesired side effects, it can just plain slow down your program. In a case like this, you're better off using the standard If . . . Then . . . Else construct, which will execute only the portions of the statement that fall within the appropriate clause. Given the statement:

If BooleanExpression Then
    varValue = Function1()
Else
    varValue = Function2()
End If

you can be assured that only Function1 or Function2 will end up being called. The same concepts apply for the Choose and Switch functions. If you plan on calling functions from any of these functions, you may be better served by using an If . . . Then . . . Else or a Select Case statement.

Beyond any optimization considerations, IIf is very dangerous when dealing with numeric values and division. If this was your expression:

dblNew = IIf(intY = 0, 0, intX/intY)

it would appear that you had appropriately covered your bases. Your code checks to make sure intY isn't 0 and returns an appropriate value if it is, rather than attempting to divide by 0. Unfortunately, if y is 0, this statement will still cause a runtime error. Because VBA will evaluate both portions of the IIf expression, the division by 0 will occur and will trigger an error. In this case, you need to either trap for the error or use the If . . . Then . . . Else statement. (See Test17a/b for the full test procedures.)

Note:   The IIf function used in queries, forms, or reports doesn't have this same behavior—that is, it works as you'd expect it to in those places. It's only in VBA code, in a module, where you need to avoid IIf because of its overzealous evaluation tendencies.

Tip:   It's also interesting to note that VBA does not short-circuit AND or OR logical expressions. That is, in other languages, when you have an If statement including a compound OR statement (that is, "If x OR y Then"), if it's determined that the first operand is True, the code doesn't even investigate the second operand; the whole expression has to be True. (True OR any expression is True, even True OR Null.) If you write a compound AND statement (that is, "If x AND y Then"), if it's determined that the first operand is False, the code doesn't investigate the second operand; the entire expression must be False. (False AND any expression is False, even False AND Null.) Don't count on this behavior in VBA to avoid runtime errors—it doesn't happen, and VBA will always evaluate both operands in any logical expression before it determines the outcome.

Test 18: If . . . End If is faster than IIf

If the previous tip wasn't enough to make you leery of the IIf function, this one certainly will. In any situation within VBA code, you can always replace a call to the IIf function with a full If . . . Then . . . Else . . . End If statement, and you'll always get better performance. You learned in the previous tip not to ever call functions from the second and third parameters of IIf, but you still pay a price for using IIf, even if you're returning simple literals. For example, you might write an innocuous statement like this:

strValue = IIf(i Mod 2 = 0, "Even", "Odd")

but you'll find that you'll get better performance by rewriting that single line of code, like this:

If i Mod 2 = 0 Then
    strValue = "Even"
Else
    strValue = "Odd"
End If

Basically, we see no reason to ever use IIf in VBA code. Because you can always write an equivalent construct without using the function, and the function call is always slower, just avoid it. (See Test18a/b for the full test procedures.)

Tip:   Again, the same goes for the Switch and Choose functions. Both can easily be replaced using nested If statements or Select Case statements. Although all three functions have their place when called from queries, forms, or reports (where they cannot be simply replaced), avoid them in VBA code.

Test 19: don't call DoEvents on every loop iteration

If you include a tight loop in your VBA code (that is, a loop that runs for a while, and performs calculations without calling any other VBA function) and you want to update the screen or allow mouse clicks or screen activity while the loop is running, you'll find that VBA ties up Access while it runs this loop. A common solution is to insert a call to the VBA DoEvents statement inside the loop. DoEvents effectively yields time to Windows and allows it to update the screen, react to mouse clicks, and perform other background tasks. This would seem to be a perfect solution.

Unfortunately, DoEvents halts your code and doesn't allow it to continue until all the pending Windows activities have been completed. If you call this procedure each time you loop, your code will run significantly slower than it would without DoEvents. In the slower test case, the code calls DoEvents each time through a loop:

For i = 1 To lngRepeats
    DoEvents
Next I

In the faster case, the code only calls DoEvents every tenth time through the loop:

For i = 1 To lngRepeats
    If i Mod 10 = 0 Then
        DoEvents
    End If
Next I

Even this small change, calling DoEvents only once every 10 times through the loop, makes the loop take only 10 percent of the time it did originally. Of course, that makes sense—the loop is doing nothing besides calling DoEvents, and calling it once every 10 times through should take up 10 percent as much time. Even if you add code to the loop that does some processing, that processing won't, in general, take nearly as long as the call to DoEvents, and you'll still end up with code that runs faster. (See Test19a/b for the full procedures.)

Test 20: put the most likely case first in Select Case

When you use a Select Case statement, VBA compares your condition against each of the cases you've provided, from top to bottom, until it finds a case that matches the condition. Say, for example, you wanted to convert a short month name into a month number, and you wrote code like this:

Select Case strMonth
    Case "Jan"
        intMonth = 1
    Case "Feb"
        intMonth = 2

    ' Code removed here.

    Case "Nov"
        intMonth = 11
    Case "Dec"
        intMonth = 12
End Select

Given a short month name (in English), this code sets intMonth to be the corresponding month number.

The problem is that VBA always starts at the top and looks at each case until it finds a match. If you're most often going to be sending "Dec" in as the month to find, the code will still look, every time, at each of the preceding cases, searching for the specified text.

The answer, then, is to write your code so that the item you're most likely to search for is the first in the list. Ranking the Case statements by their likelihood of occurrence, as opposed to placing them in an otherwise logical order, can speed your code significantly. In our tests, we first tried searching for "Dec", (the last Case statement) and then for "Jan" (the first Case statement). In this example (and the numbers would change, of course, depending on the number of Case statements you have), searching for the first case took only 12 percent as long as searching for the final case. The more Case statements you have, the smaller this value can be. (See Test20a/b for the full procedures.)

Test 21: in arrays, For . . . Next is faster than For Each . . . Next

If you want to visit each item in an array, you have two alternatives:

  • Use a For . . . Next loop, looping from the lower bound (determined using the LBound function) to the upper bound (determined using the UBound function) of the array.
  • Use a For Each . . . Next loop, using a variant to retrieve each value in turn.

For Each . . . Next seems simpler because you needn't worry about retrieving the lower and upper bounds—the loop simply takes care of that for you:

For Each varValue In alngValues
    j = varValue
Next varValue

Using For . . . Next requires a bit more effort on your part because you must write the code that finds the lower and upper bounds:

For lngCount = LBound(alngValues) To _
 UBound(alngValues)
    j = alngValues(lngCount)
Next lngCount

You might think that For Each . . . Next would be faster, because it requires less code. That isn't so, in fact. The For . . . Next loop will give you better performance if you're working with arrays. In our tests, the faster version took about 70 percent as long as the slower version. (See Test21a/b for the full procedures.)

Warning:   It's worth noting that although you can use either of these techniques to read items from an array, you can only use the For . . . Next loop to write into array elements. The For Each . . . Next loop retrieves a copy of the data in the array, not the actual array element itself. Although you won't receive an error if you use For Each . . . Next to write into an array, the data won't actually go into the array.

Test 22: in collections, For Each . . . Next is much faster than For . . . Next

Just as with arrays, you can use either of the two techniques listed in the previous section to iterate through a collection. In this case, however, the speed difference is much more pronounced: for collections, using For Each . . . Next takes about 2 percent as much time as using For . . . Next. This is a colossal difference in speed. Make sure you consider using For Each . . . Next to iterate through collections, rather than the commonly used For . . . Next loop. (The code for this example is almost the same as in the previous example and can be found in Test22a/b.)

Tip:   If you have a need to iterate through a collection in any order besides forward (that is, the order in which items were added to the collection), you have no choice—you won't be able to use the faster For Each . . . Next loop. In that case, you may be forced to use a For . . . Next loop instead.

Test 23: don't remove items from a collection to empty it

If you use a VBA Collection object within your application, you may have a need to empty the collection and start fresh. It's tempting to simply remove all the items in the collection, one at a time, and then start adding items again once it's empty. To remove items, however, you must specify either an index or a key value (that's the way the Remove method works). And, if you're going to remove the items by their position, you'll need to loop backwards, so that the indexes don't change as you delete items. You might write code like this, to clear your collection:

For j = col.Count To 1 Step -1
    col.Remove j
Next j

This technique is faster, however:

Set col = New Collection

By setting the variable equal to a New Collection, you've asked VBA to dispose of the original (since there's no variable referring to the original collection, it will automatically be removed from memory) and make col refer to a new, empty Collection object. (See Test23a/b for the full procedures.) In our tests, the faster version took a miniscule 7 percent of the slower version.

Test 24: use early binding

If you declare an object variable using As Object, you're asking VBA to determine the available characteristics of this object at runtime. If you declare the object variable using a specific type, VBA can do the determination of available members at compile-time. The difference in runtime speed is impressive.

For example, referring to an ADO field either as an Object or as an ADODB.Field makes a huge difference in the speed of accessing that object's properties. The test procedures use the following code:

Dim rst As ADODB.Recordset
Dim strName As String
' In the slower case:
' Dim fld As Object
Dim fld As ADODB.Field

Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.Source = "tblTests"
rst.Open

sw.StartTimer
Set fld = rst.Fields(0)
For i = 1 To lngRepeats
    strName = fld.Name
Next I

Declaring fld as ADODB.Field results in times that are around 7 percent of those measured declaring fld as Object.

In every case, if at all possible, declare a variable using the most specific object type that you can. For Access controls, that means using, for example:

Dim cmd As CommandButton

instead of:

Dim cmd As Control

or, worst of all:

Dim cmd As Object

(See Test24a/b for the full test procedures.)

Summary

This chapter presented a variety of suggestions for improving the performance of your Access applications. We covered the following topics:

  • How the Jet engine optimizes and executes queries
  • How to make use of the unsupported ISAMStats method and ShowPlan option
  • How to optimize Access
  • How to optimize queries
  • How to speed up forms
  • How to improve the performance of reports
  • How VBA compiles and loads code and how you can best take advantage of this
  • Suggestions for optimizing your VBA code
  • How to test your own optimization ideas

At every point in designing any application, you're faced with choices. These choices affect how well your application will work, and you need to be informed about the trade-offs in order to best make these choices. This chapter focused on the major areas in which you can improve the performance of your applications. Although we attempted to cover the major areas of optimization, this chapter is not meant to be comprehensive. On the other hand, it makes for a good start.