Posted by Erland Sommarskog on 10/01/19 11:17

jsfromynr (jatinder.singh@clovertechnologies.com) writes:
> I am bit confused by the output produced by the Query Analyzer and
> finding it bit difficult to decide which one of the following query is
> faster.
>
> In query one I am using Correlated subquery Approach and it consumes
> 78% of batch time when run with 2nd query but time of mere 20
> micrseconds
>
>
> In query two I am using functions (these functions berely takes
> PolicyNumber and Endrosment No to give output and does the same query )
> Approach and it consumes 22% of batch time when run with 1st query but
> time of 400 micrseconds

The difference in estimate may be because the function is not considered.

Anyway, the one way to benchmark queries is this:

DECLARE @d datetime, @tookms int
SELECT @d = getdate()
-- run query here
SELECT @tookms = datediff(ms, @d, getdate())
PRINT 'This query took ' + ltrim(str(@tookms) + ' ms to run.'

You need to consider the effect of the cache. If the two queries operates
on the same data, the easiest may be to run the queries several times
and discard the first result. You can also run DBCC DROPCLEANBUFFERS to
clean the cache, but that affects the entire server.

Also, beware that datetime has a resolution of 3.33 ms. For the
measurement method above, I have never seen any value between 0 and
13 ms. I consider values below 50 ms to be too inaccurate to be
taken as a significant. 400 ms is certainly significant.

Note: above you talk "microseconds". I assume this is a typo for
"milliseconds".

--
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]