The ISAMStats Method

[ADezii]

This week's Tip is basically geared to Power Access Users and Gurus who demand the ultimate in efficiency within their Applications. It involves an undocumented feature of Jet 4, and is a technique you can use to return a variety of pieces of information relating to Disk Reads and Writes. It is a Method of the DBEngine Object, and as such is restricted to DAO. Next week, we will discuss comparable functionality in ADO.

The ISAMStats Method is useful when you use it to compare two possible ways of doing something. Using this Method, you can retrieve information on six important statistics:

1. Disk Reads - [0]
2. Disk Writes - [1]
3. Reads from Cache - [2]
4. Reads from Read-Ahead Cache - [3]
5. Locks placed - [4]
6. Locks released - [5]

The basic syntax for the ISAMStats Method is as follows:

lngReturn = DBEngine.ISAMStats(option, [reset])

where option is a Long Integer representing one of the above listed values for each reported statistic, and reset is an optional Boolean value that, when set to True, tells Jet to reset the counter for this particular option.

I will not demonstrate this Method via a Sub-Routine Procedure, GetDAO_ISAMStats(), which accepts the name of a Query or SQL String to execute, and an Optional Argument indicating whether the code should attempt to open a Recordset or Open a Query.

For purposes of this demonstration, I've chosen to Execute a Stored Query (strQuery = qryISAMStats, blnUseRecordset = False). This Query is an Update Query, and is based on a Table consisting of 520,000 Records, 342,000 of which are updated in the process. I will also show you the Statistics generated from the Function, and hopefully, you will see how valuable this Method can be in analyzing various Query Execution Plans.

Public Sub GetDAO_ISAMStats(strQuery As String, Optional blnUseRecordset As Boolean)
Const conDisk_Reads As Integer = 0
Const conDisk_Writes As Integer = 1
Const conReads_From_Cache As Integer = 2
Const conReads_From_Read_Ahead_Cache As Integer = 3
Const conLocks_Placed As Integer = 4
Const conLocks_Released As Integer = 5
 
Dim dbISAM As DAO.Database, rstISAM As DAO.Recordset
 
Dim lngDiskReads As Long, lngDiskWrites As Long, lngReadsFromCache As Long
Dim lngReadsFromReadAheadCache As Long, lngLocksPlaced As Long
Dim lngLocksReleased As Long
 
DoCmd.Hourglass True
 
Set dbISAM = CurrentDb()
 
'Reset all Meters
Call DAO.DBEngine.ISAMStats(conDisk_Reads, True)
Call DAO.DBEngine.ISAMStats(conDisk_Writes, True)
Call DAO.DBEngine.ISAMStats(conReads_From_Cache, True)
Call DAO.DBEngine.ISAMStats(conReads_From_Read_Ahead_Cache, True)
Call DAO.DBEngine.ISAMStats(conLocks_Placed, True)
Call DAO.DBEngine.ISAMStats(conLocks_Released, True)
 
If blnUseRecordset Then
  Set rstISAM = dbISAM.OpenRecordset(strQuery, dbOpenSnapshot)
Else
  dbISAM.Execute strQuery
End If
 
lngDiskReads = DAO.DBEngine.ISAMStats(conDisk_Reads)
lngDiskWrites = DAO.DBEngine.ISAMStats(conDisk_Writes)
lngReadsFromCache = DAO.DBEngine.ISAMStats(conReads_From_Cache)
lngReadsFromReadAheadCache = DAO.DBEngine.ISAMStats(conReads_From_Read_Ahead_Cache)
lngLocksPlaced = DAO.DBEngine.ISAMStats(conLocks_Placed)
lngLocksReleased = DAO.DBEngine.ISAMStats(conLocks_Released)
 
Debug.Print "==========================================="
Debug.Print "Statistics for (" & strQuery & ") - [" & IIf(blnUseRecordset, "Recordset", "Query") & "]"
Debug.Print "Number of Records: " & Format$(DCount("*", "tblMain"), "#,#,#")
Debug.Print "==========================================="
Debug.Print "Disk Reads                     : " & Format$(lngDiskReads, "#,#,#")
Debug.Print "Disk Writes                    : " & Format$(lngDiskWrites, "#,#,#")
Debug.Print "Reads From Cache               : " & Format$(lngReadsFromCache, "#,#,#")
Debug.Print "Reads From Read-Ahead Cache    : " & Format$(lngReadsFromReadAheadCache, "#,#,#")
Debug.Print "Locks Placed                   : " & Format$(lngLocksPlaced, "#,#,#")
Debug.Print "Locks Released                 : " & Format$(lngLocksReleased, "#,#,#")
Debug.Print "==========================================="
 
If blnUseRecordset Then
  rstISAM.Close
  Set rstISAM = Nothing
End If
 
DoCmd.Hourglass False
 
End Sub

Sub-Routine Call:

'Code will Execute the Query - (blnUseRecordset = False)
Call GetDAO_ISAMStats("qryISAMTest", False)

Generated Statistics:

===========================================
Statistics for (qryISAMTest) - [Query]
Number of Records: 520,000
===========================================
Disk Reads                     :    21,499
Disk Writes                    :    17,862
Reads From Cache               : 2,046,721
Reads From Read-Ahead Cache    :    32,873
Locks Placed                   :    16,514
Locks Released                 :    16,256
===========================================

[dima69]

Ok - so we can see the statistics on running that query.
So what is your conclusion ? Is running stored query more efficient than using DAO recordset ?

[ADezii]

Ok - so we can see the statistics on running that query.
So what is your conclusion ? Is running stored query more efficient than using DAO recordset ?

A comparative analysis between the two methodologies yielded very similar results on certain Query types. There is no conclusion, I had to leave something up to you guys to figure out (LOL). The number of Variables involved would be quite large, and it would be up to you, as the Developer, to figure out the Optimal solution for your specific set of circumstances.