VBA：变体数组与类型数组与非键控集合的迭代速度

VBA: Iteration speed of variant array vs. typed array vs. non-keyed collection

提问by Swiftslide

My project requires a bunch of dynamically-resizable arrays for different objects. An array may hold any number of objects, potentially thousands, of a single class, but not objects of multiple classes.

我的项目需要一组用于不同对象的可动态调整大小的数组。一个数组可以包含任意数量的单个类的对象，可能是数千个，但不能包含多个类的对象。

Mostly I will be iterating through arrays, thus use of a keyed collection is not ideal. I think I have two options:

大多数情况下，我将遍历数组，因此使用键控集合并不理想。我想我有两个选择：

The first option is to develop a 'List' class for each object type, with methods for adding objects (and expanding the array), getting the First and Last indexes and the object count, and retrieving an object by index (the latter 4 will include error handling in case the array is empty).

第一个选项是为每个对象类型开发一个“列表”类，其中包含添加对象（和扩展数组）、获取第一个和最后一个索引以及对象计数以及按索引检索对象的方法（后 4 个将包括错误处理，以防数组为空）。

The second option is to develop a single 'List' class, with the same methods, using the Variant data type. Obviously this is a lot less work, but I am concerned about speed. How much slower is it to use variants than typed objects? Note that I will always be casting the variant objects in the array directly to a typed variable upon retrieval, a la:

第二种选择是使用 Variant 数据类型开发具有相同方法的单个“List”类。显然这是一个少得多的工作，但我担心速度。使用变体比使用类型对象慢多少？请注意，我将始终在检索时将数组中的变体对象直接转换为类型化变量，例如：

Dim myObject As MyClass
Set myObject = variantList.Get(i)

Does casting improve speed, or does vba still have to perform all the type-checking associated with variants?

强制转换会提高速度，还是 vba 仍然必须执行与变体相关的所有类型检查？

Also, would this second option be faster than using a non-keyed Collection? I have read that Collection iteration is slow, that they are designed for lookup. Does this apply to non-keyed collections, or only key-value-mapped collections?

另外，第二个选项会比使用非键控集合更快吗？我读过 Collection 迭代很慢，它们是为查找而设计的。这适用于非键集合，还是仅键值映射集合？

Thanks to anyone who can offer advice.

感谢任何可以提供建议的人。

回答by Swiftslide

I followed Tim Williams's advice and did some speed tests.

我听从了 Tim Williams 的建议并做了一些速度测试。

For each type of collection/array, I first added 100,000 objects of class "SpeedTester", which was simply a shell object holding a long variable (with get/set properties). The value of the variable was the value of the loop index (between 1 and 100,000)

对于每种类型的集合/数组，我首先添加了“SpeedTester”类的 100,000 个对象，它只是一个持有长变量（具有 get/set 属性）的 shell 对象。变量的值是循环索引的值（介于 1 和 100,000 之间）

Then I did a second loop, which involved accessing each object in the collection/array and assigning the object's long property value to a new variable of type long. I performed 3 rounds per method, and averaged the times for the And and get loops.

然后我进行了第二个循环，它涉及访问集合/数组中的每个对象并将对象的 long 属性值分配给一个新的 long 类型变量。我对每个方法执行了 3 轮，并平均了 And 和 get 循环的时间。

The results are as follows:

结果如下：

Method                      Avg Add Time    Avg Get Time    Total Time
Collection Indexed             0.305          25.498         25.803
Collection Mapped              1.021           0.320          1.342
Collection Indexed For Each    0.334           0.033          0.367
Collection Mapped For Each     1.084           0.039          1.123
Dynamic Array Typed            0.303           0.039          0.342
Static Array Typed             0.251           0.016          0.266

The methods Collection Indexed and Collection Mapped involved holding the objects in a collection. The first were added with no key, the second was added with a key which was the object's long property converted into a string. These objects were then accessed in a for-loop using an index from 1 to c.Count

Collection Indexed 和 Collection Mapped 方法涉及将对象保存在集合中。第一个添加没有键，第二个添加了一个键，该键是对象的 long 属性转换为字符串。然后使用从 1 到 c.Count 的索引在 for 循环中访问这些对象

The next two methods were identical to the first two in the way variables were added to the collection. However, for the Get loop, instead of using a for-loop with an index, I used a for-each loop.

接下来的两种方法在变量添加到集合的方式上与前两种相同。但是，对于 Get 循环，我没有使用带索引的 for 循环，而是使用了 for-each 循环。

Dynamic array typed was a custom class containing an array of type SpeedTester. Each time a variable is added, the size of the array was expanded by 1 slot (using ReDim Preserve). The get-loop was a for-loop using an index from 1 to 100,000, as is typical for an array.

类型化的动态数组是一个包含 SpeedTester 类型数组的自定义类。每次添加变量时，数组的大小都会扩展 1 个插槽（使用 ReDim Preserve）。get-loop 是一个 for 循环，使用从 1 到 100,000 的索引，这对于数组来说是典型的。

Finally the static array typed was simply an array of type SpeedTester, which was initialised with 100,000 slots. Obviously this is the fastest method. Strangely enough, much of its speed gains were in Getting rather than Adding. I would have assumed that adding would be slower for the other methods, due to the need for resizing, while Getting each object would be no faster than a dynamic array.

最后，静态数组类型只是一个 SpeedTester 类型的数组，它被初始化为 100,000 个插槽。显然这是最快的方法。奇怪的是，它的大部分速度提升都是在获取而不是添加。由于需要调整大小，我会假设其他方法的添加会更慢，而获取每个对象不会比动态数组快。

I was astounded by the difference between using a for-loop and a for-each loop to access an indexed collection's objects. I was also suprised by the mapped collection's key lookup speed - much, much faster than indexing and comparable to all other methods except the static array.

我对使用 for 循环和 for-each 循环访问索引集合的对象之间的差异感到震惊。我也对映射集合的键查找速度感到惊讶 - 比索引快得多，并且可以与除静态数组之外的所有其他方法相媲美。

In short, they're all viable alternatives for my project (except for the 1st and last methods, first because of its slowness, last because I need dynamically resizable arrays). I know absolutely nothing about how the collections are actually implemented, or the implementation differences between a dynamic and static array. Any further insight would be much appreciated.

简而言之，对于我的项目，它们都是可行的替代方案（除了第一个和最后一个方法，首先是因为它速度慢，最后是因为我需要动态调整大小的数组）。我完全不知道集合是如何实际实现的，或者动态和静态数组之间的实现差异。任何进一步的见解将不胜感激。

EDIT: The code for the test itself (using the dynamic array)

编辑：测试本身的代码（使用动态数组）

Public Sub TestSpeed()
    Dim ts As Double
    ts = Timer()

    Dim c As TesterList
    Set c = New TesterList

    Dim aTester As SpeedTester

    Dim i As Long
    For i = 1 To 100000
        Set aTester = New SpeedTester
        aTester.Number = i

        Call c.Add(aTester)
    Next i

    Dim taa As Double
    taa = Timer()

    For i = c.FirstIndex To c.LastIndex
        Set aTester = c.Item(i)

        Dim n As Long
        n = aTester.Number
    Next i

    Dim tag As Double
    tag = Timer()

    MsgBox "Time to add: " & (taa - ts) & vbNewLine & "Time to get: " & (tag - taa)
End Sub

And for the dynamic array class TesterList:

对于动态数组类 TesterList：

Private fTesters() As SpeedTester

Public Property Get FirstIndex() As Long
    On Error GoTo Leave

    FirstIndex = LBound(fTesters)

Leave:
    On Error GoTo 0
End Property

Public Property Get LastIndex() As Long
    On Error GoTo Leave

    LastIndex = UBound(fTesters)

Leave:
    On Error GoTo 0
End Property

Public Sub Add(pTester As SpeedTester)
    On Error Resume Next

    ReDim Preserve fTesters(1 To UBound(fTesters) + 1) As SpeedTester
    If Err.Number <> 0 Then
        ReDim fTesters(1 To 1) As SpeedTester
    End If

    Set fTesters(UBound(fTesters)) = pTester

    On Error GoTo 0
End Sub

Public Function Item(i As Long) As SpeedTester
    On Error GoTo Leave

    Set Item = fTesters(i)

Leave:
    On Error GoTo 0
End Function

And finally, the very simple SpeedTester object class:

最后，非常简单的 SpeedTester 对象类：

Private fNumber As Long

Public Property Get Number() As Long
    Number = fNumber
End Property

Public Property Let Number(pNumber As Long)
    fNumber = pNumber
End Property