CODEWITHSUNDEEP
sqlvbaexcelms-accessado
SandPiper
SandPiper

Reputation: 2906

ADO RecordSet Batch Retrievals Running Excessively Slowly

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

Upvotes: 0

Views: 463

Answers (1)

Parfait
Parfait

Reputation: 107652

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

Upvotes: 1

Related Questions