Generate Statistics With the OpenSchema Method

[ADezii]

In last week's Tip, I showed you how to use the ISAMStats Method of the DBEngine (DAO) to return vital statistics concerning Query executions such as: Disk Reads and Writes, Cache Reads and Writes, and Locks placed and released. As promised, in this week's Tip I'll demonstrate how to accomplish parallel functionality within the context of ADO using the OpenSchema Method of the Connection Object.

We indicate to the OpenSchema Method that we would like a Provider-specific set of information by providing it a magic number called a GUID instead of the traditional Constant value indicating a specific Schema. A Recordset is subsequently filled with the information that we need.

Unlike the ISAMStats Method, we can Reset all the statistics at once. Once we've Reset the statistics, we can then execute a Query to refill the statistics and then retrieve them. To do this, we use the OpenSchema Method of the Connection Object, which returns a one-row Recordset Object filled with the statistics in various Fields.

I know you are all anxiously waiting, so let's get to the meat-and-potatoes of this Tip. I've encapsulated all logic in a Sub-Routine Procedure called GetADO_ISAMStats which has two Arguments. In this specific case, I'll pass to the Routine the name of a Stored Query (qrySelect) and a Boolean Value (True) indicating that the code will attempt to open a Recordset instead of executing a Query. I could very well have passed the Routine an SQL Statement reflecting an Action Query (Update, Delete, Append, Make Table) and then the value False to execute the SQL Statement.

qrySelect is a simple Select Query which returns values from a Table containing 1,101,764 Records. Criteria are set on two Fields just to make things a little interesting.

The Sub-Routine code, the Call to the Routine, and the generated statistics are listed below for your viewing pleasure. See last week's Tip for reference if you so desire.

Public Sub GetADO_ISAMStats(strQuery As String, Optional blnUseRecordset As Boolean)
'1st set a Reference to the 'Microsoft ActiveX Data Objects X.X Library'
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
 
Set cnn = CurrentProject.Connection
 
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 lngDiskReads As Long, lngDiskWrites As Long, lngReadsFromCache As Long
Dim lngReadsFromReadAheadCache As Long, lngLocksPlaced As Long
Dim lngLocksReleased As Long
 
DoCmd.Hourglass True
 
'Reset the Statistics all at once
cnn.Properties("Jet OLEDB:Reset ISAM Stats") = 1
 
If blnUseRecordset Then
  Set rst = cnn.Execute(strQuery)     'used for this Tip
Else
  cnn.Execute (strQuery)
End If
 
Set rst = cnn.OpenSchema(Schema:=adSchemaProviderSpecific, SchemaID:="{8703b612-5d43-11d1-bdbf-00c04fb92675}")
 
lngDiskReads = rst.Fields(conDisk_Reads)
lngDiskWrites = rst.Fields(conDisk_Writes)
lngReadsFromCache = rst.Fields(conReads_From_Cache)
lngReadsFromReadAheadCache = rst.Fields(conReads_From_Read_Ahead_Cache)
lngLocksPlaced = rst.Fields(conLocks_Placed)
lngLocksReleased = rst.Fields(conLocks_Released)
 
Debug.Print "==========================================="
Debug.Print "Statistics for (" & strQuery & ") - [" & IIf(blnUseRecordset, "Recordset", "Query") & "]"
Debug.Print "[ADO Method] - 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 "==========================================="
 
DoCmd.Hourglass False
End Sub

'Procedure Call
Call GetADO_ISAMStats("qrySelect", True)

OUTPUT:

===========================================
Statistics for (qrySelect) - [Recordset]
[ADO Method] - Number of Records: 1,101,764
===========================================
Disk Reads                     : 6,970
Disk Writes                    :    22
Reads From Cache               : 7,546
Reads From Read-Ahead Cache    :   968
Locks Placed                   :    31
Locks Released                 :    28
===========================================