It seems that I get very similar results with pivot and cross tabs. The parallelism is created with the pre-aggregation because the normal pivot and cross tabs won't use it. Both pre-aggregated versions return the exact same plan and similar results every time I ran the test. Maybe I'm missing something. I'll try to come back if I get the time to add an extra column to pivot.
If you have any comments about the test, please let me know.
Code used for the test:
IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;
CREATE TABLE #p(id int primary key, qtr tinyint, sales int);
DECLARE @rows int = 1000000;
WITH iTally(n) AS
(SELECT TOP(@rows) ROW_NUMBER() OVER (ORDER BY (SELECT ($)))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT #p
SELECTn,
ceiling(4*rand(convert(varbinary, newid()))),
ceiling(80*rand(convert(varbinary, newid())))+20
FROM iTally;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
SET NOCOUNT ON;
PRINT 'Normal Cross-tab'
SET STATISTICS TIME ON;
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 #p;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Normal Pivot'
SET STATISTICS TIME ON;
--where I was stuck
SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4
FROM
(SELECT qtr, sales
FROM #p) AS p
PIVOT(
SUM(sales)
FOR qtr IN ([1],[2],[3],[4])
) AS pivottbl;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Pre-aggregated Cross-tab'
SET STATISTICS TIME ON;
--where I was stuck
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;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Pre-aggregated Pivot'
SET STATISTICS TIME ON;
--where I was stuck
SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4
FROM
(SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY qtr) AS p
PIVOT(
SUM(sales)
FOR qtr IN ([1],[2],[3],[4])
) AS pivottbl;
SET STATISTICS TIME OFF;
Results
Normal Cross-tab
Warning: Null value is eliminated by an aggregate or other SET operation.
SQL Server Execution Times:
CPU time = 1350 ms, elapsed time = 1352 ms.
Normal Pivot
SQL Server Execution Times:
CPU time = 1340 ms, elapsed time = 1345 ms.
Pre-aggregated Cross-tab
Warning: Null value is eliminated by an aggregate or other SET operation.
SQL Server Execution Times:
CPU time = 1060 ms, elapsed time = 292 ms.
Pre-aggregated Pivot
SQL Server Execution Times:
CPU time = 1070 ms, elapsed time = 320 ms.
PS. Take care, I hope it doesn't get too terrible with all that snow. I sure miss it but I don't need as much as you might be getting.