August 26, 2014 at 5:07 am
Steve Jones - SSC Editor (8/25/2014)
Jeff Moden does a good job of showing performance comparisons
Great article!
I could feel the performance drop when using PIVOT statements on 1M+ records pre-aggregated or not as well with a CTE or not. When you get up there to 10M to 100M+ records, it's even worse.
August 26, 2014 at 8:38 am
Hardy21 (8/26/2014)
Luis Cazares (8/25/2014)
jshahan (8/25/2014)
sestell1 (8/25/2014)
Does anyone actually use the PIVOT operator?I find it so limited that I almost always roll my own using GROUP BY and CASE.
I like PIVOT because you can use it to do a dynamic number of columns on the back end.
You can do that with cross tabs as well. 😀
Any good example with Cross tab query vs PIVOT?
Other than Jeff's article that Steve shared?
Here's a test I did some time ago in this thread:http://qa.sqlservercentral.com/Forums/Topic1537999-392-1.aspx
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
SELECT n
,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;
SET STATISTICS TIME ON;
PRINT 'Normal Cross-tab'
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
,COUNT(CASE qtr WHEN 1 THEN sales END) AS Count_q1
,COUNT(CASE qtr WHEN 2 THEN sales END) AS Count_q2
,COUNT(CASE qtr WHEN 3 THEN sales END) AS Count_q3
,COUNT(CASE qtr WHEN 4 THEN sales END) AS Count_q4
FROM #p;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
SET STATISTICS TIME ON;
PRINT 'Normal Pivot'
SELECT pivottbl.[1] AS q1
,pivottbl.[2] AS q2
,pivottbl.[3] AS q3
,pivottbl.[4] AS q4
,pivottbl2.[1] AS Count_q1
,pivottbl2.[2] AS Count_q2
,pivottbl2.[3] AS Count_q3
,pivottbl2.[4] AS Count_q4
FROM (
SELECT qtr
,sales
FROM #p
) AS p
PIVOT(SUM(p.sales) FOR p.qtr IN ([1],[2],[3],[4])) AS pivottbl
JOIN (
SELECT qtr
,sales
FROM #p
) AS p2
PIVOT(COUNT(p2.sales) FOR p2.qtr IN ([1],[2],[3],[4])) AS pivottbl2 ON 1 = 1;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
SET STATISTICS TIME ON;
PRINT 'Pre-aggregated Cross-tab'
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
,SUM(CASE qtr WHEN 1 THEN Cnt END) AS Count_q1
,SUM(CASE qtr WHEN 2 THEN Cnt END) AS Count_q2
,SUM(CASE qtr WHEN 3 THEN Cnt END) AS Count_q3
,SUM(CASE qtr WHEN 4 THEN Cnt END) AS Count_q4
FROM (
SELECT qtr
,SUM(sales) sales
,COUNT(*) Cnt
FROM #p
GROUP BY qtr
) p;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
SET STATISTICS TIME ON;
PRINT 'Pre-aggregated Pivot'
SELECT pivottbl.[1] AS q1
,pivottbl.[2] AS q2
,pivottbl.[3] AS q3
,pivottbl.[4] AS q4
,pivottbl2.[1] AS Count_q1
,pivottbl2.[2] AS Count_q2
,pivottbl2.[3] AS Count_q3
,pivottbl2.[4] AS Count_q4
FROM (
SELECT qtr
,SUM(sales) sales
FROM #p
GROUP BY qtr
) AS p
PIVOT(SUM(p.sales) FOR p.qtr IN ([1],[2],[3],[4])) AS pivottbl
JOIN (
SELECT qtr
,COUNT(*) Cnt
FROM #p
GROUP BY qtr
) AS p2
PIVOT(SUM(Cnt) FOR p2.qtr IN ([1],[2],[3],[4])) AS pivottbl2 ON 1 = 1;
SET STATISTICS TIME OFF;
I can also generate the code for a dynamic crosstab with a single statement.:cool:
August 27, 2014 at 3:11 am
Hardy21 (8/26/2014)
Luis Cazares (8/25/2014)
jshahan (8/25/2014)
sestell1 (8/25/2014)
Does anyone actually use the PIVOT operator?I find it so limited that I almost always roll my own using GROUP BY and CASE.
I like PIVOT because you can use it to do a dynamic number of columns on the back end.
You can do that with cross tabs as well. 😀
Any good example with Cross tab query vs PIVOT?
This is pretty comprehensive:
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply