Simple Performance testing questions

  • 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[/url] 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[/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]

  • 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
  • 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![/I]

    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

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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.

  • 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[/url] 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[/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]

  • 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[/url]

  • 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![/I]

    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