迭代大型数据集的Excel VBA与Javascript性能

Excel VBA vs Javascript Performance for Iterating Large Datasets

本文关键字：Javascript 性能 VBA Excel 大型 数据集 迭代      更新时间：2023-09-26

我目前正在做一个项目，涉及到将大型数据集过滤到可管理的数据点数量，这些数据点可以绘制在图表上。

我用Javascript编写了以下代码，它遍历一系列数据，并在给定步长处挑选出第一个值，在本例中，起始值= 0，步长= 0.1。这非常有效并且执行得非常快;我没有量化它，但肯定<1秒>10000个数据点。

var data = [ ... ];
var filteredData = [];
var index = 0;
var step = 0.1;
for (var i=0; i < data.length; i++) {
  if(data[i] >= index) {
    filteredData.push(data[i]);
    index+=step;
  }
}

Codepen Javascript with Mini Sample Dataset

然而，我们所有的数据都是作为Excel工作簿进来的，所以我使用VBA将代码重写为Excel宏，如下所示，将数据点输出到相邻的列。与JS相比，处理相同数量的数据点需要很长时间，处理10000个数据点大约需要20秒。

Dim dataRange As Range
Set dataRange = Range(Range("A8"), Range("A8").End(xlDown))
Dim index As Double
Dim stepsize As Double
Dim outputRow As Integer
index = 0
step = 0.1
outputRow = 8
For Each cell In dataRange
    If cell.Value >= index Then
        ActiveSheet.Cells(outputRow, 2).Value = cell.Value
        index = index + stepsize
        outputRow = outputRow + 1
    End If
Next cell

为什么这两种方法之间存在如此巨大的差异?有什么明显的效率低下关于我的VB代码?我希望这个问题不要太模糊!

非常感谢，Adam

看看你的代码的数组实现，它几乎和JS一样快。

对于10,000个数据点，它将花费(至少在我的机器上)几分之一秒。

Sub test()
    Dim dataRange As Range
    Set dataRange = Range(Range("A8"), Range("A8").End(xlDown))
    Dim index As Double
    Dim stepsize As Double
    Dim outputRow As Long
    index = 0
    step = 0.1
    outputRow = 8

    '/ Array implementation in VBA
    '/ Its almost at the same speed.
    '----------------------------------------------------
    Dim lctr        As Long
    Dim oRow        As Long
    Dim arrOut()
    Dim arr
    arr = dataRange
    For lctr = LBound(arr) To UBound(arr)
        If arr(lctr, 1) >= index Then
            index = index + stepsize
            oRow = oRow + 1
            ReDim Preserve arrOut(1 To 1, 1 To oRow)
            arrOut(1, oRow) = arr(lctr, 1)
        End If
    Next
    arrOut = Application.Transpose(arrOut)
    ActiveSheet.Cells(8, 2).Resize(UBound(arrOut)) = arrOut
    '------------------------------------------------------------

'    For Each cell In dataRange
'        If cell.Value >= index Then
'            ActiveSheet.Cells(outputRow, 2).Value = cell.Value
'            index = index + stepsize
'            outputRow = outputRow + 1
'        End If
'Next cell
End Sub

我从cyobashu中获得了灵感，但通过避免重复调用Redim Preserve和Transpose方法来提高性能。

当我对1m行运行cyboashu的代码时，大约需要16秒。当我对1m行运行下面的代码时，大约需要1秒。

我也修复了我认为是一个错字step = 0.1应该是stepsize = 0.1 

Sub test()
    Dim dataRange As Range
    Set dataRange = Range(Range("A8"), Range("A8").End(xlDown))
    Dim index As Double
    Dim stepsize As Double
    Dim outputRow As Long
    index = 0
    stepsize = 0.1
    outputRow = 8

    '/ Array implementation in VBA
    '/ Its almost at the same speed.
    '----------------------------------------------------
    Dim lctr        As Long
    Dim oRow        As Long
    Dim arrOut()
    Dim arr
    arr = dataRange
    ReDim arrOut(LBound(arr) To UBound(arr), LBound(arr, 2) To UBound(arr, 2)) As Variant
    For lctr = LBound(arr) To UBound(arr)
        If arr(lctr, 1) >= index Then
            index = index + stepsize
            oRow = oRow + 1
            arrOut(oRow, 1) = arr(lctr, 1)
        End If
    Next
    ActiveSheet.Cells(8, 2).Resize(oRow) = arrOut
End Sub

很多性能问题来自于不良的访问方法。就VBA和JS的RAW性能而言，如果我们制作相同的2个代码示例，将数据映射到数组中:

Const max As Long = 50000000
Sub test()
    Dim i As Long, arr(1 To max) As Long
    DEV.Timer_Start
    For i = 1 To max
         arr(i) = i
    Next
    DEV.Timer_Stop
End Sub

和

cons max = 50000000
var arr=[]
x=performance.now()
for(var i=0;i<max;i++) arr.push(i)
console.log((performance.now()-x)/1000)

: 

VBA Average time: 1.01371899999867 seconds
JS  Average time: 1.81799999999930 seconds

所以在原始计算时间方面，JS似乎比VBA慢得多，但这主要是因为这两种操作在内部是完全不同的。

VBA定义了一个200000000字节长的内存块，然后填充每个字节。JavaScript更加动态地执行，并逐渐扩展它的内存占用，如下所示:

Const max As Long = 50000000
Sub test()
    Dim i As Long, arr As Collection
    Set arr = New Collection
    DEV.Timer_Start
    For i = 1 To max
         Call arr.Add(i)
    Next
    DEV.Timer_Stop
End Sub

这会降低VBA代码的运行速度。

对于500,000行测试，我得到以下结果:

VBA fixed size long array:      0.00933219999933 seconds
VBA fixed size variant array:   0.01075579999815 seconds
JS dynamic size variant array:  0.03299999999953 seconds
VBA Collection:                 0.10702589999709 seconds
VBA dynamic size long array:    0.60271329999886 seconds
VBA stdArray:                   7.95831580000231 seconds
VBA dynamic size variant array: 8.36757760000182 seconds

注:starray是一个面向对象的替代JS数组我已经创建。显然，我自己的库的性能有很多改进!

编辑

我现在意识到，stdArray的令人难以置信的缓慢得分的原因是直接与内部使用的变量类型有关。这里我对一个变量数组进行循环，结果循环耗时8.368秒。

Sub test5()
    Dim i As Long, arr() As Variant
    ReDim arr(1 To 1) As Variant
    DEV.Timer_Start
    For i = 1 To max
         ReDim Preserve arr(1 To UBound(arr) + 1) As Variant
         arr(i) = i
    Next
    DEV.Timer_Stop
End Sub

这需要8.368秒来运行。这确实说明了在创建数组时选择正确的变量类型是多么重要。在VBA中使用可变数据类型时，一切都非常慢。

编辑:

包含测试用例和性能细节的链接GIST: https://gist.github.com/sancarn/1f92164f1b53fcd940640f680a06b426

相关文章：