T-SQL PIVOT vs. Expressions

  • I am trying to tune a view which contains a crosstab built with a SUM + CASE statement.

    SUM(CASE Field WHEN 7 THEN 1 ELSE 0 END) type of thing....

    This definitely slows down the view but would using the PIVOT command do anything in the way of improving performance?

  • The only way to truly know is to try it on your server and see how it works. Start with your execution plans for each of them and go from there.

    In my limited experience however, case statements are usually more efficient.

    Fraggle

  • I disagree with Fraggle - I haven't seen PIVOT generate any perf gains. As a matter of fact - given its limitations, it really hasn't been of much use, and in my case is more likely to make things run slower than faster (since it can't "pivot" multiple aggregations unless you create separate statements, etc....)

    So - give it a shot, but I wouldn't hang my hat on it. Now - if you were to preaggregate the data, and THEN pivot it (or use the old CASE syntax), that's an area where you might stand to make some gains perf-wise.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt, I agree with you. I have never seen a PIVOT help in performance, but I hestitate to say that this will always be the case. When ever I say that, someone shows up on my door step to prove me wrong. 😀

    Fraggle

  • Fraggle (9/30/2008)


    Matt, I agree with you. I have never seen a PIVOT help in performance, but I hestitate to say that this will always be the case. When ever I say that, someone shows up on my door step to prove me wrong. 😀

    Fraggle

    Heh - I was trying to say I AGREE with you, but I guess I was in a contrary mood today....:P

    Nice to disagree with oyu, and yet fully reinforce everything you said....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Just so long as well all know that we agree and disagree about the same thing we are agreed upon. 😀

  • See the "performance" section of the following article...

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply