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

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.