February 21, 2006 at 10:27 am
What are some possible causes for a query to take 30 seconds to run when it runs from the Server Machine, and 5 minutes to run, when it runs from a client machine, if you have eliminated networking issues (Name Resolution, etc)?
February 21, 2006 at 11:03 am
That is a huge difference. Could it be the amount of data returned?
Query analyzer displays the results faster in grid than in text mode.
What is the connection technology to sql server (ODBC,ADO,ADO.NET?)
February 21, 2006 at 11:21 am
The amount of rows returned is the same. Profiler indicates that the same amount of time is spent on the query portion in both cases. The networking technology is ADO.NET, I believe.
February 21, 2006 at 12:29 pm
Is this only the first time or every time the query is run?
February 21, 2006 at 12:44 pm
This happens every time the query is run.
February 21, 2006 at 2:31 pm
how much data is returned to the client??
Just to test try top 1 on the data that is being returned on the select and check whether u still see the huge difference in time..
Or
Just try to run the qeury from QA from the client machine..
It got to be networkio if u r seeing so much difference..
And if you meant executing via a application then i would try identify the bottlenecks w.r.t app.
-Krishnan
February 22, 2006 at 2:49 am
Problem could be your 'connection' type/ recordset type as defined within you application. Can you post the relevent 'calling' code, SQL/SP and an execution plan?
February 22, 2006 at 12:48 pm
Are the objects being referenced fully qualified (owner.objectname). Is the same login executing the command from both the server and client. What net library is each using to connect. Have you run a profiler to capture the statements being passed.
Tom
February 22, 2006 at 2:19 pm
Another possible thing would be if the "query" uses a cursor or other looping construct to build a recordset in a staging table, you could get much longer execution times remotely if "nocount" isn't used inside the loop.
just a thought...
February 23, 2006 at 4:31 am
try profiler and compare rpc completion and sp completion - a difference indicates slowness with the client connection.
you might also want to check for set options in the connection string.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 23, 2006 at 7:05 am
Have you profiled the application? You said it's ADO.NET, are you certain that it's the execution of the query that's the problem or is it something else? For example, is the application populating a dataset or otherwise doing some processing on the returned data that you're not aware of? Are you using the OleDb or ODBC library instead of SqlClient?
If this is a .NET app, then posting the relevant code would be a really big help. Could be any number of things but without looking at the code it's hard to do anything but guess.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply