What is the most efficient way to access the value of a field in an ADO recordset?

Question

I have a VB6 application in front of me, which accesses Sql Databases via ADO.

When retrieving recordsets, the application uses the Bang (!) operators to access the fields in the recordsets Eg RS!OrderId.

Whilst I am aware of this practice, I have never actually used it(except when I have been lazy), Neither have I used RS("OrderId") as I have always (or usually) used the fully qualified method (eg RS.fields("OrderId").value. or even expanded it further using the .Item Property.)

Both return exactly the same value, one is shorter to type than the other.

The reason I have stuck to this method is that sometime in the far distant past, I believe I was told that it was more performant to fully qualify the field, as the code had to translated each occurrence of the ! operator to its fully qualified sister. However, the ! operator reduces typing and as such dev time.

I also seem to recall that the ! for ADO was going to be deprecated at some point in the future. But it still seems to be around in code I just wondered which method is regarded as best practice and which one performs better over the other.

Solution 1

I have thoroughly tested performance of VB6 and ADO for use in my application. The absolute quickest way to get data from a recordset is to use the FIELD object. You will notice a big difference in performance when returning a lot of rows. The following is a block of code from my application (reduced to highlight the proper use of field objects).

Dim fMinLongitude As ADODB.Field
Dim fMinLatitude As ADODB.Field
Dim fMaxLongitude As ADODB.Field
Dim fMaxLatitude As ADODB.Field
Dim fStreetCount As ADODB.Field

If RS.RecordCount = 0 Then
    Exit Sub
End If

Set fMinLongitude = RS.Fields.Item("MinLongitude")
Set fMinLatitude = RS.Fields.Item("MinLatitude")
Set fMaxLongitude = RS.Fields.Item("MaxLongitude")
Set fMaxLatitude = RS.Fields.Item("MaxLatitude")
Set fStreetCount = RS.Fields.Item("StreetCount")

While Not RS.EOF
    LineGridCount = LineGridCount + 1
    With LineGrid(LineGridCount)
        .MinLongitude = fMinLongitude.Value
        .MaxLongitude = fMaxLongitude.Value
        .MinLatitude = fMinLatitude.Value
        .MaxLatitude = fMaxLatitude.Value
    End With
    RS.MoveNext

Wend

RS.Close
Set RS = Nothing

Note that I set field objects for 5 columns returned by a SQL Server stored procedure. I then use them inside a loop. When you do RS.MoveNext, it affects the field objects.

With the code shown above, I can load 26,000 rows into my user defined type in less than 1 second. In fact, running through code it took 0.05 seconds. In the compiled application it's even faster.

If you do not use field objects, then you should at least use a WITH block. As mentioned in another post, using ordinal position is faster than other alternative (with the exception of the field method). If you plan on using ordinal position, then you should use a WITH block. For example:

With RS.Fields
  ID = .Item(0).Value
  Name = .Item(1).Value
  EyeColor = .Item(2).Value
End With

Using a with block is nice because it reduces the amount of typing and at the same time it speeds up the execution of the code. This performance increase occurs because VB can set a pointer to the field object once and then reuse that pointer for each call to the fields object.

By the way... I dislike the "less typing" argument. I often find that better performing code is also more complex code. With VB6's intellisense, the extra typing isn't all that much either.

RS("FieldName") is 15 characters.
I've gotten in to the habit of typing: r s (dot) f (dot) i (open parenthesis) (quote) FieldName (quote) (Close Parenthesis) (dot) v. This is 6 extra key presses for using the fully qualified method.

Using the with block method, it would be (dot) i (open parenthesis) (quote) FieldName (quote) (close parenthesis) (dot) v, which is 17 key presses.

This is one of those situations where a good habit takes little effort and pays off big by having better performing code.

I just did some performance testing. The following test uses a client side cursor which means that all of the data returned by the query is copied to the client computer and stored within the recordset object.

The code I used for the performance test is this:

Private Sub Command1_Click()

    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim Results() As String

    Set DB = New ADODB.Connection
    DB.ConnectionString = "my connection string here"
    DB.CursorLocation = adUseClient
    DB.Open

    Set RS = New ADODB.Recordset
    Call RS.Open("Select * From MapStreetsPoints", DB, adOpenForwardOnly, adLockReadOnly)

    Dim Start As Single
    Dim FeatureId As Long
    Dim PointNumber As Long
    Dim Longitude As Single
    Dim Latitude As Single
    Dim fFeatureId As ADODB.Field
    Dim fPointNumber As ADODB.Field
    Dim fLongitude As ADODB.Field
    Dim fLatitude As ADODB.Field

    ReDim Results(5)

    RS.MoveFirst
    Start = Timer
    Do While Not RS.EOF
        FeatureId = RS!FeatureId
        PointNumber = RS!PointNumber
        Longitude = RS!Longitude
        Latitude = RS!Latitude
        RS.MoveNext
    Loop
    Results(0) = "Bang Method: " & Format(Timer - Start, "0.000")

    RS.MoveFirst
    Start = Timer
    Do While Not RS.EOF
        FeatureId = RS.Fields.Item("FeatureId").Value
        PointNumber = RS.Fields.Item("PointNumber").Value
        Longitude = RS.Fields.Item("Longitude").Value
        Latitude = RS.Fields.Item("Latitude").Value
        RS.MoveNext
    Loop
    Results(1) = "Fully Qualified Name Method: " & Format(Timer - Start, "0.000")

    RS.MoveFirst
    Start = Timer
    Do While Not RS.EOF
        FeatureId = RS.Fields.Item(0).Value
        PointNumber = RS.Fields.Item(1).Value
        Longitude = RS.Fields.Item(2).Value
        Latitude = RS.Fields.Item(3).Value
        RS.MoveNext
    Loop
    Results(2) = "Fully Qualified Ordinal Method: " & Format(Timer - Start, "0.000")

    RS.MoveFirst
    Start = Timer
    With RS.Fields
        Do While Not RS.EOF
            FeatureId = .Item("FeatureId").Value
            PointNumber = .Item("PointNumber").Value
            Longitude = .Item("Longitude").Value
            Latitude = .Item("Latitude").Value
            RS.MoveNext
        Loop
    End With
    Results(3) = "With Block Method: " & Format(Timer - Start, "0.000")

    RS.MoveFirst
    Start = Timer
    With RS.Fields
        Do While Not RS.EOF
            FeatureId = .Item(0).Value
            PointNumber = .Item(1).Value
            Longitude = .Item(2).Value
            Latitude = .Item(3).Value
            RS.MoveNext
        Loop
    End With
    Results(4) = "With Block Ordinal Method: " & Format(Timer - Start, "0.000")

    RS.MoveFirst
    Start = Timer
    Set fFeatureId = RS.Fields.Item("FeatureId")
    Set fPointNumber = RS.Fields.Item("PointNumber")
    Set fLatitude = RS.Fields.Item("Latitude")
    Set fLongitude = RS.Fields.Item("Longitude")
    Do While Not RS.EOF
        FeatureId = fFeatureId.Value
        PointNumber = fPointNumber.Value
        Longitude = fLongitude.Value
        Latitude = fLatitude.Value
        RS.MoveNext
    Loop
    Results(5) = "Field Method: " & Format(Timer - Start, "0.000")

    Text1.Text = "Rows = " & RS.RecordCount & vbCrLf & Join(Results, vbCrLf)

End Sub

The results are:

Rows = 2,775,548

Bang Method: 9.441
Fully Qualified Name Method: 9.367
Fully Qualified Ordinal Method: 5.191
With Block Method: 8.527
With Block Ordinal Method: 5.117
Field Method: 4.316

Clearly the field method is the winner. It takes less than 1/2 the time of the bang method. Also notice that the ordinal methods also have decent performance compared to field method.

Solution 2

Bill Vaughn had a good discussion of this in his article "ADO Performance Best Practices". The conclusion, as indicated by Alex K in the comment, is to use an ordinal such as rs(0) or rs(7).

Bill also discussed a technique of using an enum to provide readable names for the index positions. For example, with a query like:

SELECT CatName, CatType, CatSize from Cats Where...

You could have this enum in VB:

Enum enuCatsQuery
    CatName
    CatType
    CatSize
End Enum

And this code to access the field:

StrMyName = Rs(enuCatsQuery.CatName)