adodb performance EXCEL VBA vs vbscript

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
521
I have a rather large query, containing a table join that returns approximately 80k rows; executing this query via excel VBA seems to result in a minute or longer of processing. I don’t have the same speed issues when returning 5-10000 rows in excel; just the one that’s returning 80k rows. I replicated my VBA adodb connections to a vbscript and wonder why the vbscript can return 80k rows in about 3seconds. Big difference in timing 1min vs 3seconds. I assure you, I’m invoking adodb the same ways in VBA vs vbscript; promis it has nothing to do with how I coded it. Any idea’s as to why this is?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,189
Based on the limited information: no idea.
3 seconds sounds like the right ball park for 80,000 records from multiple tables; and a minute is certainly not.
Can you check it on someone else's computer?
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,684
How are you measuring when the data has returned? Please post your code for both versions
 

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
521
was about craft code examples when I discovered the exact bottleneck. I use the adodb getrows method in both excel-vbscript. Directly after getrows (in both excel and vbscript), I create an empty 2d array with 1 extra row, and populate the first row with column names (a header for my 2d array), followed by looping the provided getrows array, for the purpose of transferring to a 2d array containing headers. The vbscript seems to be much quicker at the transfer then excel.

Here is excel code for transfering getrows array into an array with headers:
Code:
        ReDim FormattedDataSet(0 To UBound(QueriedFields), 0 To 0)
        For y = 0 To UBound(QueriedFields)
            FormattedDataSet(y, 0) = QueriedFields(y)
        Next
        For x = 0 To UBound(DataSet, 2)
            ReDim Preserve FormattedDataSet(0 To UBound(FormattedDataSet, 1), 0 To (UBound(FormattedDataSet, 2) + 1))
            For y = 0 To UBound(QueriedFields)
                If Not IsNull(DataSet(y, x)) Then
                    FormattedDataSet(y, UBound(FormattedDataSet, 2)) = DataSet(y, x)
                End If
            Next
        Next
Here is the vbscript code for transfering getrows array into an array with headers:
Code:
            ReDim A(UBound(Temp), (UBound(V, 2) + 1))
            For x = 0 To UBound(Temp)
                A(x, 0) = Temp(x)
            Next
            For x = 0 To UBound(V, 2)
                i = x + 1
                For y = 0 To UBound(V, 1)
                    If IsNull(V(y, x)) = False Then
                        A(y, i) = V(y, x)
                    Else:
                        A(y, i) = ""
                    End If
                Next
            Next

As you can see; same logic, but excel taking nearly 45-55 seconds longer then vbscript.
If I don't do the array to array with headers, they both perform about the same speed (getting query results)
Also; I did try from different computers; same result; I even put the access database on a different server to see if any difference; same all around; I no longer believe this to be a problem related to adodb, but want to understand why the excel for loops are so much slower then my vbscript for loops
 
Last edited:

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
521
may have just answered own question; excel likley running slower due to the redim preserve statement at every row; I will try and get back.
 

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
521
that was the difference; shouldn't have redim preserved every line. Adodb performance is the same between excel and vbscript.
Anyone know a better way to achieve my headerless array to header array, or is this about all we can do for efficiency?
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,684
Probably, Redim Preserve is very expensive - it releases and rebuilds the whole array when it is called
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,189
maybe explain what you're doing. what is the objective? why make the 2D array?
if we understand what is required, there may be a totally different approach

PS. Like leave the data in a recordset. or clone it. or make a disconnected recordset. or CopyFromRecordset (& then separately load the headers)
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Why do you need an array with a header?
 

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
521
I like keeping an access database cached in memory for quick finds-manipulations; looping through a recordset is far more slower then looping an array.
Because I have many tables loaded into memory, it's easier for me to find correct data when I have a header indicated. This is why I add headers to my arrays.
 

Forum statistics

Threads
1,081,517
Messages
5,359,234
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top