ACC: Version 2.0 Database Operations Take Longer Than in 1.x
ID: Q114083
|
The information in this article applies to:
-
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
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