How can I decrease the execution cost of my CASE expressions?

Question

I have this CASE expression in my query:

    CASE el.faunaType
        WHEN 2 THEN 'Examine "' + ISNULL((SELECT TOP 1 cellComplex FROM cellList WHERE TRY_CONVERT(NVARCHAR(50), cellID) = TRY_CONVERT(NVARCHAR(50), faunaSource)), '') + '"'
        WHEN 9 THEN 'Investigate "' + (SELECT TOP 1 sourceText FROM diseaseSources WHERE TRY_CONVERT(NVARCHAR(50), sourceID) = TRY_CONVERT(NVARCHAR(50), faunaSource)) + '"'
        WHEN 15 THEN 'Infection detected "' + (SELECT TOP 1 sourceText FROM diseaseSources WHERE TRY_CONVERT(NVARCHAR(50), sourceID) = TRY_CONVERT(NVARCHAR(50), faunaSource)) + '"'
        WHEN 23 THEN 'Forward on "' + (SELECT TOP 1 cellComplex FROM cellList WHERE TRY_CONVERT(NVARCHAR(50), cellID) = TRY_CONVERT(NVARCHAR(50), faunaSource)) + '"'
        WHEN 45 THEN 'Traced cell "' + ISNULL((SELECT TOP 1 cellComplex FROM cellList WHERE TRY_CONVERT(NVARCHAR(50), cellID) = TRY_CONVERT(NVARCHAR(50), faunaSource)), '') + '"'
        WHEN 77 THEN 'Sampled on "' + (SELECT TOP 1 cellComplex FROM cellList WHERE TRY_CONVERT(NVARCHAR(50), cellID) = TRY_CONVERT(NVARCHAR(50), faunaSource)) + '"<br />[' + faunaMessage + ']'
    END As FaunaName

This block is causing a huge performance hit. When I view the Execution Plan, I see 20% costs on both diseaseSources and cellList.

It is doing a Clustered Index Scan.

I have the ID(cellID and sourceID) of both of those tables as Primary Keys.

Is there a way to decrease the performance cost of this CASE statement?

Answer 1

It's a bit of a guess at your table structures to generate HTML, but you'd be better off joining once and then changing the way you handle the SQL. In your example you'd be doing a unique select statement for each case, while a join does the matching once and handles the display after.

DECLARE @FT TABLE(faunaType NVARCHAR(100),faunaSource NVARCHAR(50),faunaMessage NVARCHAR(100))
DECLARE @CL TABLE(cellID NVARCHAR(50),cellComplex NVARCHAR(100))
DECLARE @DS TABLE(sourceID NVARCHAR(50),sourceText NVARCHAR(100))

SELECT *,
    CASE el.faunaType
        WHEN 2 THEN 'Examine "' + ISNULL((SELECT TOP 1 cellComplex FROM @CL WHERE TRY_CONVERT(NVARCHAR(50), cellID) = TRY_CONVERT(NVARCHAR(50), faunaSource)), '') + '"'
        WHEN 9 THEN 'Investigate "' + (SELECT TOP 1 sourceText FROM @DS WHERE TRY_CONVERT(NVARCHAR(50), sourceID) = TRY_CONVERT(NVARCHAR(50), faunaSource)) + '"'
        WHEN 15 THEN 'Infection detected "' + (SELECT TOP 1 sourceText FROM @DS WHERE TRY_CONVERT(NVARCHAR(50), sourceID) = TRY_CONVERT(NVARCHAR(50), faunaSource)) + '"'
        WHEN 23 THEN 'Forward on "' + (SELECT TOP 1 cellComplex FROM @CL WHERE TRY_CONVERT(NVARCHAR(50), cellID) = TRY_CONVERT(NVARCHAR(50), faunaSource)) + '"'
        WHEN 45 THEN 'Traced cell "' + ISNULL((SELECT TOP 1 cellComplex FROM @CL WHERE TRY_CONVERT(NVARCHAR(50), cellID) = TRY_CONVERT(NVARCHAR(50), faunaSource)), '') + '"'
        WHEN 77 THEN 'Sampled on "' + (SELECT TOP 1 cellComplex FROM @CL WHERE TRY_CONVERT(NVARCHAR(50), cellID) = TRY_CONVERT(NVARCHAR(50), faunaSource)) + '"<br />[' + faunaMessage + ']'
    END As FaunaName
FROM @FT el

SELECT *,
    CASE el.faunaType
        WHEN 2 THEN 'Examine "' + ISNULL(cellComplex, '') + '"'
        WHEN 9 THEN 'Investigate "' + ISNULL(sourceText, '') + '"'
        WHEN 15 THEN 'Infection detected "' + ISNULL(sourceText, '') + '"'
        WHEN 23 THEN 'Forward on "' + ISNULL(cellComplex, '') + '"'
        WHEN 45 THEN 'Traced cell "' + ISNULL(cellComplex, '') + '"'
        WHEN 77 THEN 'Sampled on "' + ISNULL(cellComplex, '') + '"<br />[' + faunaMessage + ']'
    END As FaunaName
FROM @FT el 
LEFT JOIN @DS ds ON ds.sourceID=el.faunaSource
LEFT JOIN @CL cl ON cl.cellID=el.faunaSource