Binaryworld - Create explicit Field objects when looping on large Recordsets ... [ VB -> ADO ]

Whenever you access a database field through the Recordset object you incur in a slight overhead. Take for example the following code snippet:

Click here to copy the following block

Dim rs As New ADODB.Recordset
rs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdText

Do Until rs.EOF
List1.AddItem rs("au_lname") & ", " & rs("au_fname")
rs.MoveNext
Loop
rs.Close

The references to the table's fields inside the Do...Loop block is actually carried out by ADO as follows:

Click here to copy the following block

List1.AddItem = rs.Fields("au_lname") & ", " & rs.Fields("au_fname")

and the overhead comes from the fact that ADO must locate the referenced fields in the Fields collection. You can make your code up to 3-4 times faster by explicitly creating Field objects before running the loop, as follows:

Click here to copy the following block

Dim rs As New ADODB.Recordset
Dim fldFName As ADODB.Field, fldLName As ADODB.Field

rs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdText

' create the two Field objects that reference those two fields
Set fldLName = rs.Fields("au_lname")
Set fldFName = rs.Fields("au_fname")

Do Until rs.EOF
  ' here you use the Field object's default Value property
  List1.AddItem fldLName & ", " & fldFName
  rs.MoveNext
Loop
rs.Close

However, bear in mind that looping on all the records in a Recordset rarely is your fastest option. For example, in this particular case you can populate the ListBox control faster using the following approach:

Click here to copy the following block

Dim rs As New ADODB.Recordset
Dim varArray() As Variant
Dim i As Long

' let SQL Server concatenate the strings
rs.Open "SELECT au_lname+', '+au_fname FROM authors", "DSN=pubs", , , adCmdText
' get all the strings in one operation
varArray() = rs.GetRows()
' fill the ListBox control
For i = 0 To UBound(varArray, 2)
List1.AddItem varArray(0, i)
Next