InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 09:10:36 GMT20http://www.sqlservercentral.com/Forums/Topic1381289-391-1.aspx[quote][b]Dave Ballantyne (11/6/2012)[/b][hr]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 :)[/quote]That's interesting. When I did the performance analysis in this article on UNPIVOT ([url]http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/[/url]), I considered sparseness and I didn't notice any marked difference.Not saying you're wrong mind you. Just noting.Tue, 06 Nov 2012 00:32:38 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1381289-391-1.aspxHi,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 :)Tue, 06 Nov 2012 00:20:22 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic1381289-391-1.aspxso 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.Mon, 05 Nov 2012 21:23:46 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1381289-391-1.aspxI got similar results to DwainMAXDOP 0 ( 8 cores )[code="plain"]CrossTab 2548CrossTab CTE 2559Pivot 7095[/code][code="plain"]CrossTabsTable '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.PivotTable '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.[/code]The CrossTabs had CPU times of around 11.5 secsMAXDOP 1[code="plain"]CrossTab 7571CrossTab CTE 7571Pivot 6999[/code][code="plain"]CrossTabs and PivotTable '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.[/code]Mon, 05 Nov 2012 20:01:02 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1381289-391-1.aspxLevelling the playing field by adding OPTION (MAXDOP 1) to the crosstab queries (including an additional one of my own design), I got these results:[code="plain"]Method AVGDurationCrossTab 11544CrossTab CA 11746CrossTab CTE 11610Pivot 11389[/code]A pretty close heat but Pivot seems to have a slight edge.CrossTabCA:[code="sql"]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 AccountNumOPTION (MAXDOP 1)[/code]Mon, 05 Nov 2012 18:19:38 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1381289-391-1.aspxCH,Interesting test so I tried to reproduce your results. I used just 4 passes instead of the 10 in your loop and got this:[code="plain"]Method AVGDurationCrossTab 6483CrossTab CTE 6534Pivot 15032[/code]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.[code="plain"]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.[/code]Mon, 05 Nov 2012 17:59:57 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1381289-391-1.aspxCheck 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.Mon, 05 Nov 2012 17:48:29 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1381289-391-1.aspxOk 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.[code="sql"]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 cCREATE 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 = 1WHILE @cnt <= 10BEGINDBCC 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 )pPIVOT (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]))pvtINSERT INTO #ResultsSELECT '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 AccountNumINSERT INTO #ResultsSELECT '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 AccountNumINSERT INTO #ResultsSELECT 'CrossTab CTE', DATEDIFF(MS,@Start,GETDATE())SET @cnt = @cnt + 1ENDSELECT Method, AVG(Duration) 'AVGDuration' FROM #Results GROUP BY Method[/code]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:[code="sql"]Method AVGDuration---------------- -----------CrossTab 10409CrossTab CTE 10351Pivot 9149[/code]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.Mon, 05 Nov 2012 15:44:42 GMTCapnHector