VBA:变体数组与类型数组与非键集合的迭代速度 [英] VBA: Iteration speed of variant array vs. typed array vs. non-keyed collection

问题描述

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

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:

第一个选项是为每个对象类型开发一个List"类，其中包含添加对象(和扩展数组)、获取 First 和 Last 索引以及对象计数以及通过索引检索对象的方法(后 4 将包括错误处理，以防数组为空).

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).

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

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:

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

强制转换是否提高了速度，还是 vba 仍然需要执行与变体相关的所有类型检查?

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

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

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?

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

Thanks to anyone who can offer advice.

推荐答案

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

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

对于每种类型的集合/数组，我首先添加了 100,000 个SpeedTester"类对象，它只是一个包含长变量(具有 get/set 属性)的 shell 对象.变量的值是循环索引的值(1 到 100,000 之间)

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)

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

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.

结果如下:

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

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

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

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

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.

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

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 类型的数组，它被初始化为 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.

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

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.

简而言之，它们都是我项目的可行替代方案(除了第一种和最后一种方法，首先是因为它的速度很慢，最后是因为我需要动态调整大小的数组).我对集合的实际实现方式或动态数组和静态数组之间的实现差异一无所知.任何进一步的见解将不胜感激.

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.

测试本身的代码(使用动态数组)

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

对于动态数组类TesterList:

And for the dynamic array class 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

最后是非常简单的 SpeedTester 对象类:

And finally, the very simple SpeedTester object class:

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

这篇关于VBA:变体数组与类型数组与非键集合的迭代速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！