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:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may