Query 2.
SELECT * FROM (
SELECT
o.OrderDate,
o.AccountNumber,
o.TotalDue,
o.SalesPersonID,
p.FirstName,
p.LastName,
ROW_NUMBER() OVER (PARTITION BY YEAR(o.OrderDate), MONTH(o.OrderDate) ORDER BY o.TotalDue desc) MonthPosition
FROM Sales.SalesOrderHeader AS o
INNER JOIN Person.Person AS p
ON o.SalesPersonID = p.BusinessEntityID
) sub
WHERE MonthPosition = 1
ORDER BY sub.TotalDue desc
Performance characteristics:
Yours
Table 'Person'. Scan count 9, logical reads 310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 18, logical reads 1504, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 36, logical reads 113790, 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 = 2590 ms, elapsed time = 781 ms.
Mine
Table 'Person'. Scan count 9, logical reads 310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 9, logical reads 752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 17 ms.
p.s. I added the order by (to both queries) to make comparison easier as the order of rows comes out differently without the order by due to the different ways the query processor executes them
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