T-SQL alternatives to nested CASE for better performance?

Question

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:

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.

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.

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.

Thoughts?

Answer 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:

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.