Problem with SQL Query Analyzer

Hi,
I have the following problem: if I call a stored procedure from a program (via COM+/OLE DB) I measure the response time and I get something around 400 ms (including reading the data from the result set, a couple of MoveNext operations).
Now if I call the very same stored procedure within the SQL Query Analyzer, it takes almost 8 seconds until I can see the results! Does anybody know why?
Is the Query Analyzer so badly implemented when it reads the results and displays them?
Even if I eliminate the results (by adding select top 0 in my final query in the stored procedure) it still takes about 7 seconds within the Query Analyzer.
My bigger problem is that I use the query analyzer to fine-tune my application and it seems I can't rely on the results, because the same queries run differently once they are called from the application.
It's weird because I also get these confusing response times displayed in the Profiler! At the beginning I thought that the Query Analyzer adds some overhead, but why do I get different times in the Profiler?
Any hints/tips? Did anyone experience that before?
Thanks in advance,
Florin Micle
Execution plans can differ for several reasons. Different SET option is one potential reason. The first thing
to do is to compare the execution plans and see if they are the same or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"fmicle" <fmicle@.hotmail.com> wrote in message news:824E53F3-AC7C-4C1C-AFD5-06D4609F8714@.microsoft.com...
> Hi,
> I have the following problem: if I call a stored procedure from a program (via COM+/OLE DB) I measure the
response time and I get something around 400 ms (including reading the data from the result set, a couple of
MoveNext operations).
> Now if I call the very same stored procedure within the SQL Query Analyzer, it takes almost 8 seconds until
I can see the results! Does anybody know why?
> Is the Query Analyzer so badly implemented when it reads the results and displays them?
> Even if I eliminate the results (by adding select top 0 in my final query in the stored procedure) it still
takes about 7 seconds within the Query Analyzer.
> My bigger problem is that I use the query analyzer to fine-tune my application and it seems I can't rely on
the results, because the same queries run differently once they are called from the application.
> It's weird because I also get these confusing response times displayed in the Profiler! At the beginning I
thought that the Query Analyzer adds some overhead, but why do I get different times in the Profiler?
> Any hints/tips? Did anyone experience that before?
> Thanks in advance,
> Florin Micle
|||Perhaps your data happened to be cached in some instances. Try clearing the
cache using DBCC DROPCLEANBUFFERS before testing the queries using.
As Tibor mentioned, different execution plans may be another cause, which
you can also capture using Profiler.
Regards
Ray Mond
"fmicle" <fmicle@.hotmail.com> wrote in message
news:824E53F3-AC7C-4C1C-AFD5-06D4609F8714@.microsoft.com...
> Hi,
> I have the following problem: if I call a stored procedure from a program
(via COM+/OLE DB) I measure the response time and I get something around 400
ms (including reading the data from the result set, a couple of MoveNext
operations).
> Now if I call the very same stored procedure within the SQL Query
Analyzer, it takes almost 8 seconds until I can see the results! Does
anybody know why?
> Is the Query Analyzer so badly implemented when it reads the results and
displays them?
> Even if I eliminate the results (by adding select top 0 in my final query
in the stored procedure) it still takes about 7 seconds within the Query
Analyzer.
> My bigger problem is that I use the query analyzer to fine-tune my
application and it seems I can't rely on the results, because the same
queries run differently once they are called from the application.
> It's weird because I also get these confusing response times displayed in
the Profiler! At the beginning I thought that the Query Analyzer adds some
overhead, but why do I get different times in the Profiler?
> Any hints/tips? Did anyone experience that before?
> Thanks in advance,
> Florin Micle
|||Hi, when you look at profiler do you to capture exactly what it being run when the program calls the sp? I ask because unless the inputs are exactly the same you could get differing results. I've seen this with datetime inputs where query plans have been
different depending on the format of the date input. As Tibor says, did the query plan show anything different? I assume they must have done? You've tried the TOP 0 which should eliminate the displaying of the results and as Ray says you can drop the data
from cache (and run CHECKPOINT) to give the two approaches a level playing field. I feel the answer will be in your execution plan and I'd expect either as Tibor says, your SET options will be the root, or that it's perhaps something to do with one or mo
re of your sp inputs.
Alicia
Http://www.sqlporn.co.uk
|||Well, how am I suppose to know how exactly the query is executed and which query plan is taken?
I can see in the Profiler, that the exact same query is launched when I call it from my application and when I call it from the Query Analyzer. The query plan I can only see in the Query Analyzer, if I run my query in it, so I'll never know what plan is a
ctually taken when my query runs from the application.
That's exactly my problem, I can't tune my application properly, since the queries work differently in the Query Analyzer and from my app.
By the way, to answer your questions, the plan is always the same for my query.
I also think there are some bugs in the Profiler.
My stored procedure, let's name it SP1 calls 2 other SP's, which are very fast, let's call them SP2 and SP3.
So SP1 looks something like:
...
EXEC SP2
EXEC SP3
Do some complex queries
...
In the profiler, every time I call SP1, I get 3 SP Completed events, like this (two consecutive calls, you can see the effects of the cache, which is ok):
EventClass TextData Duration
SP:Completedexec SP1 0
SP:Completedexec SP1 0
SP:Completedexec SP1 453
SP:Completedexec SP1 0
SP:Completedexec SP1 0
SP:Completedexec SP1 313
Now, I call the same SP two times from the Query Analyzer, this is what I get in the profiler:
EventClass TextData Duration
SP:Completedexec SP2 0
SP:Completedexec SP3 0
SP:Completedexec SP1 1926
SP:Completedexec SP2 0
SP:Completedexec SP3 0
SP:Completedexec SP1 2005
The profiling mechanisms seem to be different, if the SP1 is called from the Query Analyzer it looks OK, but from my app, there seems to be a bug somwhere in the event handling in the Profiler.
Any ideas?
|||I can't believe this, if I delete the proccache (dbcc freeproccache) I get almost the same response times within QA like from my application!
About 600 ms, which is much better than 2 seconds!
What can make QA generate such a bad query plan?
Another thing: my application (C++) runs as a COM object in COM+. I wrote a small C++ program which does the exact same thing (calls the same stored procedure) and this is slower than when it runs in COM+. I use ATL OLE DB to connect to the DB in both cas
es.
I can only imagine, that there must be some magic in COM+, which sets some parameters differently, thus making the SQL Server run faster/better when called from COM+.
Confusing...
|||> What can make QA generate such a bad query plan?
The cached plan may have been generated for a query that uses parameter
values that do not fall into the same distribution pattern that the later
query has. However, this is unlikely in your case since you mentioned that
they are both using the same query.
I suggest checking the execution plan from both sides. Use Profiler to
capture the execution plan, using the Show Plan Statistics event under the
Performance heading.
Regards
Ray Mond
"fmicle" <fmicle@.hotmail.com> wrote in message
news:D42860F2-6C23-48C0-AA90-9301F2697191@.microsoft.com...
> I can't believe this, if I delete the proccache (dbcc freeproccache) I get
almost the same response times within QA like from my application!
> About 600 ms, which is much better than 2 seconds!
> What can make QA generate such a bad query plan?
> Another thing: my application (C++) runs as a COM object in COM+. I wrote
a small C++ program which does the exact same thing (calls the same stored
procedure) and this is slower than when it runs in COM+. I use ATL OLE DB to
connect to the DB in both cases.
> I can only imagine, that there must be some magic in COM+, which sets some
parameters differently, thus making the SQL Server run faster/better when
called from COM+.
> Confusing...
|||No, you're right, it makes sense. When I call the SP from COM+, I use placeholders and Accessors to bind my parameters.
But I get the exact same query text in the profiler.
On the other hand, if I do the same thing outside COM+, even using the parameters, I still get the bad plan (WITH PREFETCH).
I can only reach almost the same speed outside COM+ if I delete the PROCCACHE.