Efficiency reflections on returning large arrays from functions in VBA-CodePudding

I have been working on a project that does signal processing in VBA for excel with large data-sets (10:s of millions of data points). I have had some performance concerns to the way VBA handled how arrays can be returned from functions. I like the visual appearance of the code when multiple filters are applied as functions. Thus to apply several filters you would do something like this: dResult = Scale(Normalize(Smooth(Integrate(MyBigData))),2) .

However to accomplish this you need a code like this as there is no way of putting values directly into the return-array ScaleData1:

Function ScaleData1(dSource() As Double, dFactor As Double) As Double()
    Dim dTmp() As Double, i As Long
    ReDim dTmp(UBound(dSource))
    For i = 0 To UBound(dSource)
        dTmp(i) = dSource(i) * dFactor
    Next
    ScaleData1 = dTmp
End Function

This creates a temporary dataset dTmp that is populated with the result. Then this array is copied into ScaleData1 which is returned and again copied into dResult. Thus, the data-set needs to be copied two times unnecessarily.

There are at least two different ways to avoid the copying. The function and sub below avoids one out of two copies, and also gives the benefit of a "modify in place" (ip) version that may be handy in some occations:

Function ScaleData2(dSource() As Double, dFactor As Double) As Double()
    ScaleData2 = dSource
    ScaleData2_ip ScaleData2, dFactor
End Function

Sub ScaleData2_ip(dSource() As Double, dFactor As Double)
    Dim i As Long
    For i = 0 To UBound(dSource)
        dSource(i) = dSource(i) * dFactor
    Next
End Sub

The third version avoids all unnecessary copying but do not allow the nice multiple filter calling feature that adds to code readability:

Sub ScaleData3(dSource() As Double, dFactor As Double, dScaled() As Double)
    Dim i As Long
    ReDim dScaled(UBound(dSource))
    For i = 0 To UBound(dSource)
        dScaled(i) = dSource(i) * dFactor
    Next
End Sub

With all this in mind I would expect version 1 to be the slowest and version 3 to be the fastest, and I set out to test this hypothesis with the following code:

Sub Bench()
    Dim dSourceData() As Double, i As Long, t(1 To 3) As Single
    
    Dim dProcessedData1() As Double
    Dim dProcessedData2() As Double
    Dim dProcessedData3() As Double
    
    ReDim dSourceData(50000000)
    For i = 0 To UBound(dSourceData)
        dSourceData(i) = Rnd()
    Next
    
    For i = 1 To 5
        t(1) = t(1) - Timer()
        dProcessedData1 = ScaleData1(dSourceData, 2)
        t(1) = t(1)   Timer()
        
        t(2) = t(2) - Timer()
        dProcessedData2 = ScaleData2(dSourceData, 2)
        t(2) = t(2)   Timer()
        
        t(3) = t(3) - Timer()
        ScaleData3 dSourceData, 2, dProcessedData3
        t(3) = t(3)   Timer()        
    Next
    
    For i = 1 To 3
        Debug.Print "Scaledata" & i & ": " & t(i) * 1000 & " ms"
    Next
End Sub

I was very surprised with the results (this was run repeatedly with the same result):

Scaledata1: 6210,938 ms
Scaledata2: 6744,141 ms
Scaledata3: 6306,641 ms

I guess there is some optimization in the compiler that knows that the arrays will run out-of-scope and avoids copying of the data. Or is the data-copying just so fast that the overhead of the other things overshadow this? I am also concerned about the memory management as you quickly run out of memory when handling large data. Can you shed some light on the surprising benchmarking results?

CodePudding user response：

Please, try the next adapted Bench way:

Declare dSourceData on top of the module (in the declarations area):

  Private dSourceData() as Double

Load the array (once for future tests):

Sub loadSourceData()  'load
    Dim i As Long
    ReDim dSourceData(50000000)
    For i = 0 To UBound(dSourceData)
        dSourceData(i) = Rnd()
    Next
End Sub

Run the adapted Banch Sub multiple times, on the same array:

Sub Bench()
    Dim i As Long, j As Long, t(0 To 3) As Single
    
    Dim dProcessedData1() As Double
    Dim dProcessedData2() As Double
    Dim dProcessedData3() As Double
        
    dProcessedData3() = dSourceData()
    For i = 1 To 5
        t(0) = t(0) - Timer()
         For j = 0 To UBound(dProcessedData3)
            dProcessedData3(j) = dProcessedData3(j) * 2
         Next j
        t(0) = t(0)   Timer
        
        t(1) = t(1) - Timer()
         dProcessedData1 = ScaleData1(dSourceData, 2)
        t(1) = t(1)   Timer()
        
        t(2) = t(2) - Timer()
         dProcessedData2 = ScaleData2(dSourceData, 2)
        t(2) = t(2)   Timer()
        
        t(3) = t(3) - Timer()
         ScaleData3 dSourceData, 2, dProcessedData3
        t(3) = t(3)   Timer()
    Next    
    
    For i = 0 To 3
        Debug.Print "Scaledata" & i & ": " & t(i) * 1000 & " ms"
    Next
End Sub

Results in Immediate Window:

Scaledata0: 3613.281 ms
Scaledata1: 4007.813 ms
Scaledata2: 4445.313 ms
Scaledata3: 4433.594 ms

Scaledata0: 3625 ms
Scaledata1: 3984.375 ms
Scaledata2: 4300.781 ms
Scaledata3: 4304.688 ms

Scaledata0: 3710.938 ms
Scaledata1: 4046.875 ms
Scaledata2: 4472.656 ms
Scaledata3: 4406.25 ms

It looks that using the same array to multiply each its element is a little faster...

It is difficult to make evaluations for each case, since the differences are small and NOT always proportional for each way per testing group. This means that the influence of the other running processes on CPU and memory loading looks to be big enough for the debated case...

CodePudding user response：

To settle this I ran the code below to measure the time for both each function as well as the "assignment" part in each function (or the loop in Scaledata3 where there is no assignment). To my surprise, this change made my computer run out of memory, so I had no reduce the array size by half.

Option Explicit
Public t(1 To 3) As Single
Public t_a(1 To 3) As Single
Public t_ip As Single
Function ScaleData1(dSource() As Double, dFactor As Double) As Double()
    Dim dTmp() As Double, i As Long
    ReDim dTmp(UBound(dSource))
    For i = 0 To UBound(dSource)
        dTmp(i) = dSource(i) * dFactor
    Next
    t_a(1) = t_a(1) - Timer()
    ScaleData1 = dTmp
    t_a(1) = t_a(1)   Timer()
End Function
Function ScaleData2(dSource() As Double, dFactor As Double) As Double()
    t_a(2) = t_a(2) - Timer()
    ScaleData2 = dSource
    t_a(2) = t_a(2)   Timer()
    ScaleData2_ip ScaleData2, dFactor
End Function

Sub ScaleData2_ip(dSource() As Double, dFactor As Double)
    t_ip = t_ip - Timer()
    Dim i As Long
    For i = 0 To UBound(dSource)
        dSource(i) = dSource(i) * dFactor
    Next
    t_ip = t_ip   Timer()
End Sub
Sub ScaleData3(dSource() As Double, dFactor As Double, dScaled() As Double)
    Dim i As Long
    ReDim dScaled(UBound(dSource))
    t_a(3) = t_a(3) - Timer()
    For i = 0 To UBound(dSource)
        dScaled(i) = dSource(i) * dFactor
    Next
    t_a(3) = t_a(3)   Timer()
End Sub
Sub Bench()
    Dim dSourceData() As Double, i As Long, t(1 To 3) As Single
    
    Dim dProcessedData1() As Double
    Dim dProcessedData2() As Double
    Dim dProcessedData3() As Double
    
    ReDim dSourceData(25000000)
    For i = 0 To UBound(dSourceData)
        dSourceData(i) = Rnd()
    Next
    
    For i = 1 To 3
        t(i) = 0: t_a(i) = 0
    Next
    t_ip = 0
    
    For i = 1 To 5
        t(1) = t(1) - Timer()
        dProcessedData1 = ScaleData1(dSourceData, 2)
        t(1) = t(1)   Timer()
        
        t(2) = t(2) - Timer()
        dProcessedData2 = ScaleData2(dSourceData, 2)
        t(2) = t(2)   Timer()
        
        t(3) = t(3) - Timer()
        ScaleData3 dSourceData, 2, dProcessedData3
        t(3) = t(3)   Timer()
    Next
    
    For i = 1 To 3
        Debug.Print "Scaledata" & i & ": " & Round(t(i) * 1000, 0) & " ms where " & IIf(i = 3, "loop", "assignment") & " takes " & Round(t_a(i) * 1000, 0) & " ms"
        If i = 2 Then Debug.Print "    Scaledata2_ip: " & Round(t_ip * 1000, 0) & " ms"
    Next
    
End Sub

The output (similar for multiple runs) were to my surprise this:

Scaledata1: 3973 ms where assignment takes 717 ms
Scaledata2: 3396 ms where assignment takes 797 ms
    Scaledata2_ip: 2469 ms
Scaledata3: 3098 ms where loop takes 2490 ms

Suddenly the ScaleData1 was the slowest. I then considered that the fact that there was a code line after the last assignment in ScaleData1 may have some effect. So I just swapped places of the two last code lines like this:

Function ScaleData1(dSource() As Double, dFactor As Double) As Double()
    Dim dTmp() As Double, i As Long
    ReDim dTmp(UBound(dSource))
    For i = 0 To UBound(dSource)
        dTmp(i) = dSource(i) * dFactor
    Next
    t_a(1) = t_a(1) - Timer()
    t_a(1) = t_a(1)   Timer()   '<= Swapped order
    ScaleData1 = dTmp           '<= Swapped order
End Function

And ran the code again with the following results that are more similar to the first trials:

Scaledata1: 3104 ms where assignment takes 0 ms
Scaledata2: 3342 ms where assignment takes 754 ms
    Scaledata2_ip: 2486 ms
Scaledata3: 3109 ms where loop takes 2477 ms

Moreover, the new code can now be run with the original array size. Thus, the compiler recognizes that dTmp is running out-of-scope and if no other code is located after the last assignment, there is no copying, just reassignment of the pointer from dTmp to ScaleData1. This is good to know. /J