Jeff Moden (7/17/2014)
Alan.B (2/7/2014)
Using the tests below: at a million rows PIVOT generates a parallel plan and is like 4X faster. At 5million they both produce parallel plans and PIVOT is still twice as fast. I did not expect that.I know that this post is several months old but there's a reason for your findings... you used pre-aggregation for the PIVOT but not for the CROSSTAB. The two tests aren't the same.
Hi Jeff, sorry for the later reply here - I have been off the grid dealing with health issues.
I fixed my code - please let me know if this is correct:
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
-- grouped by quarters only
SET NOCOUNT ON;
SET STATISTICS TIME ON;
PRINT 'crosstab, quarters only'+char(13);
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM
(SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY qtr) p;
PRINT 'pivot, quarters only'+char(13);
WITH p AS
(SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY office, qtr)
SELECT
[1] AS q1,
[2] AS q2,
[3] AS q3,
[4] AS q4
FROM p
PIVOT
(SUM(sales)
FOR qtr IN ([1],[2],[3],[4])) AS pivottbl;
SET STATISTICS TIME OFF;
GO
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
-- office & quarters
SET STATISTICS TIME ON;
PRINT 'crosstab office and quarters'+char(13);
SELECT
Office,
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM
(SELECT Office, qtr, SUM(sales) sales
FROM #p
GROUP BY office, qtr) p
GROUP BY office;
PRINT 'pivot office and quarters'+char(13);
WITH p AS
(SELECT office, qtr, SUM(sales) sales
FROM #p
GROUP BY office, qtr)
SELECT
office,
[1] AS q1,
[2] AS q2,
[3] AS q3,
[4] AS q4
FROM p
PIVOT
(SUM(sales)
FOR qtr IN ([1],[2],[3],[4])) AS pivottbl;
SET STATISTICS TIME OFF;
GO
-- Itzik Ben-Gan 2001