VBA：变体数组对类型数组与非键控数据集的迭代速度 [英] VBA: Iteration speed of variant array vs. typed array vs. non-keyed collection
问题描述
我的项目需要一组可动态调整大小的数组用于不同的对象。一个数组可以保存任意数量的对象，可能有成千上万的单个类，但不能是多个类的对象。
大多数情况下，我将通过数组进行迭代，一个键控的集合是不理想的。我想我有两个选项：
第一个选项是为每个对象类型开发一个List类，方法用于添加对象（并扩展数组）获得第一个和最后一个索引和对象计数，并通过索引检索一个对象（后者4将包括错误处理，如果数组为空）。
选项是使用Variant数据类型，使用相同的方法开发单个List类。显然这是一个很少的工作，但我关心的速度。使用变体比输入对象慢多少？注意，在检索时，我总是直接将数组中的变体对象转换为一个类型化的变量：a：
As MyClass
设置myObject = variantList.Get（i）
vba仍然必须执行与变体相关联的所有类型检查吗？
此外，第二个选项比使用非键集合更快吗？我已经阅读，集合迭代很慢，他们是专为查找。这是否适用于非键控集合，或只适用于键值映射集合？
感谢任何可以提供建议的人。
对于每种类型的集合/数组，我们可以使用这些方法，我首先添加了类SpeedTester的100,000个对象，它只是一个包含一个长变量（具有get / set属性）的shell对象。变量的值是循环索引的值（在1和100,000之间）
然后我做了第二个循环，它涉及访问集合/数组中的每个对象并将对象的long属性值分配给long类型的新变量。
结果如下：
方法平均添加时间平均获取时间总时间
收藏索引0.305 25.498 25.803
收集映射1.021 0.320 1.342
收集索引为每0.334 0.033 0.367
每个集合映射1.084 0.039 1.123
类型的动态数组0.303 0.039 0.342
类型的静态数组0.251 0.016 0.266
集合索引和集合映射的方法涉及在集合中保存对象。第一个添加没有键，第二个添加了一个键，该对象的long属性转换为字符串。然后，使用从1到c的索引在for循环中访问这些对象。
接下来的两个方法与前两个方法相同。到收集。但是，对于Get循环，而不是使用带有索引的for循环，我使用了一个for-each循环。
动态数组类型是一个自定义类， SpeedTester类型的数组。每次添加变量时，数组的大小都扩展1个槽（使用ReDim Preserve）。 get-loop是一个for循环，使用1到100,000的索引，这是一个典型的数组。
最后，类型的静态数组只是一个类型的数组SpeedTester，其初始化为100,000个插槽。显然这是最快的方法。奇怪的是，它的大部分速度增益是在而不是添加。我认为对于其他方法，由于需要调整大小，添加将更慢，而获取每个对象将不会比动态数组更快。
I被使用for循环和for-each循环访问索引集合的对象之间的差异所震惊。我也被映射集合的关键查找速度惊讶 - 比索引更快，比静态数组之外的所有其他方法更快。
简而言之，所有可行的替代我的项目（除了第一和最后的方法，首先是因为它的慢，最后，因为我需要动态调整大小的数组）。我完全不知道集合如何实际实现，或动态和静态数组之间的实现差异。非常感谢任何进一步的洞察力。
编辑：
测试本身的代码（使用动态数组）
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
对于i = 1到100000
设置aTester =新SpeedTester
aTester.Number = i
调用c.Add（aTester）
下一个i
昏暗的taa （）
对于i = c.FirstIndex到c.LastIndex
设置aTester = c.Item（i）
Dim n As Long
n = aTester.Number
下一个i
Dim标签作为Double
tag = Timer（）
MsgBox添加时间：& （taa-ts）& vbNewLine& 时间得到：& （tag-taa）
End Sub
而对于动态数组类TesterList： p>
Private fTesters（）作为SpeedTester
公共属性获取FirstIndex GoTo离开
FirstIndex = LBound（fTesters）
离开：
出现错误GoTo 0
结束属性
Get LastIndex（）As Long
错误时转到离开
LastIndex = UBound（fTesters）
离开：
出现错误GoTo 0
结束属性
公共子添加（pTester作为SpeedTester）
错误恢复下
ReDim保存fTesters（1到UBound（fTesters）+ 1）作为SpeedTester
如果Err.Number<> 0 Then
ReDim fTesters（1 To 1）作为SpeedTester
结束如果
设置fTesters（UBound（fTesters））= pTester
0
End Sub
公共函数项（i As Long）As SpeedTester
错误时转到离开
设置项目= fTesters（i）
离开：
出现错误GoTo 0
结束函数
最后，非常简单的SpeedTester对象类：
Private fNumber As Long
Number（）As Long
Number = fNumber
结束属性
公共属性让数字（pNumber As Long）
fNumber = pNumber
结束属性
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).
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)
Does casting improve speed, or does vba still have to perform all the type-checking associated with variants?
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.
I followed Tim Williams's advice and did some speed tests.
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)
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.
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
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.
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.
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.
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.
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:
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:
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屋！
