Too many CASE statements...how to avoid??

Question

Too many CASE statements...how to avoid??

Viewing 7 posts - 16 through 21 (of 21 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

CELKO (8/4/2010)
...Also, start using COALESCE() instead of ISNULL() ...

Why is that, Joe? I use COALESCE() if I want to return the first non-null value from a comma-delimited list of values. If the list has only one element, I use ISNULL() - because I assume that COALESCE() is more expensive than ISNULL().

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Chris Morris-439714 (8/5/2010)
CELKO (8/4/2010)
...Also, start using COALESCE() instead of ISNULL() ...
Why is that, Joe? I use COALESCE() if I want to return the first non-null value from a comma-delimited list of values. If the list has only one element, I use ISNULL() - because I assume that COALESCE() is more expensive than ISNULL().

Cause COALESCE contains four letters from Joe surname and ISNULL only 2...:-D

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 3

Hi..

Any one can optimize(physically/logically) my code..

if possible please post the re-written code..

[font="Comic Sans MS"]Praveen Goud[/font]

Ben Teraberry SSChampion Points: 11845 More actions · Answer 4

Praveen Goud Kotha (8/5/2010)
Hi..
Any one can optimize(physically/logically) my code..
if possible please post the re-written code..

If you don't want to do any work I would suggest you hire a consultant.

You haven't provided the base data or table structures. You've merely provided the results of some pivot or crosstab operation (which is completely counterproductive to do before the analysis) that hasn't been explained.

The funny thing is that the majority of the solution has already been provided. You just need to take the effort to apply it to your actual system.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 5

bteraberry (8/5/2010)
If you don't want to do any work I would suggest you hire a consultant.
You haven't provided the base data or table structures. You've merely provided the results of some pivot or crosstab operation (which is completely counterproductive to do before the analysis) that hasn't been explained.
The funny thing is that the majority of the solution has already been provided. You just need to take the effort to apply it to your actual system.

Thank you so much....teraberry for your suggestion..

[font="Comic Sans MS"]Praveen Goud[/font]

SLeitch Newbie Points: 3 More actions · Answer 6

Here's one way you could rewrite your code, using drew.allen's unpivot query. Takes about 30ms on my server.

select empid, apr, may, jun, jul, aug, sep, oct, nov, [dec], jan, feb, mar,

Incidents, TScore, [<=0], [>0 and <=30], [>30 and <=45], [>45 and <=60], [>60], TScored, [T%],

( Case When [T%] >= 90 then 'A+'

When [T%] >= 85 then 'A'

When [T%] >= 65 then 'B+'

When [T%] >= 40 then 'B'

When [T%] >= 20 then 'C+'

When [T%] < 20 then 'C'

end ) Rating

from (

select a.empid,

coalesce(apr,0) apr, coalesce(may,0) may, coalesce(jun,0) jun, coalesce(jul,0) jul, coalesce(aug,0) aug, coalesce(sep,0) sep,

coalesce(oct,0) oct, coalesce(nov,0) nov, coalesce(dec,0) dec, coalesce(jan,0) jan, coalesce(feb,0) feb, coalesce(mar,0) mar,

a.Incidents, a.TScore, [<=0], [>0 and <=30], [>30 and <=45], [>45 and <=60], [>60],

([<=0] * 5 + [>0 and <=30] * 4 + [>30 and <=45] * 3 + [>45 and <=60] * 2 + [>60] * 1) TScored,

((100 * ([<=0] * 5 + [>0 and <=30] * 4 + [>30 and <=45] * 3 + [>45 and <=60] * 2 + [>60] * 1)) / a.TScore) as 'T%'

from @emp e cross apply (

select n.empid,

SUM(cnt) as Incidents,

SUM(cnt) * 5 as TScore,

SUM(case when category = '<=0' then cnt else 0 end) as '<=0',

SUM(case when category = '>0 and <=30' then cnt else 0 end) as '>0 and <=30',

SUM(case when category = '>30 and <=45' then cnt else 0 end) as '>30 and <=45',

SUM(case when category = '>45 and <=60' then cnt else 0 end) as '>45 and <=60',

SUM(case when category = '>60' then cnt else 0 end) as '>60'

from (

SELECT empid

, CASE

WHEN Num > 60 THEN '>60'

WHEN Num > 45 THEN '>45 and <=60'

WHEN Num > 30 THEN '>30 and <=45'

WHEN Num > 0 THEN '>0 and <=30'

ELSE '<=0'

END AS Category

, mon

, num

, 1 AS Cnt

FROM (

SELECT empid, apr, may, jun, jul, aug, sep, oct, nov, [dec], jan, feb, mar

FROM @emp

) AS p

UNPIVOT (

num

FOR mon IN ( apr, may, jun, jul, aug, sep, oct, nov, [dec], jan, feb, mar )

) AS u

) n

group by n.empid

) a where a.empid = e.empid

) b

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 7

SLeitch (8/6/2010)
Here's one way you could rewrite your code, using drew.allen's unpivot query. Takes about 30ms on my server.

Thnx Sleitch..for the code provided..

wht i have observed so far is:(by only 20records in my table)

Using my code:

Estimated Subtree cost = 0.0032832

Cache plan size = 1926B

whereas using the code posted by you:

Estimated Subtree cost = 0.0358718

Cache plan size = 195B

whereas my actual table consists of more than 7000 records..

can anyone help either the Cache plan size Or estimated cost should be considered while optimizing the query OR both of them..

[font="Comic Sans MS"]Praveen Goud[/font]