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

Simple Performance testing questions Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 3:44 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:16 AM
Points: 945, Visits: 1,769
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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1381289
Posted Monday, November 5, 2012 5:48 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:57 AM
Points: 369, Visits: 1,208
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.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1381329
Posted Monday, November 5, 2012 5:59 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 @ 8:28 PM
Points: 3,609, Visits: 5,219
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1381330
Posted Monday, November 5, 2012 6:19 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 @ 8:28 PM
Points: 3,609, Visits: 5,219
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
CrossTab 11544
CrossTab CA 11746
CrossTab CTE 11610
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1381335
Posted Monday, November 5, 2012 8:01 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:37 PM
Points: 947, Visits: 2,869
I got similar results to Dwain

MAXDOP 0 ( 8 cores )
CrossTab	2548
CrossTab CTE 2559
Pivot 7095
CrossTabs
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
CrossTab	7571
CrossTab CTE 7571
Pivot 6999
CrossTabs 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.
Post #1381345
Posted Monday, November 5, 2012 9:23 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:16 AM
Points: 945, Visits: 1,769
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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1381362
Posted Tuesday, November 6, 2012 12:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:03 AM
Points: 1,949, Visits: 8,303
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 :)





Clear Sky SQL
My Blog
Kent user group
Post #1381412
Posted Tuesday, November 6, 2012 12:32 AM


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 @ 8:28 PM
Points: 3,609, Visits: 5,219
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1381413
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse