CacheSize Property Example

This example uses the CacheSize property to show the difference in performance for an operation performed with and without a 30-record cache.

Public Sub CacheSizeX()

   Dim rstRoySched As ADODB.Recordset
   Dim strCnn As String
   Dim sngStart As Single
   Dim sngEnd As Single
   Dim sngNoCache As Single
   Dim sngCache As Single
   Dim intLoop As Integer
   Dim strTemp As String

   ' Open the RoySched table.
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set rstRoySched = New ADODB.Recordset
   rstRoySched.Open "roysched", strCnn, , , adCmdTable

   ' Enumerate the Recordset object twice and record
   ' the elapsed time.
   sngStart = Timer

   For intLoop = 1 To 2
      rstRoySched.MoveFirst

      Do While Not rstRoySched.EOF
         ' Execute a simple operation for the
         ' performance test.
         strTemp = rstRoySched!title_id
         rstRoySched.MoveNext
      Loop
   Next intLoop

   sngEnd = Timer
   sngNoCache = sngEnd - sngStart

   ' Cache records in groups of 30 records.
   rstRoySched.MoveFirst
   rstRoySched.CacheSize = 30
   sngStart = Timer

   ' Enumerate the Recordset object twice and record
   ' the elapsed time.
   For intLoop = 1 To 2

      rstRoySched.MoveFirst
      Do While Not rstRoySched.EOF
         ' Execute a simple operation for the
         ' performance test.
         strTemp = rstRoySched!title_id
         rstRoySched.MoveNext
      Loop
   Next intLoop

   sngEnd = Timer
   sngCache = sngEnd - sngStart

   ' Display performance results.
   MsgBox "Caching Performance Results:" & vbCr & _
      "   No cache: " & Format(sngNoCache, _
      "##0.000") & " seconds" & vbCr & _
      "   30-record cache: " & Format(sngCache, _
      "##0.000") & " seconds"
   rstRoySched.Close

End Sub