March 6, 2012 at 10:28 pm
siva 20997 (3/6/2012)
I have my own theorey whay this is so fast, but I like others comment first P
It's because of a thing that Peter Larsson coined the phrase for... "Pre-Aggregation". If you had spent as much time reading the article I directed you to in the other post you referenced instead of arguing as much as you have on this post, you'd be an expert on the subject. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2012 at 10:44 pm
Jeff Moden (3/6/2012)
I just know I'm going to regret getting involved with this one. 😛 Testing in progress...
But you're so good at it! :Whistling: :w00t:
March 6, 2012 at 10:54 pm
Ok folks. First, I'm going to refer everyone to the article on "Converting Rows to Columns". If you haven't read it before, now would be a good time (take the hint, Siva ;-)) because it explains the idea of "Pre-Aggregation" as well as doing a whole bunch of performance testing explaining why PIVOT is a comparatively BAD choice compared to traditional cross tabs. Here's the link to the article...
http://www.sqlservercentral.com/articles/T-SQL/63681/
Just to make it a bit quicker to test, I reduced the number of rows that J Livingston SQL's fine test data generator created... a couple million rows on my 10 year old single CPU desktop is enough to prove the point.
DROP TABLE SivaTest
GO
SELECT TOP 2000000
ID = IDENTITY(INT, 1, 1),
igroup = 1 + CAST(Abs(Checksum(Newid()) %4 ) AS INT)
INTO SivaTest
FROM sys.all_columns ac1 --Contains 4000 rows even on a brand new system
CROSS JOIN sys.all_columns ac2 --so single cross join is more than enough for up to 16 million rows
ALTER TABLE [dbo].[SivaTest] ADD CONSTRAINT [PKst_] PRIMARY KEY CLUSTERED ([ID] ASC)
GO
CREATE NONCLUSTERED INDEX [ix] ON [dbo].[SivaTest]
([igroup] ASC)
Both David and Howard had the right idea except they forgot the primary rule of computing... keep the data and presentation layers separate. They combined the crosstab with the aggregation instead of pre-aggregating the data first and then cross-tabbing the data. As you'll see, it makes all the difference in the world as it did for Siva's pivot.
Here's the test code which plays against the data table created above.
SET STATISTICS TIME OFF;
PRINT '========== Siva Method =================================================';
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
With GroupCTE(iGroup,nCount) as
(SELECT igroup,COUNT(id) FROM SivaTest group by igroup),
PivotedCTE(Group1,Group2,Group3,Group4) as
(select coalesce([1],0) as Group1,coalesce([2],0) as Group2,coalesce([3],0) as Group3,coalesce([4],0) as Group4 from
(Select iGroup,nCount from groupCTE)
DataTable Pivot
(Sum (nCount)
for iGroup in ([1],[2],[3],[4])
) as PivotTable)
Select (Group1+Group2+Group3+Group4) as Total,Group1,Group2,Group3,Group4 From PivotedCTE
SET STATISTICS TIME OFF;
GO
PRINT '========== Howard Method ===============================================';
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
SELECT COUNT(*) AS Total,
COUNT(CASE WHEN igroup=1 THEN 1 END) AS group1,
COUNT(CASE WHEN igroup=2 THEN 1 END) AS group2,
COUNT(CASE WHEN igroup=3 THEN 1 END) AS group3,
COUNT(CASE WHEN igroup=4 THEN 1 END) AS group4
FROM SivaTest
SET STATISTICS TIME OFF;
GO
PRINT '========== David Method ================================================';
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
select
sum(1) as Total,
sum(case igroup when 1 then 1 end) as Group1,
sum(case igroup when 2 then 1 end) as Group2,
sum(case igroup when 3 then 1 end) as Group3,
sum(case igroup when 4 then 1 end) as Group4
from SivaTest
SET STATISTICS TIME OFF;
GO
PRINT '========== Jeff Pre-Aggregated Crosstab Method =========================';
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
WITH
ctePreAgg AS
(
SELECT IGroup,
GroupCount = COUNT(*)
FROM dbo.SivaTest
GROUP BY IGroup
)
SELECT Total = SUM(GroupCount),
Group1 = SUM(CASE WHEN IGroup = 1 THEN GroupCount ELSE 0 END),
Group2 = SUM(CASE WHEN IGroup = 2 THEN GroupCount ELSE 0 END),
Group3 = SUM(CASE WHEN IGroup = 3 THEN GroupCount ELSE 0 END),
Group4 = SUM(CASE WHEN IGroup = 4 THEN GroupCount ELSE 0 END)
FROM ctePreAgg;
SET STATISTICS TIME OFF;
GO
Here are the results on my humble desktop.
========== Siva Method =================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 1015 ms, elapsed time = 1128 ms.
========== Howard Method ===============================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 2640 ms, elapsed time = 2722 ms.
========== David Method ================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 3421 ms, elapsed time = 3600 ms.
========== Jeff Pre-Aggregated Crosstab Method =========================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 1000 ms, elapsed time = 1068 ms.
Now, in this case, the pre-aggregated cross-tab only edged out Siva's pre-aggregated PIVOT by a nose. BUT, which would you rather read or troubleshoot? As a reminder, here's the PIVOT code...
With GroupCTE(iGroup,nCount) as
(SELECT igroup,COUNT(id) FROM SivaTest group by igroup),
PivotedCTE(Group1,Group2,Group3,Group4) as
(select coalesce([1],0) as Group1,coalesce([2],0) as Group2,coalesce([3],0) as Group3,coalesce([4],0) as Group4 from
(Select iGroup,nCount from groupCTE)
DataTable Pivot
(Sum (nCount)
for iGroup in ([1],[2],[3],[4])
) as PivotTable)
Select (Group1+Group2+Group3+Group4) as Total,Group1,Group2,Group3,Group4 From PivotedCTE
... and here's the pre-aggregated cross-tab code.
WITH
ctePreAgg AS
(
SELECT IGroup,
GroupCount = COUNT(*)
FROM dbo.SivaTest
GROUP BY IGroup
)
SELECT Total = SUM(GroupCount),
Group1 = SUM(CASE WHEN IGroup = 1 THEN GroupCount ELSE 0 END),
Group2 = SUM(CASE WHEN IGroup = 2 THEN GroupCount ELSE 0 END),
Group3 = SUM(CASE WHEN IGroup = 3 THEN GroupCount ELSE 0 END),
Group4 = SUM(CASE WHEN IGroup = 4 THEN GroupCount ELSE 0 END)
FROM ctePreAgg;
There IS a way to beat even the pre-aggregated cross-tab code but I'll be damned if I can remember it just now. The day's coffee has finally worn off and I'm hitting the hay. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2012 at 11:11 pm
Ran Jeff's tests on my PC at home, it is only 7 years old and has a hyperthreaded processor. Here are my results:
========== Siva Method =================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
Table 'SivaTest'. Scan count 3, logical reads 3511, physical reads 18, read-ahead reads 3482, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1454 ms, elapsed time = 1268 ms.
========== Howard Method ===============================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
Table 'SivaTest'. Scan count 3, logical reads 3503, physical reads 10, read-ahead reads 3482, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3750 ms, elapsed time = 3499 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
========== David Method ================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
Table 'SivaTest'. Scan count 3, logical reads 3503, physical reads 8, read-ahead reads 3482, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5313 ms, elapsed time = 3264 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
========== Jeff Pre-Aggregated Crosstab Method =========================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
Table 'SivaTest'. Scan count 3, logical reads 3511, physical reads 17, read-ahead reads 3482, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1375 ms, elapsed time = 977 ms.
March 6, 2012 at 11:16 pm
You added "IO" to the mix, Lynn. Probably not a bad thing to do. Thank you, Sir. The "Physical Reads" are a bit ironic.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2012 at 11:18 pm
SQLKnowItAll (3/6/2012)
siva 20997 (3/6/2012)
are you saying my method was faster even with the UNION ?Why are you obsessed with speed? Did you not see the reads and scans?
Nah... he's not obsessed with speed. I AM! 😀
[font="Arial Black"]"Make it work, make it fast, make it purdy... and it ain't done 'til it's purdy!"[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2012 at 11:26 pm
Sean Lange (3/6/2012)
I sort of remember that thread too. I think that Jeff was trying to say that the "best" solution did not need to use a pivot. I don't recall if he ever posted his way of doing that without a pivot or not but I know that several others had posted solutions using a pivot.
I took care of that problem this time. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2012 at 11:30 pm
Jeff Moden (3/6/2012)
You added "IO" to the mix, Lynn. Probably not a bad thing to do. The "Physical Reads" are a bit ironic.
Yes, yes I did.
It was interesting. I thought about going back into the code to add MAXDOP 1 so that it didn't go parallel, but thought it added something to the mix.
March 7, 2012 at 2:37 am
Morning Folks, Is every one on this site in UK ?
Let me tell you my reasoning why I do it my way. When I got switched over to SQL few years back I had problems and couldn't and still cant find good material on to work out how SQl query works. It was the David Durrants article on this site which confirmed certain things for me.The problem I had about 2007 was that I needed about 3 temp tables to solve a problem. I dont like the idea of temp tables as I have used them outside SQL and it was a pin to maintain them make sure it didnt clash with another user etc
So when I saw the CTE I grabbed it and used it heavily. The data I was processing with single stored procedure had less than 100 rows as source from 3 or 4 tables and 100 rows as result but needed so much manipulation. It will be too much to discuss that here but it still is used by a very large automation system.
During the years I knew it was doing parallel processing. that the second task can start even before the first finish. So I was satisfied CTE's are ok.
But this one cant behave that way because the second Pivot can not start till the first finish
here I have a another theory. If you write some code in VB or TSQL which is on the surface layer where your code is running the information has to pass between your layer and the layer which actually does the job many time. It like me ordering 10000 Cycles to me made in china and them sending me information after every one is made over some means such as telephone or telex. where as if they were to repot to me every 1000 made it will get faster. report at the end is the fastest
So the aggregate function like giving a order to SQL to group and it doest not report to the hire layer till it is finished. which is fast. So it is fast and then it reports to the second task and so on. If the tasks can operate in parallel the time taken can be less than sum will total of time . I don think it will work in parralel with temp tables. With CTE it will
My solution has been Collate, Pivot and then Format output. I will most probably have minimum 3 sections to my CTE Logic
Now the second layer is the PIVOT. at most we will Pivot only a grid of the size 100 by 100. In practice much less.why do I say that. If you are collating something like Turnover etc you collate by months or week etc. They appear in rows. When you Pivot it they move to Columns. a human does not want results in 100 Columns. Much less.
Also no one is going to group 100 Columns in from data . Much less. so when you Pivot they become rows. so your Pivot is more or less done from cache. Should happen in no time without any resource overhead
The third needs extra information from other tables to put the column headers correctly. In the other thread Ashaldi used a string as a parameter to achive this. It worked but the code to me is not elegant as I would like to be
I would use my 3rd layer to put the titles correctly from other physical tables or CTE I would have prepared earlier at the appropriate stage
These are all my theories much with intutuion and bit of reading there and here. Dont shoot me if you dont agree just tell me where I should read what
March 7, 2012 at 2:46 am
I missed this thread until this morning, I want to play 😀
Here's a bit of a mathematical answer to pivoting: -
SELECT
SUM(eachGroup) AS Total,
SUM(eachGroup*(1-ABS(SIGN(IGroup-1)))) AS Group1,
SUM(eachGroup*(1-ABS(SIGN(IGroup-2)))) AS Group2,
SUM(eachGroup*(1-ABS(SIGN(IGroup-3)))) AS Group3,
SUM(eachGroup*(1-ABS(SIGN(IGroup-4)))) AS Group4
FROM (SELECT IGroup, COUNT(*) AS eachGroup
FROM dbo.SivaTest
GROUP BY IGroup) a;
SET NOCOUNT ON
SELECT TOP 2000000
ID = IDENTITY(INT, 1, 1),
igroup = 1 + CAST(Abs(Checksum(Newid()) %4 ) AS INT)
INTO SivaTest
FROM sys.all_columns ac1 --Contains 4000 rows even on a brand new system
CROSS JOIN sys.all_columns ac2; --so single cross join is more than enough for up to 16 million rows
ALTER TABLE [dbo].[SivaTest] ADD CONSTRAINT [PKst_] PRIMARY KEY CLUSTERED ([ID] ASC);
GO
CREATE NONCLUSTERED INDEX [ix] ON [dbo].[SivaTest]
([igroup] ASC);
SET STATISTICS TIME OFF;
PRINT '========== Siva Method =================================================';
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH GroupCTE (iGroup, nCount)
AS (SELECT igroup, COUNT(id)
FROM SivaTest
GROUP BY igroup),
PivotedCTE (Group1, Group2, Group3, Group4)
AS (SELECT coalesce([1], 0) AS Group1, coalesce([2], 0) AS Group2, coalesce([3], 0) AS Group3,
coalesce([4], 0) AS Group4
FROM (SELECT iGroup, nCount
FROM groupCTE) DataTable
Pivot(Sum(nCount) FOR iGroup IN ([1], [2], [3], [4])) AS PivotTable)
SELECT (Group1 + Group2 + Group3 + Group4) AS Total, Group1, Group2, Group3, Group4
FROM PivotedCTE;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
PRINT '========== Howard Method ===============================================';
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT COUNT(*) AS Total,
COUNT(CASE WHEN igroup=1 THEN 1 END) AS group1,
COUNT(CASE WHEN igroup=2 THEN 1 END) AS group2,
COUNT(CASE WHEN igroup=3 THEN 1 END) AS group3,
COUNT(CASE WHEN igroup=4 THEN 1 END) AS group4
FROM SivaTest
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
PRINT '========== David Method ================================================';
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
select
sum(1) as Total,
sum(case igroup when 1 then 1 end) as Group1,
sum(case igroup when 2 then 1 end) as Group2,
sum(case igroup when 3 then 1 end) as Group3,
sum(case igroup when 4 then 1 end) as Group4
from SivaTest
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
PRINT '========== Jeff Pre-Aggregated Crosstab Method =========================';
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH
ctePreAgg AS
(
SELECT IGroup,
GroupCount = COUNT(*)
FROM dbo.SivaTest
GROUP BY IGroup
)
SELECT Total = SUM(GroupCount),
Group1 = SUM(CASE WHEN IGroup = 1 THEN GroupCount ELSE 0 END),
Group2 = SUM(CASE WHEN IGroup = 2 THEN GroupCount ELSE 0 END),
Group3 = SUM(CASE WHEN IGroup = 3 THEN GroupCount ELSE 0 END),
Group4 = SUM(CASE WHEN IGroup = 4 THEN GroupCount ELSE 0 END)
FROM ctePreAgg;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
PRINT '========== Cad Method ==========';
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT
SUM(eachGroup) AS Total,
SUM(eachGroup*(1-ABS(SIGN(IGroup-1)))) AS Group1,
SUM(eachGroup*(1-ABS(SIGN(IGroup-2)))) AS Group2,
SUM(eachGroup*(1-ABS(SIGN(IGroup-3)))) AS Group3,
SUM(eachGroup*(1-ABS(SIGN(IGroup-4)))) AS Group4
FROM (SELECT IGroup, COUNT(*) AS eachGroup
FROM dbo.SivaTest
GROUP BY IGroup) a;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
DROP TABLE SivaTest;
Comparable with Jeff's Pre-Aggregated Crosstab on my machine: -
========== Siva Method =================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SivaTest'. Scan count 5, logical reads 3522, physical reads 67, read-ahead reads 3483, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 624 ms, elapsed time = 768 ms.
========== Howard Method ===============================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SivaTest'. Scan count 5, logical reads 3522, physical reads 47, read-ahead reads 3483, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1092 ms, elapsed time = 779 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
========== David Method ================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SivaTest'. Scan count 5, logical reads 3522, physical reads 32, read-ahead reads 3483, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1217 ms, elapsed time = 752 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
========== Jeff Pre-Aggregated Crosstab Method =========================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SivaTest'. Scan count 5, logical reads 3522, physical reads 68, read-ahead reads 3483, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 452 ms, elapsed time = 741 ms.
========== Cad Method ==========
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SivaTest'. Scan count 5, logical reads 3522, physical reads 68, read-ahead reads 3483, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 729 ms.
March 7, 2012 at 3:20 am
I have read the article "Converting Rows to Columns".
I dont undertand why at the start we are using the case stament to split it into 4 quarters
group By year,Quarter would have done that
Am I missing something here ?
March 7, 2012 at 6:05 am
siva 20997 (3/7/2012)
I have read the article "Converting Rows to Columns".I dont undertand why at the start we are using the case stament to split it into 4 quarters
group By year,Quarter would have done that
Am I missing something here ?
It was to prepare people for the idea that pre-aggregation was going to be faster. The example came straight from Books Online and it was part of my goal to demonstrate how to improve on it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply