ACC: Version 2.0 Database Operations Take Longer Than in 1.x

• Microsoft Access versions 1.0, 1.1, 2.0

SYMPTOMS

Disk-intensive database operations (such as record updates) seem to take longer in Microsoft Access version 2.0 than in version 1.x.

CAUSE

Typically poor performance disk-intensive database operations, such as locking, depend on the environment. For example, a network that has heavy network traffic could cause a performance decrease. Disk operations performed on databases that are opened nonexclusively, or shared, take longer than operations on databases that are opened exclusively, because lock checking occurs for each operation in a shared database.

In addition, changes to the way that nontransaction record writing is done in version 2.0 can, in some cases, cause those operations to take longer than they do in version 1.x.

RESOLUTION

To speed up non-exclusive operations, open the database exclusively whenever possible.

To improve record writing performance in version 2.0, embed your disk- writing routines in transactions (using the BeginTrans and CommitTrans statements). This improves speed by decreasing the number of times Microsoft Access must write data to the disk, because the data is instead written to the transaction buffer in memory. When the transaction is committed, the data is written to the disk, resulting in fewer physical writes to the disk. Using transactions brings performance back to 1.x levels in most cases.

However, avoid putting all write operations into a transaction, because the transaction buffer could conceivably become large enough to decrease the size of the read-cache buffer, which could subsequently slow down read operations. The key in this tradeoff is to place only disk-intensive write operations in transactions, leaving enough room for the read-cache buffer.

Transactions can also improve concurrency in multiuser situations. Consider the following example:

   .
   .
   BeginTrans
     set myrs = mdb.openrecordset("long running query")
   CommitTrans
   .
   .
   debug.print myrs!field1
   .
   .
   myrs.close

The cost in using this method is that there will be a slight pause at the CommitTrans stage when all the record read locks are released. These read locks would otherwise be gradually released when the system had idle time.

REFERENCES

For more information about optimizing performance, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q112117
   TITLE     : ACC2: How to Optimize Microsoft Access Version 2.0
               Performance