A discussion came up at my job on the most efficient way to return subtotals and totals in one row, something like:
Total A | Total B | Total C | Grand Total |
1 | 2 | 3 | 6 |
I had assumed that PIVOT would be the most straightforward method, but others preferred using CASE statements. So which method is more efficient?
I posted a script on GitHub to build a table with 6 million records to run both kinds of queries against. The results of each will be:
Red | Blue | White | Total |
3000000 | 2000000 | 1000000 | 6000000 |
The PIVOT query:
select [Red], [Blue], [White], ([Red] + [Blue] + [White]) as Total from ( select RecordColor from dbo.TestPivotCase ) as a pivot ( count(RecordColor) for RecordColor in ([Red], [White], [Blue]) )as p;
The CASE query:
select sum(case when RecordColor = 'Red' then 1 else 0 end) as [Red], sum(case when RecordColor = 'Blue' then 1 else 0 end) as [Blue], sum(case when RecordColor = 'White' then 1 else 0 end) as [White], count(*) as [Total] from dbo.TestPivotCase;
The plans were almost exactly similar in cost, with the PIVOT with a slightly less cost (49.7 % vs. 50.3%).
The execution plans were very similar.
Case Plan:
So both methods have a similar execution plan and run time, with a slight edge to the PIVOT method.
I also found similar execution time. Tested with 2 millions rows.