嵌套CASE的T-SQL替代方案可以获得更好的性能吗？

I have a T-SQL query that is performing very poorly to the point that it times out. The culprits are these two nested CASE statements with embedded queries:

我有一个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

The CURRENT_BUSINESSDAY function does just what it describes..identifies the current business day of the reporting period.

CURRENT_BUSINESSDAY函数正如它所描述的那样...识别报告期间的当前工作日。

The logic of the CASE is to return goals values based on where we are in the reporting cycle and whether we've received an updated goals file. If it's not yet BD8, check to see if we've received the new file (by comparing max dates). If we have received it, return that value to the report, otherwise return the prior month's value. If it's after BD8 and we still don't have the new file, it should return "0" which will fail our process and let us know that they haven't provided the data on time.

CASE的逻辑是根据我们在报告周期中的位置以及我们是否收到更新的目标文件来返回目标值。如果它还不是BD8,请检查我们是否收到了新文件(通过比较最大日期)。如果我们已收到它,请将该值返回到报表,否则返回上个月的值。如果它在BD8之后并且我们仍然没有新文件,它应该返回“0”,这将使我们的过程失败并让我们知道他们没有及时提供数据。

Is there a more efficient way to script this logic that will keep the query from timing out? Keep in mind that this script is being used to build a table in a tabular model, so only SELECT is in play...no variable declarations or anything of that sort.

是否有一种更有效的方法来编写此逻辑,以防止查询超时?请记住,此脚本用于在表格模型中构建表,因此只有SELECT正在运行...没有变量声明或任何类型。

Thoughts?

1 个解决方案

#1

Since the case logic is the same in both COLLECTION_GOALS and BILLINGS_BUDGET, I suggest moving the logic out of the inline subqueries and into the main FROM clause, like so:

由于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 */

This should result in it being evaluated once per query, rather than twice for every row returned by the query.

这应该导致每个查询对其进行一次评估,而不是对查询返回的每一行进行两次评估。

#1