467,145 Members | 1,037 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Generate Statistics With the OpenSchema Method

ADezii
Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub GetADO_ISAMStats(strQuery As String, Optional blnUseRecordset As Boolean)
  2. '1st set a Reference to the 'Microsoft ActiveX Data Objects X.X Library'
  3. Dim cnn As ADODB.Connection
  4. Dim rst As ADODB.Recordset
  5.  
  6. Set cnn = CurrentProject.Connection
  7.  
  8. Const conDisk_Reads As Integer = 0
  9. Const conDisk_Writes As Integer = 1
  10. Const conReads_From_Cache As Integer = 2
  11. Const conReads_From_Read_Ahead_Cache As Integer = 3
  12. Const conLocks_Placed As Integer = 4
  13. Const conLocks_Released As Integer = 5
  14.  
  15. Dim lngDiskReads As Long, lngDiskWrites As Long, lngReadsFromCache As Long
  16. Dim lngReadsFromReadAheadCache As Long, lngLocksPlaced As Long
  17. Dim lngLocksReleased As Long
  18.  
  19. DoCmd.Hourglass True
  20.  
  21. 'Reset the Statistics all at once
  22. cnn.Properties("Jet OLEDB:Reset ISAM Stats") = 1
  23.  
  24. If blnUseRecordset Then
  25.   Set rst = cnn.Execute(strQuery)     'used for this Tip
  26. Else
  27.   cnn.Execute (strQuery)
  28. End If
  29.  
  30. Set rst = cnn.OpenSchema(Schema:=adSchemaProviderSpecific, SchemaID:="{8703b612-5d43-11d1-bdbf-00c04fb92675}")
  31.  
  32. lngDiskReads = rst.Fields(conDisk_Reads)
  33. lngDiskWrites = rst.Fields(conDisk_Writes)
  34. lngReadsFromCache = rst.Fields(conReads_From_Cache)
  35. lngReadsFromReadAheadCache = rst.Fields(conReads_From_Read_Ahead_Cache)
  36. lngLocksPlaced = rst.Fields(conLocks_Placed)
  37. lngLocksReleased = rst.Fields(conLocks_Released)
  38.  
  39. Debug.Print "==========================================="
  40. Debug.Print "Statistics for (" & strQuery & ") - [" & IIf(blnUseRecordset, "Recordset", "Query") & "]"
  41. Debug.Print "[ADO Method] - Number of Records: " & Format$(DCount("*", "tblMain"), "#,#,#")
  42. Debug.Print "==========================================="
  43. Debug.Print "Disk Reads                     : " & Format$(lngDiskReads, "#,#,#")
  44. Debug.Print "Disk Writes                    : " & Format$(lngDiskWrites, "#,#,#")
  45. Debug.Print "Reads From Cache               : " & Format$(lngReadsFromCache, "#,#,#")
  46. Debug.Print "Reads From Read-Ahead Cache    : " & Format$(lngReadsFromReadAheadCache, "#,#,#")
  47. Debug.Print "Locks Placed                   : " & Format$(lngLocksPlaced, "#,#,#")
  48. Debug.Print "Locks Released                 : " & Format$(lngLocksReleased, "#,#,#")
  49. Debug.Print "==========================================="
  50.  
  51. DoCmd.Hourglass False
  52. End Sub
Expand|Select|Wrap|Line Numbers
  1. 'Procedure Call
  2. Call GetADO_ISAMStats("qrySelect", True)
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. ===========================================
  2. Statistics for (qrySelect) - [Recordset]
  3. [ADO Method] - Number of Records: 1,101,764
  4. ===========================================
  5. Disk Reads                     : 6,970
  6. Disk Writes                    :    22
  7. Reads From Cache               : 7,546
  8. Reads From Read-Ahead Cache    :   968
  9. Locks Placed                   :    31
  10. Locks Released                 :    28
  11. ===========================================
Dec 17 '07 #1
  • viewed: 5281
Share:

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Joe User | last post: by
reply views Thread by Denis ERCHOFF | last post: by
reply views Thread by Marcin Grzębski | last post: by
4 posts views Thread by abcd | last post: by
17 posts views Thread by romixnews@googlemail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.