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

See also  Comparing different methods of testing your Infrastructure-as-Code

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist