CODEWITHSUNDEEP
sqlsql-servert-sql
3355307
3355307

Reputation: 1678

how to replace T-SQL CASE statement with efficient code

how can i replace the following case statement with efficient code

SELECT 
CASE WHEN LEN(Code.Description)>=30
    THEN left(Code.Description, 30) + '...' 
    ELSE NominalCode.Description END AS ShortDescription
FROM Code

Because the data set it returns is going to be 30-50,000 records and according to lead dev this code is ridiculous. I need help

Upvotes: 0

Views: 153

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

Other than adding a ShortDesc field to your Product Table, perhaps I would try the following

Concat(left(Code.Description,30),IIF(Len(Code.Description)>30,'...',''))

or even

left(Code.Description,30)+IIF(Len(Code.Description)>30,'...','')

EDIT

As you can see the same execution plan, however, the performance of my approach was 18% better. This test was done on an isolated machine with a sample size of 30,000 records.

<code>enter image description here</code>

enter image description here

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Presumably, you intend:

SELECT (CASE WHEN LEN(c.Description) > 30
             THEN left(c.Description, 30) + '...' 
             ELSE c.Description
        END) AS ShortDescription
FROM Code c;

That is, the Description column references all come from the same table. Your code is fine.

However, I would adjust the semantics so the resulting string always has the same length:

SELECT (CASE WHEN LEN(c.Description) > 30
             THEN LEFT(c.Description, 27) + '...' 
             ELSE c.Description
        END) AS ShortDescription
FROM Code c;

Upvotes: 1

Related Questions