CacheSize Property Example (VB)

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.

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