访问ADO记录集中的字段值的最有效方法是什么？ [英] What is the most efficient way to access the value of a field in an ADO recordset?

问题描述

我面前有一个VB6应用程序，该应用程序通过ADO访问Sql数据库。

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

检索记录集时，该应用程序使用Bang（！）运算符进行访问记录集中的字段，例如RS！OrderId。

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

虽然我知道这种做法，但我从未真正使用过（除非我懒惰），但都没有我使用RS（ OrderId）是因为我一直（或通常）使用完全限定的方法（例如RS.fields（ OrderId）。value。甚至使用.Item属性将其进一步扩展。）

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.

我似乎还记得！因为ADO在将来的某个时候会被弃用。但是它似乎仍然存在于代码中，我只是想知道哪种方法被认为是最佳实践，哪种方法比另一种方法性能更好。

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.

推荐答案

我已经彻底测试了在我的应用程序中使用的VB6和ADO的性能。从记录集中获取数据的绝对最快的方法是使用FIELD对象。返回很多行时，您会注意到性能上的巨大差异。以下是我的应用程序中的代码块（减少以突出显示字段对象的正确使用）。

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

请注意，我为SQL Server存储过程返回的5列设置了字段对象。然后，我在循环中使用它们。使用RS.MoveNext时，它会影响字段对象。

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.

使用上面显示的代码，我可以在不到1秒的时间内将26,000行加载到用户定义的类型中。实际上，运行代码需要0.05秒。在已编译的应用程序中，它甚至更快。

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.

如果不使用字段对象，则至少应使用WITH块。如另一篇文章所述，使用序数位置比其他方法要快（字段方法除外）。如果计划使用顺序位置，则应使用WITH块。例如：

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

使用with块是不错的选择，因为它减少了键入量，同时又加快了代码的执行速度。之所以会出现这种性能提高，是因为VB可以一次设置一个指向字段对象的指针，然后在每次对字段对象的调用中重新使用该指针。

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.

顺便说一句...不喜欢少打字的说法。我经常发现性能更好的代码也是更复杂的代码。借助VB6的智能感知功能，多余的输入也就不那么多了。

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）是15个字符。

我已经习惯了键入：rs（dot）f（dot ）i（开括号）（引号）FieldName（引号）（闭括号）（点）v。这是使用完全限定方法的6次额外按键。

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.

使用with块方法，它将是（点）i（右括号）（引用）FieldName（引用）（右括号）（点）v，即17个按键。

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

结果为：

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

显然，现场方法是赢家。它花费的时间不到bang方法的1/2。另外请注意，序数方法与现场方法相比也具有不错的性能。

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.

这篇关于访问ADO记录集中的字段值的最有效方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！