May 18, 2012 at 1:14 pm
Comments posted to this topic are about the item Performance of the Aggregate and Ranking anaytical function against al
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
June 8, 2012 at 1:55 pm
Your assertions that the OVER clause performs worse than alternative methods is incorrect and misleading. You used an example where it's going to be worse. There are MANY cases where the OVER clause blows the doors off the performance of alternative methods. It wasn't designed to do the simple aggregations that you are showing where you compute an aggregate over the entire table or you have a simple GROUP BY...HAVING.
These produce the same answer and writing it with an OVER clause is horribly inefficient.
--65 logical reads and 16 ms
SELECT SUM(OrderTotal)
FROM Orders.OrderHeader;
--16440 logical reads and 312 ms
SELECT DISTINCT SUM(OrderTotal) OVER ()
FROM Orders.OrderHeader;
But, try computing something like a running total. (One of the problems that the OVER clause was designed to handle.)
--2097 logical reads and 171 ms
WITH DailyTotal_CTE (OrderDate, DailyTotal)
AS
(SELECT OrderDate, SUM(OrderTotal) DailyTotal
FROM Orders.OrderHeader
GROUP BY OrderDate)
SELECT a.OrderDate, a.DailyTotal, SUM(b.DailyTotal) RunningTotal
FROM DailyTotal_CTE a INNER JOIN DailyTotal_CTE b
ON a.OrderDate >= b.OrderDate
GROUP BY a.OrderDate, a.DailyTotal
ORDER BY a.OrderDate;
--2430 logical reads and 47ms
WITH DailyTotal_CTE (OrderDate, DailyTotal)
AS
(SELECT OrderDate, SUM(OrderTotal) DailyTotal
FROM Orders.OrderHeader
GROUP BY OrderDate)
SELECT OrderDate, DailyTotal, SUM(DailyTotal) OVER(ORDER BY OrderDate) RunningTotal
FROM DailyTotal_CTE
ORDER BY OrderDate;
I'd challenge you to write a query that produces a 3 month moving average without window functions and get it to perform even half as good as a window function will. The assertion in this article is the classic case of "everything is a nail". You're selecting examples that are going to prove your assertion that the OVER clause doesn't perform well. That doesn't mean you can make a blanket assertion like this.
Michael Hotek
June 9, 2012 at 12:37 pm
One way to make a difficult problem more difficult is to make it difficult to see. Remove the formatting and it becomes more difficult for a programmer to decipher. Proper formatting helps tremendously in determining how a query is constructed. It appears from your post that you removed indenting and case to help make your case.
May 10, 2016 at 9:28 am
Thanks for the script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply