ADO RecordSet Batch Retrievals Running Excessively Slowly

Question

I am using Excel-VBA to connect with ADO to an MS-Access database. It is executing a stored procedure I have in the Access database. There are about 900 records being returned with 13 fields. I am using the following VBA code:

Dim RS As ADODB.Recordset

Call OpenDatabase 'Subroutine that opens an ADO connection: DatabaseName

Set RS = DatabaseName.Execute("SELECT * FROM My_Procedure")  'This DOES return a recordset

Do While Not RS.EOF
    Debug.Print RS(0) 
    RS.MoveNext
Loop

Call CloseDatabase 'Another sub

The database connection is made here:

Sub OpenDatabase

    Dim ConnString as String
    Set DB = New ADODB.Connection

    ConnString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & My_DB_Path & "; Persist Security Info=False;"
    With DB
        .ConnectionString = ConnString
        .ConnectionTimeout = 10
        .Open
    End With

End Sub

Here is the query with names generalized:

SELECT Col1, Col2, Col3,
(SELECT Col4 FROM t2 where t2.Col1 = t1.Col1 AND t2.Col2 = t1.Col2 and Col4 IS NOT NULL) As Col4,
(SELECT Col5 FROM t2 where t2.Col1 = t1.Col1 AND t2.Col2 = t1.Col2 and Col5 IS NOT NULL) As Col5,
... (Same through Col13)
FROM t1

Within the stored procedure, fields 1, 2, and 3 are all very simple and everything moves zippy fast. However, fields #4-13 are unfortunately all more complicated select statements and this seems to be part of the problem

This was not exactly a preferable way to do it, but within the limits of MS-Access, this is what I had to do to get the data formatted in the way I need to present it.

When I run this procedure in Access, it takes maybe 15-20 seconds to calculate and display everything in the DataSheet view. When I run the VBA code above, the Do loop takes about .45 seconds to print all 900 rows of RS(0,1,2), but with Debug.Print RS(3->12), it takes more than 280 seconds per field. I suspect that it is recalculating all these embedded subqueries every time I ask for it in VBA, but I do not know why.

Ideally, I want Access to run the procedure and generate the results, and I just pull each record into a VBA variable for further processing. Any ideas of how to speed this retrieval process up?

EDIT TO ADD SAMPLE DATA:

This is a generalized sample of the data the query is operating on and what it is supposed to look like when done. Input is like:

Col1|Col2|Col3|Col4|Col5|...
A   |01  |X   |    |
A   |01  |    |Y   |
A   |02  |X   |    |
A   |02  |    |Y   |
B   |01  |    |X   |
B   |02  |    |X   |
B   |02  |Y   |    |
B   |02  |    |    |Z

Output is like:

Col1|Col2|Col3|Col4|Col5|...
A   |01  |X   |Y   |
A   |02  |X   |Y   |
B   |01  |    |X   |
B   |02  |Y   |X   |Z

It might be helpful to see how the connection is setup, can you post that? Also can you see the field values in the Locals Window prior to iterating over the recordset? — Ryan Wildry
When you say stored procedure, do you mean that you've set up a query in Access? Have you tried using the SQL statement in the 'procedure' directly from Excel to get the recordset? — jhTuppeny
@jhTuppeny Yes, It is a query in Access. I just tried Set RS = Database.Execute("The entire SQL string"). It successfully gave me the recordset, only now even the first three fields that moved fast before are moving at the same slow rate as the last 10... — SandPiper
Can you post the full Access SQL query as it can surely be optimized from running 9 separate subqueries? — Parfait
Unfortunately, I can't post the full query. I can't clean it up enough to remove proprietary info, and it is quite long anyway. The query works, and it only takes about 20 seconds to get all the data in a datasheet view in Access. I just don't understand why even when I use something like RS.GetRows it still takes so long. I was under the impression GetRows got the data and put it into an array, but that isn't working well for me either... — SandPiper

Parfait Parfait · Accepted Answer · 2016-09-05T18:38:18

From your current sample data and desired results, you can certainly optimize the query. Right now, you run 9 separate subqueries to retrieve non-null values from each respective column. Simply, run an aggregate query using MAX() on columns, 3 - 13, grouped on first two columns:

SELECT Col1, Col2, Max(Col3) As C3,  Max(Col4) As C4,  Max(Col5) As C5, ...
       Max(Col13) As C13
FROM t1
GROUP BY Col1, Col2

ADO RecordSet Batch Retrievals Running Excessively Slowly

1 Answers