SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can the multiple CASE statments be optimized?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
419 Posts

Posted - 04/01/2013 :  13:57:35  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 04/01/2013 :  14:15:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 04/01/2013 :  16:00:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000