T-SQL альтернативы вложенному CASE для лучшей производительности?

Question

У меня есть T-SQL запрос, который выполняется очень плохо до такой степени, что время ожидания истекло. Виновными являются два вложенных оператора CASE со встроенными запросами:

SELECT
  CASE
    WHEN b.month_type = (CASE
        WHEN dbo.CURRENT_BUSINESSDAY(GETDATE()) >= 8 THEN 'Current Month BD2'
        ELSE (CASE
            WHEN dbo.CURRENT_BUSINESSDAY(GETDATE()) < 8 AND
              (SELECT
                MAX(b.cal_start_date)
              FROM factbillingcollectionhistory a
              JOIN dimdateperiod b
                ON a.fiscal_month = b.fsc_period)
              <> (SELECT
                MAX(cal_start_date)
              FROM dimdateperiod) THEN 'Current Reporting Month'
            ELSE 'Current Month BD2'
          END)
      END) THEN a.BILLINGS_BUDGET
    ELSE 0
  END
  AS BILLINGS_BUDGET,
  CASE
    WHEN b.month_type = (CASE
        WHEN dbo.CURRENT_BUSINESSDAY(GETDATE()) >= 8 THEN 'Current Month BD2'
        ELSE (CASE
            WHEN dbo.CURRENT_BUSINESSDAY(GETDATE()) < 8 AND
              (SELECT
                MAX(b.cal_start_date)
              FROM factbillingcollectionhistory a
              JOIN dimdateperiod b
                ON a.fiscal_month = b.fsc_period)
              <> (SELECT
                MAX(cal_start_date)
              FROM dimdateperiod) THEN 'Current Reporting Month'
            ELSE 'Current Month BD2'
          END)
      END) THEN a.COLLECTION_GOALS
    ELSE 0
  END
  AS COLLECTION_GOALS

CURRENT_BUSINESSDAY Функция выполняет только то, что она описывает. Определяет текущий рабочий день отчетного периода.

Логика CASE возвращает значения целей в зависимости от того, где мы находимся в цикле отчетности и получили ли мы обновленный файл целей. Если это еще не BD8, проверьте, получили ли мы новый файл (сравнив максимальные даты). Если мы его получили, верните это значение в отчет, в противном случае верните значение предыдущего месяца. Если это после BD8, и у нас все еще нет нового файла, он должен возвратить "0", что приведет к сбою в нашем процессе и даст нам знать, что они не предоставили данные вовремя.

Есть ли более эффективный способ написания этой логики, который не позволит тайм-ауту запроса? Имейте в виду, что этот скрипт используется для построения таблицы в табличной модели, поэтому в игру играет только SELECT... никаких объявлений переменных или чего-либо подобного.

Мысли?

Answer 1

Поскольку логика регистра в COLLECTION_GOALS и BILLINGS_BUDGET одинакова, я предлагаю переместить логику из встроенных подзапросов в основное предложение FROM, например, так:

SELECT CASE WHEN b.month_type = z.month_type
           THEN a.BILLINGS_BUDGET
           ELSE 0
       END AS BILLINGS_BUDGET,
       CASE WHEN b.month_type = z.month_type
           THEN a.COLLECTION_GOALS
           ELSE 0
       END AS COLLECTION_GOALS
FROM (SELECT CASE WHEN dbo.CURRENT_BUSINESSDAY(GETDATE()) >= 8
                 THEN 'Current Month BD2'
                 ELSE (CASE WHEN dbo.CURRENT_BUSINESSDAY(GETDATE()) < 8 AND 
                                 (SELECT max(b.cal_start_date) 
                                         FROM factbillingcollectionhistory a
                                         JOIN dimdateperiod b
                                           ON a.fiscal_month = b.fsc_period) <> (SELECT max(cal_start_date) FROM dimdateperiod)
                           THEN 'Current Reporting Month'
                           ELSE 'Current Month BD2'
                       END)
             END month_type) z
CROSS JOIN
/*... Rest of query */

Это должно привести к тому, что он будет оцениваться один раз для каждого запроса, а не дважды для каждой строки, возвращаемой запросом.