Posted by Erland Sommarskog on 09/17/05 21:01

laurenq uantrell (laurenquantrell@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, esquel@sommarskog.se

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

[Back to original message]