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:

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

The basic syntax for the ISAMStats Method is as follows:

Expand|Select|Wrap|Line Numbers

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.

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 'Code will Execute the Query - (blnUseRecordset = False)

Call GetDAO_ISAMStats("qryISAMTest", False)

Generated Statistics:

Expand|Select|Wrap|Line Numbers

 ===========================================

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

Quote:

Originally Posted by 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 ?

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.

The ISAMStats Method

re: The ISAMStats Method

re: The ISAMStats Method

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about