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

ID: Q114083

The information in this article applies to:

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

This partial code construction allows other users to edit records as soon as the query is finished running. If transactions are not used, or the CommitTrans statement is after the myrs.close statement, the read locks on the index pages are not released as quickly, thereby not allowing other users to commit changes to their data.

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:

Q112117 ACC2: How to Optimize Microsoft Access Version 2.0 Performance

For more information about optimizing Microsoft Access performance, search for "optimizing performance" then "Optimizing Microsoft Access Performance" using the Microsoft Access Help menu.

Additional query words: reading writing caching 


Keywords          : kbusage GnlOthr 
Version           : 1.0 1.1 2.0
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 3, 1999