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 04, 2014 5:40 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: Thursday, April 17, 2014 2:02 PM
Points: 500, Visits: 2,291
Hello Everyone.

This should be an easy one. It's been a long time since I have used PIVOT and think I forgot something. Take a look at these two queries. The first query provides the desired results. I am trying to get the same results using PIVOT but seem to be missing something.

IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;
CREATE TABLE #p(id int, qtr tinyint, sales int);

DECLARE @rows int = 10000;
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;

-- DESIRED RESULTS
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;

-- I AM STUCK HERE
SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4
FROM #p AS p
PIVOT(
SUM(sales)
FOR qtr IN ([1],[2],[3],[4])
) AS pivottbl;

Thanks!


-- 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 #1537999
Posted Tuesday, February 04, 2014 5:51 PM This worked for the OP Answer marked as solution


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
Hi Alan,
You need to change your table to a subquery involving only the grouping columns and the ones to be aggregated.
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;

Why wouldn't you want to use a nice pre-aggregated cross tab approach?
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;




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 #1538002
Posted Tuesday, February 04, 2014 5:54 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: Thursday, April 17, 2014 2:02 PM
Points: 500, Visits: 2,291
Luis Cazares (2/4/2014)
Hi Alan,
You need to change your table to a subquery involving only the grouping columns and the ones to be aggregated.
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;

Why wouldn't you want to use a nice pre-aggregated cross tab approach?
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;



Duh! (slapping self on head!) You rock as always Luis!


-- 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 #1538003
Posted Tuesday, February 04, 2014 8:48 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
Luis Cazares (2/4/2014)
Why wouldn't you want to use a nice pre-aggregated cross tab approach?
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;


+1000


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1538024
Posted Wednesday, February 05, 2014 11:31 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: Thursday, April 17, 2014 2:02 PM
Points: 500, Visits: 2,291

Luis Cazares (2/4/2014)


... Why wouldn't you want to use a nice pre-aggregated cross tab approach?
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;



I'm sorry Luis that I missed this question... I was in a hurry to leave work last night, saw that you had replied to my post and did not finish reading it before thanking you (I knew from the first sentence you wrote what I was doing wrong). I finished your post last night.

Thank you Jeff for jumping into the thread, I would have likely missed Luis' question otherwise. As a token of my thanks I sent you some snow.

Ironically, a conversation started last night when I, again, asked my friend/co-worker, Rob, if he read those Jeff Moden Articles I emailed him. Blah, blah... I was showing him why I use the technique in Jeff's article (Cross Tabs and Pivots, Part 1 – Converting Rows to Columns) instead of PIVOT. I was going to walk him through a the diferent techniques (including the pre-aggregations), do some tests, look at query plans but got stuck at the Pivot. We actually had the table #p filled with 1,000,000 rows when I got stuck on PIVOT (I never use PIVOT). I did not post the complete code...

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 FREEPROCCACHE

SET NOCOUNT ON;
SET STATISTICS TIME ON;
--what I need
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;

--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;

I have to run (it has not stopped snowing and we're being kicked out). I am going to chime back in later when I'm at home. If you get a moment (I know it's not snowing where you are Luis) run this code above. I have some more questions, such as, "why no parallelism with the cross tab" ? What does the query optimized assume that a serial version of this query will be faster?

Again, I'll chime in again later. Thanks to both of you!


Edit: Grammar.


-- 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 #1538309
Posted Wednesday, February 05, 2014 12:34 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
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
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;
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.



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 #1538331
Posted Wednesday, February 05, 2014 1:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
And this is why I really love cross-tabs. Pivot becomes completely annoying with multiple columns or calculations being pivoted. I struggled to get right the pivot approach.
Again, any comments are welcome.

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
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;
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,
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
PRINT 'Normal Pivot'
SET STATISTICS TIME ON;

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
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,
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
PRINT 'Pre-aggregated Pivot'
SET STATISTICS TIME ON;
--where I was stuck
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;

Results:
Normal Cross-tab
Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:
CPU time = 2160 ms, elapsed time = 2120 ms.


Normal Pivot

SQL Server Execution Times:
CPU time = 2540 ms, elapsed time = 1741 ms.


Pre-aggregated Cross-tab
Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:
CPU time = 1160 ms, elapsed time = 475 ms.


Pre-aggregated Pivot

SQL Server Execution Times:
CPU time = 1740 ms, elapsed time = 1271 ms.



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 #1538354
Posted Friday, February 07, 2014 12:38 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: Thursday, April 17, 2014 2:02 PM
Points: 500, Visits: 2,291
Luis. Thanks for putting all this together; I wanted to reply soon but it's been a busy couple days.

I have been playing around with the code you posted and re-visited Jeff's article about cross-tabs and pivots. I never read it in it's entirety and never really played with pre-aggregated cross-tabs and pre-aggregated pivots. This was an extremely helpful and informative thread.

I always used the cross-tab approach when pivoting because it was easier to read/write. PIVOT is a real drag when you need to do multiple aggregations; I am now learning that through experience.

What I find interesting is that, on my system (08R2, 64bit, 16gb RAM) & when dealing with one aggregation, the cross-tab approach shows a faster estimated plan but PIVOT performs better. 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.

-- (1) Sample data
IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;
CREATE TABLE #p
( id int primary key,
office int not null,
qtr int not null,
sales int not null);
GO

DECLARE @rows int = 2000000;
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(60*rand(convert(varbinary, newid()))),
ceiling(4*rand(convert(varbinary, newid()))),
ceiling(80*rand(convert(varbinary, newid())))+20
FROM iTally;
GO

CREATE INDEX c_oqs ON #p(office) INCLUDE (qtr, sales);
CREATE INDEX c_qs ON #p(qtr) INCLUDE (sales);
GO

-- single aggregate: 1m rows=no paralellism for CROSS-TAB, but yes for piv
-- both get paralell plan at 5m rows
---------------------------------------------------------------------------

-- quarters only
SET NOCOUNT ON;
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;

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

-- office & quarters only
SET STATISTICS TIME ON;
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 #p
GROUP BY office;

--piv
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

Results:
5M Rows (both create Parelell Plans)
crossTab


SQL Server Execution Times:
CPU time = 3120 ms, elapsed time = 783 ms.
pivot
SQL Server Execution Times:
CPU time = 1560 ms, elapsed time = 396 ms.

1M Rows (Only PIVOT creates a Parelell Plan)
crossTab
SQL Server Execution Times:
CPU time = 952 ms, elapsed time = 946 ms.

pivot
SQL Server Execution Times:
CPU time = 841 ms, elapsed time = 224 ms.

Comments questions welcome.

Thanks again Luis!


-- 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 #1539354
Posted Friday, February 07, 2014 1:20 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
Alan,
I was getting worried about you, it's good to know that you haven't been around because you were busy.
Back to the problem, you're cheating with your test. You didn't clean up the buffer after the cross tabs and before the pivot. At least not in the code you posted. That will give the second method (pivot in this case) a great advantage.



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 #1539370
Posted Tuesday, February 11, 2014 4:08 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: Thursday, April 17, 2014 2:02 PM
Points: 500, Visits: 2,291
Luis Cazares (2/7/2014)
...you're cheating with your test. You didn't clean up the buffer after the cross tabs and before the pivot. At least not in the code you posted. That will give the second method (pivot in this case) a great advantage.


Did not know to cleanup the buffer. Learned to do that in this thread. I have been doing so since.


-- 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 #1540485
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse