How to optimioze the Case statement for a column. Please help

Question

CREATE TABLE MYCheck1Temp1 ( NDS VARCHAR(20), OPTCODE VARCHAR(20), OPTCODEDESC varchar(20), CODECHECK Varchar(20), SRCCODE VARCHAR(20), VALID_RECORD VARCHAR(1))

SELECT DISTINCT 'MILLER' AS NDS
, TRIM(STG.OPTCODE) AS SRC_CD
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''
AND TRIM(STG.CODECHECK) = 'MILT'

UNION ALL

SELECT DISTINCT 'ROMAN BOWLER' AS NDS
, TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''
AND TRIM(STG.CODECHECK) = 'ROMB'

UNION ALL

SELECT DISTINCT 'LOANER' AS NDS
, TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''
AND TRIM(STG.CODECHECK) = 'BANK'

UNION ALL

SELECT DISTINCT 'MOVABLES' AS NDS
, TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''
AND TRIM(STG.CODECHECK) = 'MORT'

like this I have 150 UNION ALLS referring the same table causing much time to execte. here i am trying to optimize this code..

first thing i did was

STEP1)
created a temp table that trims and loads all data (with upper, trims) in one shot rather at every query.

CREATE TABLE #abc AS
SELECT DISTINCT
TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, TRIM(STG.CODECHECK) AS CODECHECK
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''

STEP2)
here i would like to include a where clause for all the CODECHECK COLUMNS but the question here is how to make the NDS column depend on the CODECHECK column
which means below query i can make up NDS with CASE STATEMENT (about 150) is there anyotherway to do this NDS column with other temp tables using joins etc (or any other Idea)? to optimize this PLEASE...

SELECT DISTINCT
CASE WHEN CODECHECK = 'MORT' THEN 'MOVABLES'
WHEN CODECHECK = 'BANK' THEN 'LOANER'
WHEN CODECHECK = 'ROMB' THEN 'ROMAN BOWLER'
WHEN CODECHECK = 'MILT' THEN 'MILLER'
ELSE NULL END AS NDS
, TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, TRIM(STG.CODECHECK) AS CODECHECK
FROM MYCheck1Temp1 STG
WHERE CODECHECK IN
(
'MORT',
'BANK',
'ROMB',
'MILT'
)

please help me out here to optimze it.. Thanks in advance.

Answer 1

Check is this fits your need:

SELECT DISTINCT
 NDS = CASE
 WHEN TRIM(STG.CODECHECK) = 'MILT' THEN 'MILLER'
 WHEN TRIM(STG.CODECHECK) = 'ROMB' THEN 'ROMAN BOWLER'
 WHEN TRIM(STG.CODECHECK) = 'BANK' THEN 'LOANER'
 WHEN TRIM(STG.CODECHECK) = 'MORT' THEN 'MOVABLES'
 -- Add more condition as needed here for other cases
 END
 , TRIM(STG.OPTCODE) AS SRC_CD
 , UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
 , TRIM(STG.OPTCODE) AS CON_CODE
 , UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
 , 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> '' 
AND TRIM(STG.CODECHECK) in ('MILT', 'ROMB', 'BANK', 'MORT') -- add more option in the IN condition as needed

Note! VERY IMPORTANT! You should have the right indexes according to the filter conditions but using condition on TRIM(STG.CODECHECK) in the where part might reduce the performance dramatically since the server might not be able to use the index on STG.CODECHECK. It is better to store the data after it is trimmed if this fit your case.

Check this sample and why it is not a good idea to use filter on a column that you manipulate

create table T(id int identity(2,2), txt nvarchar(100))
insert T(txt) values (' ronen '),('ronen'),(' ronen')

CREATE CLUSTERED INDEX i1 ON T (id);
CREATE nonCLUSTERED INDEX i2 ON T (txt);

-- this scan all the index since the server must first execute TRIM on the value before it can filter by it
select txt from T where TRIM(txt) = 'ronen' 
-- this use the index seek which is very useful, since here the server could filter according to the index
select txt from T where txt = 'ronen' 

Answer 2

Thank you Much pitauch.

I appreciate your valuable input. I am just checking is there any way that we can do with temp table of CODECHECKs

for example Create table #codecheck (CodeCheck varchar(4), CodeCheckDesc varchar(20))

insert into #codecheck values ('MILT','MILLER')
insert into #codecheck values ('ROMB','ROMAN BOWLER')
insert into #codecheck values ('BANK','LOANER')
insert into #codecheck values ('MORT','MOVABLES')

then use this table to join the main table above based on CODECHECK column and use the Codecheckdesc column in select clause?

Please advise your valuable inputs

Thanks Again
Asita

Answer 3

Hi @aswaniD-8992n

Welcome to the Microsoft TSQL Q&A Forum!

If it is to improve performance, you can try to use union all to split the case statement. But this will make the code look complicated.Or replace the case statement with or.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Thank you EchoLiu, Can you please share example using with or as you mentioned?please

Answer 5

1) CASE is an expression, not a statement.

2) You create a table ( CodeCheck, NDS ), then you can JOIN it with your table. This replaces the WHERE condition as well as the CASE expression.

3) Why those all those TRIM calls? I would think that non of those columns needs that. But when it is necessary, then why not updating the table before running your tests?