Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: VB6,VBS
Expertise: Intermediate
May 17, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.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")

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.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
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.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)
Francesco Balena
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date