Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can the multiple CASE statments be optimized?

Author  Topic 

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-04-01 : 13:57:35
I wrote a SELECT statment for a report but it uses the CASE statment a lot and each time it is checking for the same value. Is there a way I can optimize this a bit more?

SELECT t.Terminal_Name AS Location,
di.ProductName + ' (' + di.ProductNumber + ')' AS Product,
null AS 'Total Tank Capacity',
Begging = CASE WHEN t.Gross_Gallons_Terminal = 'Y'
THEN
di.StartingGrossInv
ELSE
di.STartingNetInv
END,
Receipts = CASE WHEN t.Gross_Gallons_Terminal = 'Y'
THEN
r.Gross
ELSE
r.Net
END,
Adjustments = CASE WHEN t.Gross_Gallons_Terminal = 'Y'
THEN
a.Gross
ELSE
a.Net
END,
Sales = CASE WHEN t.Gross_Gallons_Terminal = 'Y'
THEN
s.Gross
ELSE
s.Net
END,
Ending = CASE WHEN t.Gross_Gallons_Terminal = 'Y'
THEN
di.EndingGrossInv
ELSE
di.EndingNetInv
END,
Type = CASE WHEN t.Gross_Gallons_Terminal = 'Y'
THEN
'Gross'
ELSE
'Net'
END,
tp.Temp,
tp.Gravity,
null AS 'End < Capacity',
'End < 0' = CASE WHEN t.Gross_Gallons_Terminal = 'Y' AND di.EndingGrossINV > 0
THEN
'OK'
WHEN t.Gross_Gallons_Terminal <> 'Y' AND di.EndingNetInv > 0
THEN
'OK'
ELSE
'***ERROR***'
END


--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-04-01 : 14:15:58
Just a shot in the dark... Perhaps breaking the query into two subqueries and using a UNION ALL to combine them. Each subquery would have a WHERE clause that acted on t.Gross_Gallons_Terminal and looked for either "t.Gross_Gallons_Terminal = 'Y'" or "t.Gross_Gallons_Terminal <> 'Y'" conditions.
Will this speed things up? Give it a try and see.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-01 : 16:00:25
I wouldn't think that those case statements should add much overhead to your execution time. How do you know the CASE statements are the problem? If you simply select out the columns t.*, di.* does it speed up? I'm guessing not.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -