VBA Internals: Performance of Fixed vs. Dynamic Arrays

May 8, 2013 in VBA

Arrays in VBA can have their dimensions either fixed at compile time or modifiable at runtime. Fixed-length arrays are declared by specifying the bounds in the declaration, while dynamic arrays must be initialized using the ReDim statement before they can be used. (See Types of Arrays for more details).

Given the flexibility of dynamic arrays I have sometimes wondered why anyone would use a fixed-length array. Sometimes it is required for byte buffers or in user defined types for passing to and from API methods. But for straight VB code, is there any advantage?

In particular, what is the performance cost of using a dynamic array? I set to measure exactly that, and I present my results here. I’ll give it away from the start: There is essentially no statistical difference in how long it takes to initialize or copy fixed-length arrays vs. dynamic-length arrays. My recommendation, then: Only use fixed-length arrays when an API function or other external limitation requires it!

Methodology

To compare the speed of initializing and copying fixed and dynamic arrays, I used the high-resolution performance timer described in Accurate Performance Timers in VBA. I executed each test 1,000 times and recorded each measurement. I then calculated the overall average and standard deviations of the measurements, and also graphed the histograms of the actual measurements. The code used to actually perform the tests is included at the end of this post.

For both initialization and copying, I measured the performance of three lengths of arrays : 100 elements, 10,000 elements, and 1,000,000 elements. All the arrays were strongly-typed arrays of Long (Int32).

Time to Initialize Arrays

Fixed-length arrays are initialized when execution enters the scope in which they are declared. If a fixed-length array is declared in a method, then, the array is initialized when execution enters the method. Dynamic-length arrays are only initialized when the ReDim statement is executed.

To test the time for initializing arrays I defined four methods: One each for the three sizes of fixed-length arrays, and one for initializing a dynamic-length array. See listing 1 at the end of this post. Here are the results:

Table: Average time to initialize arrays

Time in μs (s x 10-6) to initialize arrays

Fixed Dynamic
Array Length Avg StDev Avg StDev
Small (100) 4.12 0.76 4.51 0.71
Medium (10,000) 15.67 1.36 15.47 1.32
Large (1,000,000) 2401.55 61.38 2401.90 45.31


Histograms: Average time to initialize arrays

Time in μs (s x 10-6) to initialize arrays (100 elements)

Histogram of Array Init Times

Time in μs (s x 10-6) to initialize arrays (10,000 elements)

Histogram of Array Init Times

Time in μs (s x 10-6) to initialize arrays (1,000,000 elements)

Histogram of Array Init Times

Time to Copy Arrays

Because arrays are value types in VBA, simply assigning one array variable to another causes the entire array to be copied. Only dynamic array variables can be assigned to, so all six of the target array variables are dynamic. The code for these tests is in listing 2 at the end of this post. All six array variables are assigned to once before the testing begins in order to pre-allocate sufficient memory for them. In addition, the same target variables are reused for each source.

Table: Average time to copy arrays

Time in μs (s x 10-6) to copy arrays

Fixed Dynamic
Array Length Avg StDev Avg StDev
Small (100) 3.49 0.63 3.40 0.55
Medium (10,000) 20.59 1.81 21.45 1.71
Large (1,000,000) 4117.71 144.44 4112.68 138.08


Histograms: Average time to copy arrays

Time in μs (s x 10-6) to copy arrays (100 elements)

Histogram of Array Copy Times

Time in μs (s x 10-6) to copy arrays (10,000 elements)

Histogram of Array Copy Times

Time in μs (s x 10-6) to copy arrays (1,000,000 elements)

Histogram of Array Copy Times

Appendix: Code Listings

Listing 1: Array Initialization Tests
Public Sub FixedSmall()
    Dim arrLong(99) As Long
End Sub

Public Sub FixedMedium()
    Dim arrLong(9999) As Long
End Sub

Public Sub FixedLarge()
    Dim arrLong(999999) As Long
End Sub

Public Sub DynamicArray(ByVal UpperBound As Long)
    Dim arrLong() As Long
    ReDim arrLong(UpperBound)
End Sub

Public Sub TestArrayInit()

    Dim i As Long, vKey, dctResults As New Dictionary 

    For Each vKey In Array( _
        "FixedSmall", "FixedMedium", "FixedLarge", _
        "DynamicSmall", "DynamicMedium", "DynamicLarge")
        Set dctResults(vKey) = New Collection
    Next
     
    With New PerfTimer
        For i = 1 To 1000
            .Restart: FixedSmall: .Pause
            dctResults("FixedSmall").Add .Elapsed
            
            .Restart: FixedMedium: .Pause
            dctResults("FixedMedium").Add .Elapsed
            
            .Restart: FixedLarge: .Pause
            dctResults("FixedLarge").Add .Elapsed
            
            .Restart: DynamicArray 99: .Pause
            dctResults("DynamicSmall").Add .Elapsed
            
            .Restart: DynamicArray 9999: .Pause
            dctResults("DynamicMedium").Add .Elapsed
            
            .Restart: DynamicArray 999999: .Pause
            dctResults("DynamicLarge").Add .Elapsed
        Next
    End With
    
    ' Code to write all the results to a database table ...
End Sub
Listing 2: Array Copy Tests
Public Sub TestArrayCopy()
    Dim afxSmall(99)      As Long  
    Dim afxMedium(9999)   As Long  
    Dim afxLarge(999999)  As Long  

    Dim adynSmall()       As Long
    Dim adynMed()         As Long
    Dim adynLarge()       As Long
    ReDim adynSmall(99)
    ReDim adynMed(9999)
    ReDim adynLarge(999999)
    
    Dim aTarget1() As Long
    Dim aTarget2() As Long
    Dim aTarget3() As Long
    Dim aTarget4() As Long
    Dim aTarget5() As Long
    Dim aTarget6() As Long 
    
    Dim cnt As Long, i As Long, vKey
    Dim dctResults As New Dictionary  

    For Each vKey In Array( _
        "FixedSmall", "FixedMedium", "FixedLarge", _
        "DynamicSmall", "DynamicMedium", "DynamicLarge")
        Set dctResults(vKey) = New Collection
    Next
     
    aTarget1 = afxSmall
    aTarget2 = afxMedium
    aTarget3 = afxLarge
    aTarget4 = adynSmall
    aTarget5 = adynMed
    aTarget6 = adynLarge
         
    With New PerfTimer
        For i = 1 To cnt
            .Restart: aTarget1 = afxSmall: .Pause
            dctResults("FixedSmall").Add .Elapsed
            
            .Restart: aTarget2 = afxMedium: .Pause
            dctResults("FixedMedium").Add .Elapsed
            
            .Restart: aTarget3 = afxLarge: .Pause
            dctResults("FixedLarge").Add .Elapsed
             
            .Restart: aTarget4 = adynSmall: .Pause
            dctResults("DynamicSmall").Add .Elapsed
            
            .Restart: aTarget5 = adynMed: .Pause
            dctResults("DynamicMedium").Add .Elapsed
            
            .Restart: aTarget6 = adynLarge: .Pause
            dctResults("DynamicLarge").Add .Elapsed
            
            If i Mod 100 = 0 Then DPrint "Cycle", i
        Next
    End With
    
    ' Code to write all the results to a database table ...
End Sub