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

Jennis 21 Reputation points
2021-02-09T23:33:17.327+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
{count} votes

7 answers

Sort by: Most helpful
  1. Ronen Ariely 15,101 Reputation points
    2021-02-10T00:39:07.38+00:00

    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' 
    
    0 comments No comments

  2. Jennis 21 Reputation points
    2021-02-10T02:43:34.847+00:00

    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


  3. EchoLiu-MSFT 14,576 Reputation points
    2021-02-10T07:04:01.687+00:00

    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.


  4. Jennis 21 Reputation points
    2021-02-10T11:04:43.413+00:00

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

    0 comments No comments

  5. Stefan Hoffmann 621 Reputation points
    2021-02-10T12:23:04.37+00:00

    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?