Append Query runs slower in VBA than if I click on it in Access 2007 interface. Why?

[drumahh]

My query, "Append_History_Query," appends Oracle data to a local Access table. It has 2 date parameters. It has one join from one Oracle table to another. It is appending approximately 20,000 records, with about 20 fields.

The originating tables are on the Oracle server. There is a primary key - foreign key to the linked Oracle tables it is linking to. The local table it is appending to is empty and is not indexed.

When I run my "Append_History_Query" from the Access interface by double clicking the query, it takes just over a minute.

However, when I run it using the "TestQueryRun" code below, it takes 4-5 minutes.

I’ve tried to append it to a local table that is not indexed and indexed, but the results are the same for both methods.

Why does it take 4 times longer within VBA than if I click on it within the interface?

 
Public Function TestQueryRun() As Boolean
 
   On Error GoTo ERRORHANDLER
   Dim StartDate As Date
   Dim End_Date As Date
   Dim queryName As String
 
    End_Date = #9/9/2014#
    StartDate = End_Date
    queryName = "Append_History_Query"
 
' I tried this method to see if it would improve performance
' It resulted 
 
'    DoCmd.SetParameter "Start_Date", StartDate
'    DoCmd.SetParameter "End_Date", End_Date
'
'    DoCmd.OpenQuery "Append_History_Query"
'
 
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
 
Set db = CurrentDb
Set qdf = db.QueryDefs(queryName)
qdf.Parameters("Start_Date") = StartDate
qdf.Parameters("End_Date") = End_Date
Debug.Print Now()
qdf.Execute
Debug.Print Now()
 
    TestQueryRun = True
 
EXITHANDLER:
    Exit Function
 
ERRORHANDLER:
 
    TestQueryRun = False
 
    GoTo EXITHANDLER
End Function
 
 
 

[twinnyfo]

drumahh,

Very strange. I usually find just the opposite, that my queries run more quickly from within VBA. I don't think you're doing anything wrong. You're just getting unexpected results. Perhaps some others may have insight as to the delays.

[BrianRiggs]

Since you have no control on the JET/ACE engine query plan and since Oracle must do all the calculation before sending data back bypass MS Access query planning by using a Pass-Thru OBDC query which will use native Oracle SQL.

The append query in MS Access will reference your new Pass-Thru query. The SQL of the APPEND query will be something like: INSERT INTO localtable SELECT * FROM passthruquery;

[zmbd]

drumahh:
We need to see the SQL behind the query.
We need to see the connection string or have an idea as to how you are connecting to the backend

Also... not fair to compare opening the immediate i/o
execute your query
and the push the call back to the immediate i/o

 Debug.Print Now()
qdf.Execute
Debug.Print Now()

You are comparing apples to oranges.
Access isn't a multithreaded processor. It starts one thing (immediate's gui - i/o), starts your query, then comes back to the gui... that takes a bit if time.

[drumahh]

I used Mr. Riggs solution by using a Pass-Through query, and then an Append query referencing the Pass-Through query. The time for the execution is now about 15 seconds. It was 5 minutes using VBA and just over a minute clicking the interface. So, I will be using a Pass-Through queries as much as possible, and bypassing the Access Jet layer when I can. It is truly the best solution

Regarding the SQL and ODBC, here is the neutered SQL and ODBC connection

PARAMETERS Start_Date DateTime, 
End_Date DateTime;
 
INSERT INTO local_table1 
(
field1, 
field2, 
field3, 
...
field60
)
 
SELECT Oracle_table_1.OracleField1 AS field1, 
"" AS field2, 
Oracle_table_1.OracleField3 AS field3, 
Oracle_table_2.OracleField4 AS field4, 
Oracle_table_1.OracleField5 AS field5, 
Oracle_table_1.OracleField6 AS field6, 
-1 AS field7, 
Oracle_table_1.OracleField8 AS field8, 
Oracle_table_2.OracleField9 AS field9, 
Oracle_table_1.OracleField10 AS field10, 
"" AS AS field11, 
...
 
Oracle_table_1.OracleField50
 
FROM Oracle_table_1 LEFT JOIN 
Oracle_table_2 ON Oracle_table_1.field7 = Oracle_table_2.field7
 
WHERE (((Oracle_table_1.Field_DT) Between [Start_Date] And [End_Date]));
 
--------------
Connection String:
 
ODBC;DRIVER={Oracle in OracleClientHome};SERVER=OracleServerName;DBQ=OracleServerName;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;MLD=0;ODA=F;
 
 

Regarding “Also... not fair to compare opening the immediate i/o execute your query and the push the call back to the immediate i/o”

I put the Debug.Print statements before and after the problem areas “after” the issue was discovered so I could get a repeatable quantitative time estimate for the execution of the query. I’m assuming the Debug.Print does not take more than a second or so to execute.

I am abandoning the “Why is Access behaving this way” for others to ponder. I now have an acceptable solution using Pass-Through queries.

Thanks!

[zmbd]

I've reset the answer.
Although the pass thru query maybe the workaround... it does not answer the question asked and therefor cannot be selected as best answer.

Wither or not one starts the query from a stored procedure or from VBA, we do not have any control over how the database engine does or does not optimize performance. Furthermore, there is no documentation that indcates that any such difference exsists between executing a stored query either via the GUI or from VBA; hence why I asked for the stored query's SQL in hopes that somehting there would help explain.

[drumahh]

>>why I asked for the stored query's SQL in hopes that somehting there would help explain.

So does the 1 join SQL statement or the ODBC connection explain anything?

[zmbd]

it's most likely with the qrydef object and the execute method - however, that's kind of captain obvious there (°®°)
my suspicion here is that the query is being flagged for recompile at runtime and something is causing that to be deoptimized.

Normally I build my SQL in the module and then pass that string to the database.execute method; thus, I don't pass parameters to the qrydef object.

Anyway the speed being slower is counter to what one would expect (QueryDef Object Acc2007)

I don't have an Oracle server to play with that has a table anything close to what your SQL indicates; thus, I am not able to re-create the issue.