Множественный случай Когда замедляется выполнение запроса

У меня есть следующий запрос с несколькими случаями для каждого соответствующего столбца «AttributeId» из другой таблицы, которая агрегируется. Добавление этих операторов значительно увеличивает временную сложность. Я был бы признателен за любые предложения по улучшению производительности, поскольку я не так хорошо разбираюсь в TSQL.

Запрос:

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;

Вредные привычки, от которых следует избавиться: использование псевдонимов таблиц, таких как (a, b, c) или (t1, t2, t3) Вышеприведенное особенно сбивает с толку, когда t1 ссылается на объект CpseProcessLogger (в имени которого нет ни одного t или 1) и производная таблица во внешнем FROM

— 09.05.2022 15:37

Что касается того, почему ваш запрос медленный, вы ссылаетесь на объект CpseEventLogger в общей сложности 11 раз выше; что вряд ли будет не дорого.

— 09.05.2022 15:38

Возможно, это шаблон tsql, о котором я не знаю, но почему вы вообще используете здесь выражения case. Условие всегда истинно и кажется излишним.

— 09.05.2022 15:44

@JNeville, мне нужны отдельные столбцы для каждого атрибута. Однако есть лучший способ, о котором я не знаю, не могли бы вы предоставить образец ответа?

— 09.05.2022 15:47

Я подозреваю, что вам нужна сводная/условная агрегация, @MurtazaBasu.

— 09.05.2022 15:51

Выражения CASE останавливаются после первого совпадения, поэтому, если у вас есть WHEN 1=1 в качестве первого условия, вы получите этот результат всегда, и вообще нет смысла использовать выражение CASE.

— 09.05.2022 16:06

sql sql-server

09.05.2022 15:31

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Предполагая, что замедление связано с чтением таблицы CpseEventLogger, как предложил @larnu, вы можете ОСТАВИТЬ ВНЕШНЕЕ СОЕДИНЕНИЕ с этой таблицей и выполнить агрегацию один раз в основном запросе. Это будет выглядеть примерно так:

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;

Что касается моего комментария о избыточном использовании выражения case, ваш исходный запрос имел вид:

   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 для выполнения кода/логики только тогда, когда условие равно true. Ваше условие здесь 1=1, что всегда верно. Есть только when и нет else, так что это на 100% идентично просто:

   ISNULL((SELECT AVG(Value)
           FROM CpseEventLogger
           WHERE AttributeId = 4
                 AND MainCpseId = 12
                 AND Timestamp BETWEEN t1.Starttime AND t1.Endtime),
           0)

09.05.2022 15:57

Ответ принят как подходящий

Другой вариант, который может быть проще в вашем случае, — использовать APPLY. (OUTER APPLY похож на LEFT JOIN и CROSS APPLY похож на INNER JOIN).

Вы можете использовать условную агрегацию внутри подзапроса.

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;

Чтобы этот запрос работал хорошо, вам нужны следующие индексы:

CpseEventLogger (MainCpseId, Timestamp) INCLUDE (AttributeId, Value)
CpseProcessLogger (MainCpseId, TimeType, InterruptionTriggerAttributeId) INCLUDE (
  ReasonId, .... othercolumns)
AttributeType (Id) INCLUDE (Name)
Reason (Id) INCLUDE (Name)

Или вместо некластеризованного индекса со столбцами INCLUDE вы можете использовать кластеризованный индекс (все неключевые столбцы — INCLUDE).

Вы можете поменять местами InterruptionTriggerAttributeId и ReasonId позиции в индексе.

Спасибо, что показали мне работу с Cross Apply. Действительно мощный и именно то, что я искал.

— 09.05.2022 16:12

Также добавлены детали индексации

— 09.05.2022 16:35

09.05.2022 16:03