CacheSize Property Example (VB)

This example uses the CacheSize property to show the difference in performance for an operation performed with and without a 30-record cache.
'BeginCacheSizeVB
    
    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub CacheSizeX()

    'recordset and connection variables
   Dim rstRoySched As ADODB.Recordset
   Dim Cnxn As ADODB.Connection
   Dim strSQLSched As String
   Dim strCnxn As String
    'record variables
   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 connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   
   ' Open the RoySched Table
   Set rstRoySched = New ADODB.Recordset
   strSQLSched = "roysched"
   rstRoySched.Open strSQLSched, strCnxn, , , adCmdTable

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

   For intLoop = 1 To 2
   
      rstRoySched.MoveFirst

      If Not rstRoySched.EOF Then
         ' Execute a simple operation for the
         ' performance test
        Do
            strTemp = rstRoySched!title_id
        rstRoySched.MoveNext
        Loop Until rstRoySched.EOF
      End If
      
   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"
   
   ' clean up
   Cnxn.Close
   rstRoySched.Close
   Set Cnxn = Nothing
   Set rstRoySched = Nothing

End Sub
'EndCacheSizeVB
CacheSize Property Example (VB)

See Also
