How fast can you loop an ADO recordset? 9

[sunaj]

I was doing a mini speed test on looping recordsets - a task that I seem to do all the time in my programs...
Results:

Static,Optimistic, Explicit fields : 6.9180
ForwardOnly,ReadOnly (default), Explicit fields : 0.5117
ForwardOnly,ReadOnly (default), Predefined fields : 0.3125


I was kind of suprised by the last one, Using predefined fields [set f= Rst.fields(..)] is >60 % faster than the explicit method (Rst.fields(..)].

Commments, improvements?

Test code:
---------------------------------------------------------
[sup]
Option Explicit

Private Sub Command1_Click()
Const n As String = "10000"
Dim conn As Connection
Dim Rst As Recordset
Dim s As String, t As Single
Dim f1 As ADODB.Field
Dim f2 As ADODB.Field

Set conn = New Connection
conn.Open "Provider=SQL OLEDB;Integrated Security=SSPI;Persist Security Info=False;User ID=dbo;Initial Catalog=;Data Source="


Set Rst = New Recordset
Rst.Open "SELECT top " & n & " D.Data, D.Pres FROM tblData D", conn, adOpenStatic, adLockOptimistic
t = Timer
If Not Rst.EOF And Not Rst.BOF Then
Do
s = CStr(Rst.Fields("Data&quot) & " " & CStr(Rst.Fields("Data&quot)
Rst.MoveNext
Loop Until Rst.EOF
End If
Rst.Close
Set Rst = Nothing
Text1.Text = "Static,Optimistic, Explicit fields : " & Format(Timer - t, "0.0000&quot & vbCrLf

Set Rst = New Recordset
Rst.Open "SELECT top " & n & " D.Data, D.Pres FROM tblData D", conn
t = Timer
If Not Rst.EOF And Not Rst.BOF Then
Do
s = CStr(Rst.Fields("Data&quot) & " " & CStr(Rst.Fields("Data&quot)
Rst.MoveNext
Loop Until Rst.EOF
End If
Rst.Close
Set Rst = Nothing
Text1.Text = Text1.Text & "ForwardOnly,ReadOnly (default), Explicit fields : " & Format(Timer - t, "0.0000&quot & vbCrLf

Set Rst = New Recordset
Rst.Open "SELECT top " & n & " D.Data, D.Pres FROM tblData D", conn

t = Timer
Set f1 = Rst.Fields("Data&quot
Set f2 = Rst.Fields("pres&quot

If Not Rst.EOF And Not Rst.BOF Then
Do
s = CStr(f1) & " " & CStr(f2)
Rst.MoveNext
Loop Until Rst.EOF
End If
Rst.Close
Set Rst = Nothing
Text1.Text = Text1.Text & "ForwardOnly,ReadOnly (default), Predefined fields : " & Format(Timer - t, "0.0000&quot & vbCrLf

conn.Close
Set conn = Nothing
End Sub
[/sup]
---------------------------------------------------------
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[pgk]

Hi,
I have one suggestion for determining the size of the recordset cache.

1)Write the total number of records in a registry entry or some database table when the application is quit.

2)Retrieve this value from wherever it is stored and determine the recordset cache size at application startup. For example, we could allocate 25% of the total space occupied by the database table. If this space exceeds a certain limit( not to allocate too large memory spaces as the database grows), set it to a fixed value.

3)Repeat step1 at shut down of application.

One can argue that we can always allocate a fixed value( as is done when the 25% exceeds a certain value). But as per my suggestion, we will be allocating only space as per the database size( atleast uptp a certain limit).

Your comments and suggestions would be very useful and appreciated.

Another thing: Is it ok with you if I were to make a FAQ on looping ADO recordsets using the posts in this thread? The permission of those who have posted here is required. It would prve useful to all entry level programmers like myself.

Hope it helps. Let me know what happens.
With regards,
PGK