SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple Performance testing questions


Simple Performance testing questions

Author
Message
CapnHector
CapnHector
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3141 Visits: 1789
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
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1888 Visits: 1266
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

dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17315 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17315 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
mickyT
mickyT
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2774 Visits: 3318
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.

CapnHector
CapnHector
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3141 Visits: 1789
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
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6958 Visits: 8370
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 Smile



Clear Sky SQL
My Blog
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17315 Visits: 6431
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 Smile



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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search