Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

T-SQL Pivot Question Expand / Collapse
Author
Message
Posted Tuesday, February 11, 2014 5:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 3,374, Visits: 7,300
Have you tested again?


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1540501
Posted Thursday, July 17, 2014 7:55 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1593888
Posted Thursday, July 24, 2014 2:06 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 4:49 AM
Points: 556, Visits: 2,581
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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1596014
Posted Thursday, July 24, 2014 2:48 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 4:49 AM
Points: 556, Visits: 2,581
Luis Cazares (2/11/2014)
Have you tested again?


Luis - I completely flaked out and missed this message. I am truly sorry and feel like a jerk. I fixed the my code and re-tested it. The server I originally tested this on does not exist. On my laptop (4 cpu, 16gb ram, SQL 2014) using the code that I posted above I get the following results against 10M rows:

crosstab, quarters only

SQL Server Execution Times:
CPU time = 3796 ms, elapsed time = 1106 ms.

pivot, quarters only

SQL Server Execution Times:
CPU time = 6344 ms, elapsed time = 1703 ms.

crosstab office and quarters


SQL Server Execution Times:
CPU time = 6297 ms, elapsed time = 1684 ms.

pivot office and quarters

SQL Server Execution Times:
CPU time = 6374 ms, elapsed time = 1751 ms.


I did 10M because the results are pretty close.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1596027
Posted Thursday, July 24, 2014 3:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 3,374, Visits: 7,300
Alan.B (7/24/2014)
Luis Cazares (2/11/2014)
Have you tested again?


Luis - I completely flaked out and missed this message. I am truly sorry and feel like a jerk. I fixed the my code and re-tested it. The server I originally tested this on does not exist. On my laptop (4 cpu, 16gb ram, SQL 2014) using the code that I posted above I get the following results against 10M rows:

Hey, don't feel that way. It's completely understandable that we can't be here all the time and miss some questions.
Thank you for sharing.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1596033
Posted Thursday, July 24, 2014 6:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
Alan.B (7/24/2014)
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:


Screw the code for a minute... are you ok, Alan? That's much more important.

And, considering that I'm just getting to this after a week, you're not alone in not being able to get to things.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1596055
Posted Monday, July 28, 2014 4:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 4:49 AM
Points: 556, Visits: 2,581
I am better now Jeff, thank you for asking. I dislocated a rib last week. Got it fixed this weekend. All better now. No more softball for me until next summer it appears.

-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1596759
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse