Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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