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 = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	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