T-SQL PIVOT vs. Expressions

Question

T-SQL PIVOT vs. Expressions

CountCet

Ten Centuries

Points: 1371
More actions
September 30, 2008 at 2:39 pm

#198795

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?

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

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

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 1

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 3

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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?

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

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

http://qa.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)
Intro to Tally Tables and Functions