stored procedure query

[smk23]

I have a stored procedure that I use in a pass-through query and have many
such queries working fine. The select statement in the stored procedure is
complex but populates in one second when run from SQL query analyzer.

From the MS Access (2003) pass-through, I keep getting a time-out error. If
the query runs in 1 second from SQL, shouldn't it run in about the same from
Access? It's not a typo or syntax error. I've posted the exact syntax in SQL
and it runs fine. I've checked by ODBC connection and that is fine. What else
should I look at?

Sam

 

[Rick Brandt]

I have a stored procedure that I use in a pass-through query and have
many such queries working fine. The select statement in the stored
procedure is complex but populates in one second when run from SQL
query analyzer.

From the MS Access (2003) pass-through, I keep getting a time-out
error. If the query runs in 1 second from SQL, shouldn't it run in
about the same from Access? It's not a typo or syntax error. I've
posted the exact syntax in SQL and it runs fine. I've checked by ODBC
connection and that is fine. What else should I look at?

Sam

In query analyzer do you see results in one second or does the status bar
indicate that the query completed in one second? You can see results
immediately and still not have the query "finish" for quite a while. Access
will not show anything until the entire query is finished.

 

[smk23]

I see the results and the status bar reads 1 second.

 

[Rick Brandt]

I see the results and the status bar reads 1 second.

That's a puzzle then. I have never noticed any perceptable difference in
the speed of running a query in QA compared to using an Access Passthrough.

Are you using the latest SS driver?

 

[smk23]

Interesting observation that I had not seen before:
I got the query running.
But the first time it ran, it took 20+ seconds. My timeout was 30 seconds
and apparently if I had reset the timeout longer, it would have run. I
tweaked the stored procedure, cut it down slightly, enough for the query to
get under 30 seconds. Once it had run that first time, it now runs in under 1
second, same as the stored procedure.

I had run the query several times from QA, so that the stored procedure
would compile, but inspite of this it seemed like it had to compile
separately on the Access side.

Sam