Forum Discussion
MS Access passthrough query performs very slow when executing a stored procedure
I have a passthrough query in my Access frontend that I use to import data from a sql server db. I work with MS 365 MSO Version 2302 32 Bit. Before executing the passthrough query I pass the connectionstring and the sql to the querydef object and then I pass the output to a DAO recordset:
Set qdf = CurrentDb.QueryDefs("myPTQuery")
qdf.Connect = myconnectionstring
qdf.SQL = "EXEC MyStoredProc " & param1 & ", " param2
Set rstDAO = qdf.OpenRecordset
When I execute the stored procedure in SSMS using the same parameters it takes 2 seconds. In MS Access I need to set the ODBCTimeout parameter to 4 minutes to avoid a timeout error.
This problem occures rarely, i.e. only under certain parameters. But I cannot figure out any significant difference between the cases the passthrough query performs well and the ones it doesn't.
It seems like the timeout occures regardless on which parameters are passed to the stored proc. It worked before. Could it be caused by some office update?
Any ideas on how to improve the performance of the passthrough query?
- Show us the entire procedure, not just this snippet, please.
- The error occurs on the statement Set rstDAO = qdf.OpenRecordset. But the timeout occurs also when executing the PT query manually, so I don't think the VBA Code is relevant. The exact same EXEC statement is executed without problems in SSMS with the same user.
