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

It is important to note that using the SetOption method only affects the run-time values of the registry and does not physically change the values in the registry. Thus, once Jet is restarted, it will read the values in the registry. This means that in order to control Jet’s registry setting the developer must use the SetOption method in code that executes every time an application starts. Below is a code sample that illustrates how a developer might use the SetOption method to optimize code to take advantage of Jet’s buffer setting:

Sub Main()

On Error GoTo ErrorHandler

Dim db As Database, rs As Recordset, ws As Workspace

Dim strCompanyName As String, _

strContactName As String, _

lngReads As Long, lngWrites As Long

Set db = _

OpenDatabase("c:\northwind.mdb", False, False)

DBEngine.SetOption dbMaxBufferSize, 128

Set rs = db.OpenRecordset _

("SELECT * FROM Customers", dbOpenDynaset)

Set ws = Workspaces(0)

lngReads = DBEngine.ISAMStats(0, True)

lngWrites = DBEngine.ISAMStats(1, True)

While Not rs.EOF

rs.Edit

strCompanyName = rs!CompanyName

strContactName = rs!ContactName

rs!CompanyName = strCompanyName

rs!ContactName = strContactName

rs.Update

rs.MoveNext

Wend

' The null transaction ensures no more asynchronous

' activity that could yield inaccurate statistics.

ws.BeginTrans

ws.CommitTrans

lngReads = DBEngine.ISAMStats(0)

lngWrites = DBEngine.ISAMStats(1)

MsgBox "Total reads " & CStr(lngReads) & _

" Total writes " & CStr(lngWrites)

Exit Sub

ErrorHandler:

MsgBox "An error has occurred " & Err & " " & Error

Resume Next

End Sub

Note:  If the dbMaxBufferSize value above is modified from 128 to 2048, the number of writes will decrease from 20 to 11.   This is an extreme example to illustrate the point while using the Northwind database.  Using these numbers for a real world scenario is not recommended.

Programmatic Control of Flushing Jet’s Write Cache

While programmatically flushing Jet’s write cache isn’t necessarily a performance enhancement, it allows developers to guarantee that their data has been written to disk after they issue the DAO CommitTrans method. While Jet does tell the operating system to write its data to disk after a CommitTrans, the operating system has a lazy-write cache that does not necessarily write data to disk, but does notify Jet that the data has been written to its cache. While Jet could tell the operating system to bypass its lazy-write cache, doing so would cause significant performance degradation. Therefore,  to give developers the best default performance, but allow for increased functionality, Jet exposed an interface to bypass Windows 95’s and Windows NT Server/Workstation’s lazy-write cache. (Note: This feature works only for Windows 95 and Windows NT Server/Workstation. If the .mdb file is used with any other operating system, the feature will not work.) You can programmatically flush the cache by using an additional property on the CommitTrans method called dbForceOSFlush (for example, ws.CommitTrans dbForceOSFlush).

This property should be used only when it is critical to know that all the data in a transaction has been written to disk before proceeding with the next command. Below is a chart demonstrating the performance impact when utilizing this property.

Programmatic Control of Refreshing the Cache

A feature of Jet 3.0 was the ability to have performance parity between a database being opened as shared and a database being opened exclusively. This was largely accomplished by improved buffer refreshing. However, to implement that feature, it was necessary to modify the behavior of PageTimeout to check the database header page (DBH). The default for checking the DBH was determined by the PageTimeOut setting in the registry and by default would retry it every five seconds. While this proved optimal for performance, the drawback was that a user could go for almost ten seconds before seeing changes made by other users. The workaround for this behavior was to set the PageTimeOut registry setting to a lower value. However, this resulted in a performance hit as Jet was forced to read the DBH more frequently, resulting in increased I/O.