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:
[CODE=vb]lngReturn = DBEngine.ISAMSt ats(option, [reset])[/CODE]
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_ISAMStat s(), 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.
[CODE=vb]Public Sub GetDAO_ISAMStat s(strQuery As String, Optional blnUseRecordset As Boolean)
Const conDisk_Reads As Integer = 0
Const conDisk_Writes As Integer = 1
Const conReads_From_C ache As Integer = 2
Const conReads_From_R ead_Ahead_Cache As Integer = 3
Const conLocks_Placed As Integer = 4
Const conLocks_Releas ed As Integer = 5

Dim dbISAM As DAO.Database, rstISAM As DAO.Recordset

Dim lngDiskReads As Long, lngDiskWrites As Long, lngReadsFromCac he As Long
Dim lngReadsFromRea dAheadCache As Long, lngLocksPlaced As Long
Dim lngLocksRelease d As Long

DoCmd.Hourglass True

Set dbISAM = CurrentDb()

'Reset all Meters
Call DAO.DBEngine.IS AMStats(conDisk _Reads, True)
Call DAO.DBEngine.IS AMStats(conDisk _Writes, True)
Call DAO.DBEngine.IS AMStats(conRead s_From_Cache, True)
Call DAO.DBEngine.IS AMStats(conRead s_From_Read_Ahe ad_Cache, True)
Call DAO.DBEngine.IS AMStats(conLock s_Placed, True)
Call DAO.DBEngine.IS AMStats(conLock s_Released, True)

If blnUseRecordset Then
Set rstISAM = dbISAM.OpenReco rdset(strQuery, dbOpenSnapshot)
Else
dbISAM.Execute strQuery
End If

lngDiskReads = DAO.DBEngine.IS AMStats(conDisk _Reads)
lngDiskWrites = DAO.DBEngine.IS AMStats(conDisk _Writes)
lngReadsFromCac he = DAO.DBEngine.IS AMStats(conRead s_From_Cache)
lngReadsFromRea dAheadCache = DAO.DBEngine.IS AMStats(conRead s_From_Read_Ahe ad_Cache)
lngLocksPlaced = DAO.DBEngine.IS AMStats(conLock s_Placed)
lngLocksRelease d = DAO.DBEngine.IS AMStats(conLock s_Released)

Debug.Print "============== =============== =============="
Debug.Print "Statistics for (" & strQuery & ") - [" & IIf(blnUseRecor dset, "Recordset" , "Query") & "]"
Debug.Print "Number of Records: " & Format$(DCount( "*", "tblMain"), "#,#,#")
Debug.Print "============== =============== =============="
Debug.Print "Disk Reads : " & Format$(lngDisk Reads, "#,#,#")
Debug.Print "Disk Writes : " & Format$(lngDisk Writes, "#,#,#")
Debug.Print "Reads From Cache : " & Format$(lngRead sFromCache, "#,#,#")
Debug.Print "Reads From Read-Ahead Cache : " & Format$(lngRead sFromReadAheadC ache, "#,#,#")
Debug.Print "Locks Placed : " & Format$(lngLock sPlaced, "#,#,#")
Debug.Print "Locks Released : " & Format$(lngLock sReleased, "#,#,#")
Debug.Print "============== =============== =============="

If blnUseRecordset Then
rstISAM.Close
Set rstISAM = Nothing
End If

DoCmd.Hourglass False

End Sub[/CODE]
Sub-Routine Call:
[CODE=vb]'Code will Execute the Query - (blnUseRecordse t = False)
Call GetDAO_ISAMStat s("qryISAMTest" , False)[/CODE]
Generated Statistics:
[CODE=text]=============== =============== =============
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
=============== =============== =============[/CODE]

[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.