I have the following query with multiple case-whens for each respective 'AttributeId' column from another table which gets aggregated. Adding these statements increases the time-complexity significantly. I would appreciate any suggestions for improving the performance as I am not so well-versed with TSQL.
The query:
SELECT t1.ServiceWaittime,
t1.Starttime,
t1.Endtime,
t1.prevEndTime,
CONVERT(float, t1.Starttime - t1.prevEndTime) / 24.0 / 60.0 AS continuityDuration,
t1.Duration,
t1.MainCpseId,
t1.Yield,
t1.Scrap,
t1.MachineYield,
ISNULL((t1.MachineYield / NULLIF(t1.Duration, 0)), 0) AS Geschwindigkeit,
t1.Te,
t1.Tr,
t1.CalendarWeek,
t1.InterruptionTriggerAttributeId,
t1.ReasonId,
t1.InterruptionName,
t1.ReasonName,
CASE
WHEN 1 = 1 THEN ISNULL((SELECT AVG(Value)
FROM CpseEventLogger
WHERE AttributeId = 4
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS Sys_Speed_Value, --statement1
CASE
WHEN 1 = 1 THEN ISNULL((SELECT COUNT(*)
FROM CpseEventLogger
WHERE AttributeId = 103
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS attr_103, --statement2
CASE
WHEN 1 = 1 THEN ISNULL((SELECT COUNT(*)
FROM CpseEventLogger
WHERE AttributeId = 292
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS attr_292, --statement3
CASE
WHEN 1 = 1 THEN ISNULL((SELECT COUNT(*)
FROM CpseEventLogger
WHERE AttributeId = 293
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS attr_293, --statement4
CASE
WHEN 1 = 1 THEN ISNULL((SELECT COUNT(*)
FROM CpseEventLogger
WHERE AttributeId = 294
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS attr_294, --statement5
CASE
WHEN 1 = 1 THEN ISNULL((SELECT COUNT(*)
FROM CpseEventLogger
WHERE AttributeId = 8159
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS attr_8159, --statement6
CASE
WHEN 1 = 1 THEN ISNULL((SELECT COUNT(*)
FROM CpseEventLogger
WHERE AttributeId = 8175
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS attr_8175, --statement7
CASE
WHEN 1 = 1 THEN ISNULL((SELECT COUNT(*)
FROM CpseEventLogger
WHERE AttributeId = 8186
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS attr_8186, --statement8
CASE
WHEN 1 = 1 THEN ISNULL((SELECT COUNT(*)
FROM CpseEventLogger
WHERE AttributeId = 8208
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS attr_8208, --statement9
CASE
WHEN 1 = 1 THEN ISNULL((SELECT COUNT(*)
FROM CpseEventLogger
WHERE AttributeId = 8209
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS attr_8209 --statement10
FROM (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 1 = 1
AND TimeType IN ('UNT')
AND MainCpseId = 12) t1
ORDER BY t1.Starttime ASC;
CodePudding user response：
Another option, which may be easier in your case, is to use APPLY. (OUTER APPLY is similar to LEFT JOIN and CROSS APPLY is similar to INNER JOIN).
You can use conditional aggregation within the subquery.
SELECT t1.ServiceWaittime,
t1.Starttime,
t1.Endtime,
t1.prevEndTime,
CONVERT(float, t1.Starttime - t1.prevEndTime) / 24.0 / 60.0 AS continuityDuration,
t1.Duration,
t1.MainCpseId,
t1.Yield,
t1.Scrap,
t1.MachineYield,
ISNULL((t1.MachineYield / NULLIF(t1.Duration, 0)), 0) AS Geschwindigkeit,
t1.Te,
t1.Tr,
t1.CalendarWeek,
t1.InterruptionTriggerAttributeId,
t1.ReasonId,
t1.InterruptionName,
t1.ReasonName,
el.Sys_Speed_Value, --statement1
el.attr_103, --statement2
el.attr_292, --statement3
el.attr_293, --statement4
el.attr_294, --statement5
el.attr_8159, --statement6
el.attr_8175, --statement7
el.attr_8186, --statement8
el.attr_8208, --statement9
el.attr_8209 --statement10
FROM (
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 1 = 1
AND TimeType IN ('UNT')
AND MainCpseId = 12
) t1
CROSS APPLY (
SELECT
ISNULL(AVG(CASE WHEN AttributeId = 4 THEN Value END), 0) AS Sys_Speed_Value,
COUNT(CASE WHEN AttributeId = 103 THEN 1 END) AS attr_103,
COUNT(CASE WHEN AttributeId = 292 THEN 1 END) AS attr_292,
COUNT(CASE WHEN AttributeId = 293 THEN 1 END) AS attr_293,
COUNT(CASE WHEN AttributeId = 294 THEN 1 END) AS attr_294,
COUNT(CASE WHEN AttributeId = 8159 THEN 1 END) AS attr_8159,
COUNT(CASE WHEN AttributeId = 8175 THEN 1 END) AS attr_8175,
COUNT(CASE WHEN AttributeId = 8186 THEN 1 END) AS attr_8186,
COUNT(CASE WHEN AttributeId = 8208 THEN 1 END) AS attr_8208,
COUNT(CASE WHEN AttributeId = 8209 THEN 1 END) AS attr_8209
FROM CpseEventLogger
WHERE MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime
) el
ORDER BY t1.Starttime ASC;
For this query to perform well, you want the following indexes:
CpseEventLogger (MainCpseId, Timestamp) INCLUDE (AttributeId, Value)
CpseProcessLogger (MainCpseId, TimeType, InterruptionTriggerAttributeId) INCLUDE (
ReasonId, .... othercolumns)
AttributeType (Id) INCLUDE (Name)
Reason (Id) INCLUDE (Name)
Or instead of a non-clustered index with INCLUDE columns, you could use a clustered index (all non-key columns are INCLUDE).
You may want to swap around InterruptionTriggerAttributeId and ReasonId positions in the index
CodePudding user response：
Taking a guess that the slow down is reading the CpseEventLogger table as @larnu suggested, you could LEFT OUTER JOIN to that table and perform the aggregation once in the main query. That would look something like:
SELECT t1.ServiceWaittime,
t1.Starttime,
t1.Endtime,
t1.prevEndTime,
CONVERT(float, t1.Starttime - t1.prevEndTime) / 24.0 / 60.0 AS continuityDuration,
t1.Duration,
t1.MainCpseId,
t1.Yield,
t1.Scrap,
t1.MachineYield,
ISNULL((t1.MachineYield / NULLIF(t1.Duration, 0)), 0) AS Geschwindigkeit,
t1.Te,
t1.Tr,
t1.CalendarWeek,
t1.InterruptionTriggerAttributeId,
t1.ReasonId,
t1.InterruptionName,
t1.ReasonName,
Avg(CASE WHEN CpseEventLogger.AttributeId = 4 THEN CpseEventLogger.Value) AS Sys_Speed_Value,
COUNT(CASE WHEN CpseEventLogger.AttributeId = 103 THENCpseEventLogger.Value) AS attr_103, --statement2
COUNT(CASE WHEN CpseEventLogger.AttributeId = 292 THENCpseEventLogger.Value) AS attr_292, --statement3
COUNT(CASE WHEN CpseEventLogger.AttributeId = 293 THENCpseEventLogger.Value) AS attr_293, --statement4
COUNT(CASE WHEN CpseEventLogger.AttributeId = 294 THENCpseEventLogger.Value) AS attr_294, --statement5
COUNT(CASE WHEN CpseEventLogger.AttributeId = 8159 THENCpseEventLogger.Value) AS attr_8159, --statement6
COUNT(CASE WHEN CpseEventLogger.AttributeId = 8175 THENCpseEventLogger.Value) AS attr_8175, --statement7
COUNT(CASE WHEN CpseEventLogger.AttributeId = 8186 THENCpseEventLogger.Value) AS attr_8186, --statement8
COUNT(CASE WHEN CpseEventLogger.AttributeId = 8208 THENCpseEventLogger.Value) AS attr_8208, --statement9
COUNT(CASE WHEN CpseEventLogger.AttributeId = 8209 THENCpseEventLogger.Value) AS attr_8209 --statement10
FROM (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 1 = 1
AND TimeType IN ('UNT')
AND MainCpseId = 12) t1
LEFT OUTER JOIN CpseEventLogger
ON CpseEventLogger.MainCpseId = 12
AND CpseEvevntLogger.AttributeId IN (4,103,292,293,294,8159,8175,8186,8208,8209)
AND CpseEventLogger.Timestamp BETWEEN t1.Starttime AND t1.Endtime
GROUP BY
t1.ServiceWaittime,
t1.Starttime,
t1.Endtime,
t1.prevEndTime,
continuityDuration,
t1.Duration,
t1.MainCpseId,
t1.Yield,
t1.Scrap,
t1.MachineYield,
Geschwindigkeit,
t1.Te,
t1.Tr,
t1.CalendarWeek,
t1.InterruptionTriggerAttributeId,
t1.ReasonId,
t1.InterruptionName,
t1.ReasonName,
ORDER BY t1.Starttime ASC;
Regarding my comment on superfluous use of a case expression, your original query had the form:
CASE
WHEN 1 = 1 THEN ISNULL((SELECT AVG(Value)
FROM CpseEventLogger
WHERE AttributeId = 4
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)
END AS Sys_Speed_Value, --statement1
This is odd since we use case expression to only execute code/logic when a condition is true. Your condition here is 1=1 which is always true. There is only the when and no else so this is 100% identical to just:
ISNULL((SELECT AVG(Value)
FROM CpseEventLogger
WHERE AttributeId = 4
AND MainCpseId = 12
AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
0)