asked on

Why is a pass-through query 6X slower than the SQL Query Designer on the server itself?

I have an access 2000 client that has been running a pass-through query that executes a SP for some time now without any issues. All of the sudden the pass-through runs very slow! When the SP that it executes is run on the server itself it returns records in about 00:01:48 but when run via the pass-through on the client it takes about 5X longer! I have rebooted the server and client with no avail. I was thinking it might have something to do with the driver but I'm using the most recent available that came with mdac 2.8. The pass-through is created via VB code on the client at run-time and the connection string is basic "ODBC;Description=The SQL Server;DRIVER=SQL Server;SERVER=XXX.XXX.X.X;Database=Somedb;Trusted_Connection=Yes", return records is set to true and the timeout is set to 300. Not sure what has changed on either end that would cause this sudden slow down in response time. The Client and Server are within the same network housed under the same roof. There were no changes to the network that would cause this sudden decrease in response time (according to the network guys). Any ideas of how to troubleshoot/resolve?

ASKER CERTIFIED SOLUTION

Brendt Hess

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

dakarpiak

ASKER

Well... when I run the SP on the Query Designer on the client it runs very quickly. It's only when I run it via the pass-through query of the access client that it slows down considerably. I ran a turning trace and it looks like 3 of the 17 SP that the parent SP runs are taking a very long time to execute. Not sure why this would happen since they are the exact same SP, DBs, and tables - no difference. Only difference is in where there are executed. Query Designer vs. Access Pass-Through Qry. The records are returned to the client and nothing is done to them. No client fllters or sorting applied. Do you think it may have something to do with the fact that access is requesting the data over the query designer? I don't know what SQL would respond differently to who/what source the request is coming from. The CPU speed and memory are not an issue because the client does no processing of records. The server has 4 dual core processors and 16 GB of RAM. I don't think hardware is an issue. And if it were the problem, the client-side Query Designer would experience similar delays but it does not. HELP!

SOLUTION

Mark Wills

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer