Determine fastest query in Query Analyzer

[laurenq uantrell]

I am trying to determine which of three stored procedure designs are
fastest in the Query Analyzer:

One query is a straight SELECT query with all desired rows and a dozen
(tblName.RowName = @param or @param = Null) filters in the WHERE
statement.

One query populates a #Temp table with the UniqueIDs from the results
of the SELECT query in the above example, then joins that #Temp table
to get the desired rows.

One query users EXEC sp_executesql @sql, @paramlist, @param
in which the @param has the dozen filters.

What I'm trying to determine is which is the fastest.

Each time I run the query in Query Analyzer it returns the same
recordset (duh!) but with much different Time Statistics.

Are the Time Statisticts THE HOLY QRAIL as far as determining which is
fastest, and what so I want to look at, the Vale or the Average? I
notice there are different numbers of bytse sen and bytes received for
each of the three queries.

Any illumination on this is appreciated.
lq

[John Bell]

Hi

You are looking at the client statistic! The topic "Query Window Statistics
Pane" in books online explains their values.

Time is a good indicator of performance, for instance if there are more
network round trips this should be noticed in the time taken. You may also
want to consider the number of reads/writes which may give some indication
of how well it will perform when the system in under a load. These can be
viewed using SQL profiler.

Expect the first time you run a query to take longer than subsequent times,
if your query is cached subsequent executions may be significantly faster.
Use DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS to clear the procedure
cache and buffer pool.

John

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

I am trying to determine which of three stored procedure designs are
fastest in the Query Analyzer:

One query is a straight SELECT query with all desired rows and a dozen
(tblName.RowName = @param or @param = Null) filters in the WHERE
statement.

One query populates a #Temp table with the UniqueIDs from the results
of the SELECT query in the above example, then joins that #Temp table
to get the desired rows.

One query users EXEC sp_executesql @sql, @paramlist, @param
in which the @param has the dozen filters.

What I'm trying to determine is which is the fastest.

Each time I run the query in Query Analyzer it returns the same
recordset (duh!) but with much different Time Statistics.

Are the Time Statisticts THE HOLY QRAIL as far as determining which is
fastest, and what so I want to look at, the Vale or the Average? I
notice there are different numbers of bytse sen and bytes received for
each of the three queries.

Any illumination on this is appreciated.
lq

[undercups]

Have a look at showplan whick give you an idea what the database is
doing to resolve your queries. Determining the fastest method can be
difficult especially with changing volumes of data, add or remove an
index will effect the results (faster or slower) so experiment a bit.

Sorry I cannot be more help
duncan

[Erland Sommarskog]

laurenq uantrell (la*************@hotmail.com) writes:

I am trying to determine which of three stored procedure designs are
fastest in the Query Analyzer:

One query is a straight SELECT query with all desired rows and a dozen
(tblName.RowName = @param or @param = Null) filters in the WHERE
statement.
@param = Null?

Remember that NULL is never equal to anything, not an even another NULL.
NULL is an unknown value, and two nulls may be two different values.
Use "@param IS NULL" instead.
Are the Time Statisticts THE HOLY QRAIL as far as determining which is
fastest, and what so I want to look at, the Vale or the Average? I
notice there are different numbers of bytse sen and bytes received for
each of the three queries.

When I need to benchmark queries I usually do:

DECLARE @d datetime, @tookms int
SELECT @d = getdate()
-- Run query
SELECT @tookms = datediff(ms, @d, getdate())
PRINT 'It took ' + ltrim(str(@tookms)) + ' ms.'

As John mentioned it is important to have the cache in mind. You can
do DBCC DROPCLEANBUFFERS to flush the cache, but don't this on a
production box! Often I'm lazy and run the queries several times, and
forget the first run, since that may include time reading from disk.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[laurenq uantrell]

Erland,
Thanks. Yes, I use "Is Null". I wrote the question on the fly. I will
insert your @tookms into my sprocs to see how they perform. That's a
great hint.
LQ