This example uses the CacheSize property to show the difference in performance for an operation performed with and without a 30-record cache.
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
Dim intRecords As Integer
' Open the RoySched table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstRoySched = New ADODB.Recordset
rstRoySched.Open "roysched", strCnn
With rstRoySched
' Enumerate the Recordset object twice and record
' the elapsed time.
sngStart = Timer
For intLoop = 1 To 2
.MoveFirst
Do While Not .EOF
' Execute a simple operation for the
' performance test.
strTemp = !title_id
.MoveNext
Loop
Next intLoop
sngEnd = Timer
sngNoCache = sngEnd - sngStart
' Cache records in groups of 30 records.
.MoveFirst
.CacheSize = 30
sngStart = Timer
' Enumerate the Recordset object twice and record
' the elapsed time.
For intLoop = 1 To 2
intRecords = 0
.MoveFirst
Do While Not .EOF
' Execute a simple operation for the
' performance test.
strTemp = !title_id
intRecords = intRecords + 1
.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"
.Close
End With
End Sub
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.