多CASE WHEN语句导致TSQL查询性能下降

Hey there, let's fix that slow T-SQL query of yours! Those repeated subqueries inside CASE statements are the main culprit here—each one runs once for every row in your main query, which turns a simple scan into a performance nightmare. Here's how to optimize this step by step:

First, Understand the Problem #

Your original query uses correlated subqueries (the SELECT ... FROM CpseEventLogger inside each CASE). For every row returned by your main CpseProcessLogger subquery, SQL runs 10 separate queries against CpseEventLogger. If your main query returns 1000 rows, that's 10,000 extra table scans—no wonder it's slow!

Optimized Query: Pre-Aggregate & Join #

Instead of repeating subqueries, we'll pre-aggregate all the needed data from CpseEventLogger in one go, then join it to your main results. This way, we only scan CpseEventLogger once, not thousands of times.

Here's the rewritten query:

WITH MainQuery AS (
    SELECT 
        t1.TimeType, 
        t1.ServiceWaittime, 
        t1.Starttime, 
        t1.Endtime, 
        LAG(t1.Endtime) OVER (PARTITION BY t1.MainCpseId ORDER BY t1.Starttime ASC) AS prevEndTime, 
        t1.Duration, 
        t1.MainCpseId, 
        t1.Yield, 
        t1.Scrap, 
        t1.MachineYield, 
        t1.Te, 
        t1.Tr, 
        t1.CalendarWeek, 
        t1.InterruptionTriggerAttributeId, 
        t1.ReasonId, 
        t2.Name AS InterruptionName, 
        t3.Name AS ReasonName
    FROM CpseProcessLogger t1
    LEFT JOIN AttributeType t2 ON t1.InterruptionTriggerAttributeId = t2.Id
    LEFT JOIN Reason t3 ON t1.ReasonId = t3.Id
    WHERE TimeType IN ('UNT') AND MainCpseId = 12
)
SELECT 
    mq.ServiceWaittime, 
    mq.Starttime, 
    mq.Endtime, 
    mq.prevEndTime, 
    CONVERT(float, mq.Starttime - mq.prevEndTime) / 24.0 / 60.0 AS continuityDuration, 
    mq.Duration, 
    mq.MainCpseId, 
    mq.Yield, 
    mq.Scrap, 
    mq.MachineYield, 
    ISNULL((mq.MachineYield / NULLIF(mq.Duration, 0)), 0) AS Geschwindigkeit, 
    mq.Te, 
    mq.Tr, 
    mq.CalendarWeek, 
    mq.InterruptionTriggerAttributeId, 
    mq.ReasonId, 
    mq.InterruptionName, 
    mq.ReasonName,
    -- Use conditional aggregation instead of correlated subqueries
    ISNULL(AVG(CASE WHEN cel.AttributeId = 4 THEN cel.Value END), 0) AS Sys_Speed_Value,
    ISNULL(COUNT(CASE WHEN cel.AttributeId = 103 THEN 1 END), 0) AS attr_103,
    ISNULL(COUNT(CASE WHEN cel.AttributeId = 292 THEN 1 END), 0) AS attr_292,
    ISNULL(COUNT(CASE WHEN cel.AttributeId = 293 THEN 1 END), 0) AS attr_293,
    ISNULL(COUNT(CASE WHEN cel.AttributeId = 294 THEN 1 END), 0) AS attr_294,
    ISNULL(COUNT(CASE WHEN cel.AttributeId = 8159 THEN 1 END), 0) AS attr_8159,
    ISNULL(COUNT(CASE WHEN cel.AttributeId = 8175 THEN 1 END), 0) AS attr_8175,
    ISNULL(COUNT(CASE WHEN cel.AttributeId = 8186 THEN 1 END), 0) AS attr_8186,
    ISNULL(COUNT(CASE WHEN cel.AttributeId = 8208 THEN 1 END), 0) AS attr_8208,
    ISNULL(COUNT(CASE WHEN cel.AttributeId = 8209 THEN 1 END), 0) AS attr_8209
FROM MainQuery mq
LEFT JOIN CpseEventLogger cel 
    ON cel.MainCpseId = mq.MainCpseId
    AND cel.Timestamp BETWEEN mq.Starttime AND mq.Endtime
    AND cel.AttributeId IN (4, 103, 292, 293, 294, 8159, 8175, 8186, 8208, 8209) -- Filter only needed attributes early
GROUP BY 
    mq.ServiceWaittime, 
    mq.Starttime, 
    mq.Endtime, 
    mq.prevEndTime, 
    mq.Duration, 
    mq.MainCpseId, 
    mq.Yield, 
    mq.Scrap, 
    mq.MachineYield, 
    mq.Te, 
    mq.Tr, 
    mq.CalendarWeek, 
    mq.InterruptionTriggerAttributeId, 
    mq.ReasonId, 
    mq.InterruptionName, 
    mq.ReasonName
ORDER BY mq.Starttime ASC;

Key Improvements #

1. Single Scan of CpseEventLogger: We join the main query results to CpseEventLogger once, then use conditional aggregation (the CASE inside AVG/COUNT) to calculate all your required metrics in one pass.
2. Early Filtering: The AND cel.AttributeId IN (...) clause reduces the number of rows we process from CpseEventLogger right away.
3. Removed Redundant Code: The CASE WHEN 1=1 checks were completely unnecessary (they always evaluate to true) so we stripped them out to clean up the query.

Index Optimization (Critical for Speed) #

To make this query even faster, create a covering composite index on CpseEventLogger. This lets SQL retrieve all needed data directly from the index without hitting the main table:

CREATE NONCLUSTERED INDEX IX_CpseEventLogger_MainCpseId_AttributeId_Timestamp
ON CpseEventLogger (MainCpseId, AttributeId, Timestamp)
INCLUDE (Value);

• The leading columns (MainCpseId, AttributeId, Timestamp) let SQL quickly find the rows we need.
• The INCLUDE (Value) adds the Value column to the index, so we don't have to go back to the main table to calculate the average for AttributeId 4.

Bonus Tips #

• If your CpseProcessLogger table is large, check if it has an index on (MainCpseId, TimeType, Starttime) to speed up the main query.
• Test the query with execution plans (in SSMS, hit Ctrl+M before running) to confirm the index is being used and there are no unexpected bottlenecks.

内容的提问来源于stack exchange，提问作者Murtaza