Microsoft KB Archive/114083

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

Article ID: 114083

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q114083



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.

