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

pinkon5k 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(83)

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?

sql-server

来源：https://stackoverflow.com/questions/77489311/how-can-i-decrease-the-execution-cost-of-my-case-expressions

1条答案

按热度按时间

8tntrjer1#

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

赞(0）回复(0）举报 2023-11-16

我来回答

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

1条答案

相关问题

热门标签

最新问答