Create explicit Field objects when looping on large Recordsets

Create explicit Field objects when looping on large Recordsets

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

Dim rs As New ADODB.Recordsetrs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdTextDo Until rs.EOF    List1.AddItem rs("au_lname") & ", " & rs("au_fname")    rs.MoveNextLooprs.Close

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

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:

Dim rs As New ADODB.RecordsetDim fldFName As ADODB.Field, fldLName As ADODB.Fieldrs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdText' create the two Field objects that reference those two fieldsSet 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.MoveNextLooprs.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:

Dim rs As New ADODB.RecordsetDim varArray() As VariantDim i As Long' let SQL Server concatenate the stringsrs.Open "SELECT au_lname+', '+au_fname FROM authors", "DSN=pubs", , , adCmdText' get all the strings in one operationvarArray() = rs.GetRows()' fill the ListBox controlFor i = 0 To UBound(varArray, 2)    List1.AddItem varArray(0, i)Next


Share the Post: