November 5, 2012 at 3:44 pm
Ok so im working on a query and this will be the first time im really testing any thing so i figured i would set up a quick performance test between cross tabs and a pivot to test out a possible methodology for my actual tests. The bellow code shows the timing method ill be using for the test.
CREATE TABLE PivotTest (
ID INT IDENTITY(1,1),
AccountNum INT,
TransDate DATETIME,
Ammount NUMERIC(8,4)
)
INSERT INTO PivotTest (AccountNum, TransDate, Ammount)
SELECT top 1000000 ABS(CHECKSUM(NEWID())) % 1000 AS AccountNum,
DATEADD(DD,(ABS(CHECKSUM(NEWID())) % 365),'2010-01-01') AS TransDate,
(ABS(CHECKSUM(NEWID())) % 10000000) * 1.0 / 10000 AS Ammount
FROM Tally a, Tally b, Tally c
CREATE CLUSTERED INDEX UCX_TransDate ON PivotTest(TransDate)
CREATE TABLE #Results (
Method VARCHAR(16),
Duration INT
)
DECLARE @Start DATETIME,
@Acct INT,
@mth NUMERIC(15,4),
@cnt INT = 1
WHILE @cnt <= 10
BEGIN
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @Start = GETDATE()
SELECT @Acct = AccountNum,
@mth = [2010-01-01],
@mth = [2010-02-01],
@mth = [2010-03-01],
@mth = [2010-04-01],
@mth = [2010-05-01],
@mth = [2010-06-01],
@mth = [2010-07-01],
@mth = [2010-08-01],
@mth = [2010-09-01],
@mth = [2010-10-01],
@mth = [2010-11-01],
@mth = [2010-12-01]
FROM (
SELECT AccountNum, DATEADD(MM,DATEDIFF(MM,0,TransDate),0) AS TransMonth, Ammount
FROM PivotTest
)p
PIVOT (
SUM(Ammount)
FOR TransMonth IN ([2010-01-01],[2010-02-01],[2010-03-01],[2010-04-01],
[2010-05-01],[2010-06-01],[2010-07-01],[2010-08-01],
[2010-09-01],[2010-10-01],[2010-11-01],[2010-12-01])
)pvt
INSERT INTO #Results
SELECT 'Pivot', DATEDIFF(MS,@Start,GETDATE())
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @Start = GETDATE()
SELECT @Acct = AccountNum,
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-01-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-02-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-03-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-04-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-05-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-06-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-07-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-08-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-09-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-10-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-11-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN DATEADD(MM,DATEDIFF(MM,0,TransDate),0) = '2010-12-01' THEN Ammount ELSE 0 END)
FROM PivotTest
GROUP BY AccountNum
INSERT INTO #Results
SELECT 'CrossTab', DATEDIFF(MS,@Start,GETDATE())
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @Start = GETDATE()
;WITH Base AS (SELECT AccountNum, DATEADD(MM,DATEDIFF(MM,0,TransDate),0) AS Mnth, Ammount FROM PivotTest)
SELECT @Acct = AccountNum,
@mth = SUM(CASE WHEN Mnth = '2010-01-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-02-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-03-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-04-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-05-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-06-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-07-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-08-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-09-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-10-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-11-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-12-01' THEN Ammount ELSE 0 END)
FROM Base
GROUP BY AccountNum
INSERT INTO #Results
SELECT 'CrossTab CTE', DATEDIFF(MS,@Start,GETDATE())
SET @cnt = @cnt + 1
END
SELECT Method, AVG(Duration) 'AVGDuration'
FROM #Results
GROUP BY Method
and the results were some what surprising unless i made a mistake somewhere, i did not expect a 10% improvement of a pivot table over cross tab:
Method AVGDuration
---------------- -----------
CrossTab 10409
CrossTab CTE 10351
Pivot 9149
EDIT: This is also because i have always wondered which is faster between cross tab and pivot and i thought it would make for some good testing fodder.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 5, 2012 at 5:48 pm
Check the logical reads with profiler, it's a good chance that it is the same for all three examples.
Check the execution plan and see the differences (if any).
The methods you use are all alike, there is almost no difference in performance.
You should not measure just one execution, but at least 3 executions per each method and take the average elapsed time and logical reads.
Logical reads is more reliable parameter (than elapsed time) you should look at to see if one method is better than other.
Other methods of optimization will probably give you a better gain in performance. For example, a covering index.
November 5, 2012 at 5:59 pm
CH,
Interesting test so I tried to reproduce your results. I used just 4 passes instead of the 10 in your loop and got this:
Method AVGDuration
CrossTab 6483
CrossTab CTE 6534
Pivot 15032
Up to 100,000 rows they were all tied. But at 1M, SQL appears to have parallelized the crosstab queries on my box because I saw this (example) in the results where I set STATISTICS TIME ON.
Pivot
SQL Server Execution Times:
CPU time = 10983 ms, elapsed time = 11484 ms.
CROSSTAB
SQL Server Execution Times:
CPU time = 17847 ms, elapsed time = 5759 ms.
CROSSTAB CTE
SQL Server Execution Times:
CPU time = 18158 ms, elapsed time = 5954 ms.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 5, 2012 at 6:19 pm
Levelling the playing field by adding OPTION (MAXDOP 1) to the crosstab queries (including an additional one of my own design), I got these results:
Method AVGDuration
CrossTab11544
CrossTab CA11746
CrossTab CTE11610
Pivot 11389
A pretty close heat but Pivot seems to have a slight edge.
CrossTabCA:
SELECT @Acct = AccountNum,
@mth = SUM(CASE WHEN Mnth = '2010-01-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-02-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-03-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-04-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-05-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-06-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-07-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-08-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-09-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-10-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-11-01' THEN Ammount ELSE 0 END),
@mth = SUM(CASE WHEN Mnth = '2010-12-01' THEN Ammount ELSE 0 END)
FROM #PivotTest
CROSS APPLY (
SELECT DATEADD(MM,DATEDIFF(MM,0,TransDate),0) AS Mnth) a
GROUP BY AccountNum
OPTION (MAXDOP 1)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 5, 2012 at 8:01 pm
I got similar results to Dwain
MAXDOP 0 ( 8 cores )
CrossTab2548
CrossTab CTE2559
Pivot7095CrossTabs
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PivotTest'. Scan count 9, logical reads 4801, physical reads 34, read-ahead reads 4739, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Pivot
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PivotTest'. Scan count 1, logical reads 4723, physical reads 5, read-ahead reads 4733, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The CrossTabs had CPU times of around 11.5 secs
MAXDOP 1
CrossTab7571
CrossTab CTE7571
Pivot6999CrossTabs and Pivot
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PivotTest'. Scan count 1, logical reads 4723, physical reads 5, read-ahead reads 4733, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
November 5, 2012 at 9:23 pm
so its looking that on by dual core box i did not hit any parallelism (ill have to look at the query plans tomorrow) and if i had cross tabs would have been faster. ill also have to add in statistics io to the testing or run the profiler at the same time im running the queries.
glad to know i am at least on the right track for a test methodology as well.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 6, 2012 at 12:20 am
Hi,
I would be careful about making any form of 'X is faster than Y' statements , ever!
In this case i have found ,annecdotally, that PIVOT has a relatively high startup cost.
So, if you have a 'sparse' set of data if can be faster to use the cross tab, with more dense PIVOT. YMMV 🙂
November 6, 2012 at 12:32 am
Dave Ballantyne (11/6/2012)
Hi,I would be careful about making any form of 'X is faster than Y' statements , ever!
In this case i have found ,annecdotally, that PIVOT has a relatively high startup cost.
So, if you have a 'sparse' set of data if can be faster to use the cross tab, with more dense PIVOT. YMMV 🙂
That's interesting. When I did the performance analysis in this article on UNPIVOT (http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/), I considered sparseness and I didn't notice any marked difference.
Not saying you're wrong mind you. Just noting.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply