CASE WHEN THEN ELSE

We have a data set where some measurements (of type float) can be out of range, showing ##### from time to time. We have therefore included a BIT flag (default 0) to identify when a measurement is out of range. When querying our data set, we have used CASE WHEN condition and return -99999 when out of range is 1 which we can then use to recreate these out of range symbols in our front end web app:

SELECT s.Name, 
CASE WHEN ge.OutOfRangeCH4 = 1 
THEN -99999 
ELSE ge.CH4 
END AS 'CH4', 
CASE WHEN ge.OutOfRangeCO2 = 1 
THEN -99999 
ELSE ge.CO2 
END AS 'CO2', 
CASE WHEN ge.OutOfRangeO2 = 1 
THEN -99999
ELSE ge.O2 

FROM ....

We have noticed on a large data set that the performance of this is not very good. Is there a better way to do this?


  • Edited by DarrenOD 19 hours 41 minutes ago
February 19th, 2015 10:52am

Your ### values are an excel (or other spreadsheet) issue and not related to SQL.

You're probably going to get near best performance from that statement, and the issue is likely due to a bad, or missing index on the source table.

Get the estimated query plan, and look for hot spots.

Free Windows Admin Tool Kit Click here and download it now
February 19th, 2015 11:05am

Hi,

Maybe some small changes will help. You use string like 'CH4', maybe by giving it an int value it will differ.

I have changed you example a little bit, maybe it will help.

CREATE TABLE #T
(
ID INT, 
RANGECH4 INT,
RANGECO2 INT,
RANGEO2 INT
);
--DROP TABLE #T

INSERT INTO #T(ID, RANGECH4,RANGECO2,RANGEO2)
VALUES
(1,1,1,2),
(2,1,3,1),
(3,2,2,1);

SELECT ID, 
		CASE WHEN RANGECH4 <> 1 THEN 1
		ELSE -999999 END AS CH4,
		CASE WHEN RANGECO2 <> 1 THEN 1
		ELSE -999999 END AS CO2,
		CASE WHEN RANGEO2 <> 1 THEN 1
		ELSE -999999 END AS O2
FROM #T

Regards,

Reshma

February 19th, 2015 11:59am

Create a mapping table and try joining it to you to get return value instead and see if its any better
Free Windows Admin Tool Kit Click here and download it now
February 19th, 2015 12:22pm

You should be able to format the result from your front end based on the bit columns without the VASE expression in your select statement.
February 19th, 2015 1:39pm

Based only on the data you provided, the relatively straightforward CASE statement shouldn't impact performance in any noticable way. 

Something else that you haven't mentioned, perhaps that you thought was irrelevant or perhaps that you haven't noticed yet, is likely the cause.  For example, perhaps later on, you're doing something like "Where CH4 = -99999" or "where "Out of range flag = 1" and out-of-range flag is another computed/derived column (which wouldn't be sargable), or perhaps there's an inefficient view or query that feeds the table that GE. is based on.  The CASE statement alone isn't the full explanation.

Free Windows Admin Tool Kit Click here and download it now
February 19th, 2015 3:19pm

Thanks for all your comments. Before the CASE statement I am using a CTE to return the most recent records for each Site, which seems to be affecting performance.

Is it better to perform the conditional logic at the back end (e.g. stored procedure or view) and update the values to indicate out of range

OR

Should I just return the records I require including the out of range columns and check these flags in the front end web app?


February 20th, 2015 5:40am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics