excel vba: recordset joining and performance

Question

The Context

I have an app in excel VBA for making read-only queries on a remote database.

Queries are executed from UDF's. My app passes an array of data from the recordset object to the function and Excel's fast process for writing an array to a cell range is invoked.

The Challenge

The app must be able to optionally return field names at the top of the dataset. This is presenting a huge performance challenge for me. The only way I know of to append or prepend to a 2D array in VBA is to loop through the entire array. Normally, I'm spared such a loop by passing the recordset.getRows() object directly to my UDF. However, when combining the list of fields and the result of the query with the looping method (the only method I'm aware of) I double or triple my calculation time for sizable queries.

I benchmarked this: for a query of 2k rows and 5 fields, average calc time without field names included is 4.3 seconds, vs. 9.8 seconds with field names

My first try was to combine the field names and recordset on the server using a UNION clause in my select statement (my server is MySQL). This does not work, however, since UNION forces data-type equality, implicitly converting my numerical data to strings. To convert them back I'd have to loop through the array, negating any efficiency gained.

My Question

Is there any object method of the recordset object or of VBA arrays that could be called upon to prepend a row to a large array without looping through the entire large array? The field names are all known before the MySQL query is executed.

My loop for joining the arrays is below. Define a new array arr of length of the recordset + 1, then loop through it, first adding the fields, then each row of the recordset array:

For r = LBound(arr, 1) To UBound(arr, 1)
            If r = LBound(arr, 1) Then
                arr(r) = fieldArray
            Else                    
                arr(r) = Application.Index(rs_array, r - 1, 0)
            End If           
Next

In your "join" code it looks like you're going from a 2-D array to a 1-D array (an array of arrays). Is that intentional? — Tim Williams
Is the purpose of the field names so as to reproduce them at the top of the limited data set on the Excel sheet? If so, perhaps just use a second array to hold them. eg: V = Range(row_of_field_names) would produce an array of size V(1 to 1, 1 to num_of_field_names) . This should be faster than looping through the entire array. — Ron Rosenfeld
@RonRosenfeld - that was my first thought, but the OP is using UDF's to pass the results back to the worksheet, so only a single array can be returned — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2015-07-04T05:26:01

Using Application.Index is possibly the slowest way to combine your arrays: use a regular nested loop instead and you won't even notice any hit -

Sub TT()

    Dim a(1 To 2000, 1 To 10)

    Dim b(1 To 2000, 1 To 10)
    Dim cc(1 To 2000)

    Dim r, c, t

    t = Timer
    For r = 1 To 2000
        For c = 1 To 10
            b(r, c) = a(r, c)
        Next c
    Next r
    Debug.Print "Loop", Timer - t '>> 0.015625 sec

    t = Timer
    For r = 1 To 2000
        cc(r) = Application.Index(a, r, 0)
    Next r
    Debug.Print "Index", Timer - t '>> 4.195313 sec

End Sub

excel vba: recordset joining and performance

The Context

The Challenge

My Question

1 Answers