troubleshooting Question

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

Avatar of dakarpiak
dakarpiak asked on
Microsoft SQL Server 2005SQL
3 Comments2 Solutions208 ViewsLast Modified:
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?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros