Hi,

I'm trying to improve on a query in one of my scripts and noticed that
execution time in query analyzer is several seconds where as in sql
profiler the start and end time of the statements (including
declaration of variables) is within the same millisecond. What am I to
make out of this (apart from that my query is slow)?

regards,

Gerard

correction the last sentence should read:

What am I to make out of this (apart from that my query is slow in QA)?

If the query returns a large number of rows, QA must render these in the
Results Pane, which can make the query appear slower than it is.  To get a
better approximation of the query execution time, DECLARE a bunch of
variables and issue the query like this:

DECLARE @X INT, @Y INT, @Z INT, @Before DATETIME
SET @Before = GETDATE()
SELECT @X = X, @Y = Y, @Z = Z FROM ...
SELECT 'Before', @Before, 'After', GETDATE(), 'Duration', GETDATE() -
@Before

After execution the variables will contain the last row from the result set.
Be sure to include all of the columns from the original query.

Note that the actual production execution time will vary, depending on the
load on the server.

Show quoteHide quote

Thanks for the tip Brian, I'll try this out. The query however only
returns 8 rows ( and 8 columns).

regards,

Gerard

That's exactly what Profiler is for - to show the performance of the server
not the client.

Test it on a table with columns of datatype text/ntext first with the
default setting of how many characters are returned in a character column
(256) and then the maximum (8192). The Profiler will show equal time while QA
will struggle in the latter case.


ML

---
http://milambda.blogspot.com/

Gerard (g.doesw***@gmail.com) writes:
Other posters suggested that latency in QA is not captured in Profiler.
This is not my experience.

What events are are you tracing?

Add an extra batch before the real thing, and see where its StartTime
is. My guess is that the compilation of the query takes a long time.
This can also be investigated with running the query with SET STATISTICS
TIME ON; this adds output that separates compile time and execution time.

Check the continuity of dates
identify 'next' pk value
Help with relational set division, please! Good challange!
OPTIMIZER LOCK HINTS
Select most recent date time