September 30, 2008 at 2:39 pm
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?
September 30, 2008 at 2:52 pm
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
September 30, 2008 at 3:05 pm
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?
September 30, 2008 at 3:29 pm
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
September 30, 2008 at 5:00 pm
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?
September 30, 2008 at 7:17 pm
Just so long as well all know that we agree and disagree about the same thing we are agreed upon. 😀
September 30, 2008 at 8:26 pm
See the "performance" section of the following article...
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply