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

Parameterized queries can only be implemented by using a stored query. Since stored queries have a precompiled query plan, parameterized queries that contain parameters on indexed columns may not execute efficiently. Since the query engine does not know the values to be passed in a parameter in advance, it can only guess as to the most efficient query plan.  Based on customer performance scenarios that we have examined, we have discovered that in some instances substantial performance gains can be achieved by replacing a stored parameterized query with a temporary query. This means creating the SQL string in code and passing it to the DAO OpenRecordset or Execute methods of the Database object.

Unsupported Tuning Features

The information below has been available, but not thoroughly documented, since Jet 3.0. Microsoft will not support this information and the sole intention of documenting it here is to have a valid measurement to experiment with the ideas in this white paper. Much of the information is excerpted from the Microsoft Jet Database Engine Programmer’s Guide (available from Microsoft Press). These features were implemented primarily for use by the Jet performance team as a way to better measure and improve Jet performance, thus the availability or similar functionality of these features in future releases of Jet is not guaranteed.

The ISAMStats Function

The DAO object model exposes a function that allows the developer to get information about the raw disk reads, writes, locks, and caching. The graphs in this white paper used these functions. The following syntax is used for the ISAMStats function:

ISAMStats(StatNum as Long[, Reset as Boolean]) as Long

This method returns the value of a given engine statistic as defined by StatNum, which is defined in the following table. If the optional Reset argument is supplied, then the statistic defined by StatNum is reset and no value is returned. A Reset argument value of False is equivalent to not supplying the argument. The statistics returned apply to the whole engine, regardless of how many databases or sessions are active, including temporary databases.

StatNum	Description
0	Number of disk reads
1	Number of disk writes
2	Number of reads from cache
3	Number of reads from read-ahead cache
4	Number of locks placed
5	Number of release lock calls

The following code illustrates a sample use of the ISAMStats function:

Sub Main()

Dim dbs As Database, ws As Workspace

Dim strSQL As String

Dim lngDiskRead As Long, lngDiskWrite As Long, _

lngCacheRead As Long, _

lngCacheReadAheadCache As Long, _

lngLocksPlaced As Long, _

lngLocksReleased As Long

' Explicitly set the counters to zero.

lngDiskRead = DBEngine.ISAMStats(0, True)

lngDiskWrite = DBEngine.ISAMStats(1, True)

lngCacheRead = DBEngine.ISAMStats(2, True)

lngCacheReadAheadCache = DBEngine.ISAMStats(3, True)

lngLocksPlaced = DBEngine.ISAMStats(4, True)

lngLocksReleased = DBEngine.ISAMStats(5, True)

Set dbs = OpenDatabase("northwind.mdb", False, False)

Set ws = Workspaces(0)

strSQL = _

"UPDATE Customers SET ContactName = ContactName"

dbs.Execute strSQL, dbFailOnError

' The null transaction ensures no more asynchronous

' activity that could yield inaccurate statistics.

ws.BeginTrans

ws.CommitTrans

' The following ISAMStats calls will retrieve the latest

' values. The values will accumulate until they

' are reset

lngDiskRead = DBEngine.ISAMStats(0)

lngDiskWrite = DBEngine.ISAMStats(1)