Menu
Although a couple of questions were already posted in SO about the difference between Over Partition By and Group By, I did not find a definitive conclusion about which performs better.
I set up a simple scenario at SqlFiddle, where Over (Partition By) seems to boast a better execution plan (I am not much familiar with them, however).
Is the amount of data in the tables supposed to change this? Does Over (Partition By) then ultimately performs better?
406
Group By with not be always be faster than Partition by... its more important to understand the semantics of how the work. - Group BY with hashout the keys and then apply distinct on it.. so If you have nested queries or Views then its a never ending story.
However, it's still slower than the GROUP BY. The IO for the PARTITION BY is now much less than for the GROUP BY, but the CPU for the PARTITION BY is still much higher. Even when there is lots of memory, PARTITION BY – and many analytical functions – are very CPU intensive.
A GROUP BY normally reduces the number of rows returned by rolling them up and calculating averages or sums for each row. PARTITION BY does not affect the number of rows returned, but it changes how a window function's result is calculated.
The execution plan for the windowed function is clearly inferior to the execution plan for the group by (the whole group by execution plan is included in the top right of the windowed function execution plan). SQL in general is better optimized for group by than using an over clause, however each has their uses.
The execution plan for the windowed function is clearly inferior to the execution plan for the group by (the whole group by execution plan is included in the top right of the windowed function execution plan). SQL in general is better optimized for group by than using an over clause, however each has their uses. For example, if you wanted to output a row for each entry with the summation for the group, then windowing would probably make more sense (e.g. A.id|B.b1|sum B.b1 over A.id). Since you do not, it really doesn't. You're basically using a group by, then taking a distinct again, instead of just using the distinct that the group by implies.
138
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
