need to know couple of things SET STATISTICS IO

Question

need to know couple of things SET STATISTICS IO

Smash125

Hall of Fame

Points: 3562
More actions
August 1, 2012 at 7:10 am

#265064

I have gone through technical documents.I am very much new to performance related queries
I wrote couple of queries one using RANKING FUNCTION Row_Number() and another using SUB QUERY
Please find the query execution results
SELECT DISTINCT
ContactID,MAX(TotalDue) AS MaxTotalDueForTheContact ,ROW_NUMBER()OVER(ORDER BY ContactID) AS A
FROM Sales.SalesOrderHeader
GROUP BY ContactID
Execution results:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 8 ms.
(19119 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 346 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Using Subquery
SELECT ContactID,TotalDue FROM Sales.SalesOrderHeader A WHERE TotalDue=
(SELECT MAX(TotalDue) FROM Sales.SalesOrderHeader B WHERE A.ContactID=B.ContactID)
ORDER BY 1 ASC
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(19141 row(s) affected)
Table 'SalesOrderHeader'. Scan count 1, logical reads 706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 368 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
First query performs better than second query. Can anybody give me insight on the execution results plzz

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Execution times look pretty comparable to me. Did you run the test multiple times and average the results?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Also, those two queries don't appear to give identical results. Look at the affected rows and even the number of columns returned from each. What happens in each query if one ContactID has ties for the max TotalDue?

You shouldn't compare the performance until you have both queries doing exactly the same thing.