Microsoft Jet 3,5 Performance Overview and Optimization Techniques. Non-configurable Performance Settings, страница 10

Set rs = gdbOrder.OpenRecordset("Customer")

While Not rs.EOF

rs.Edit

rs![AddressStatus] = rs![AddressStatus] + 1

rs![CTitle] = "President"

rs![CCompany] = "Olivetti"

rs![EntryDate] = "10:20AM"

rs![Country] = 12

rs.Update

rs.MoveNext

Wend

gdbOrder.Execute "UPDATE Customer SET AddressStatus = (AddressStatus+1), Ctitle = ‘President’, Ccompany = ‘Olivetti’, EntryDate = #10:20AM#, Country=12;", dbFailOnError

While this is one of the more extreme examples that we have seen and is only inherent in Jet 3.5 due to removal of implicit transactions for SQL DML statements, it demonstrates that the developer should examine the code for potential performance enhancements by re-coding DAO looping constructs with SQL DML statements.

Implement Persistent Connections with Linked (Attached) Tables

Another performance issue raised by customers using Jet 3.0 was due to a change in how Jet dealt with the .ldb file. The .ldb file is used to track which users have the database open and to track the locking of pages in the .mdb file. In Jet 3.0, the .ldb file was deleted when the last user closed the database. This was done to prevent littering of .ldb files when replication was being used. However, in situations where only one user was accessing a linked table, particularly in a looping construct, a significant performance hit was experienced. This was because linked tables do not keep persistent connections to the database where they reside. This causes a lot of additional I/O to delete, recreate, and establish locks on the .ldb file.

The workaround for this is to have the application keep a persistent connection to any table where other linked tables reside in another database file. This prevents Jet from constantly deleting, creating, and obtaining lock information on the .ldb file. One customer’s scenario was improved from three minutes to thirty seconds by employing this technique.

Use Explicit Transactions When Implementing Online-Transaction Processing (OLTP)

Due to the changes in Jet 3.5 regarding the FlushTransactionTimeout registry setting, it is possible to see significant throughput decreases due to concurrency issues when OLTP is being implemented from many workstations. The reason for this is that Jet 3.5 will cache modified pages until no activity is encountered or the cache is full. Thus the fast throughput of OLTP statements will cause Jet to not flush its cache and release the locks on index and data pages. This may cause many pages to be locked during this time period, which will cause concurrency issues with many users trying to update one row at a time on the same table. The workaround is to use explicit transactions for these scenarios to force Jet to flush its cache when the transaction is committed. Below is an extreme example of throughput degradation when not using an explicit transaction for OLTP type work to quickly update one random row at a time from six workstations.

While this illustrates why you should use explicit transactions for this type of operation, it also illustrates, again, the cost of updating an indexed column in regards to how many rows of indexed columns are locked.

Check Parameterized Queries for Optimal Performance