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?

I guess that the scalar valued function is the reason, do you really need `CURRENT_BUSINESSDAY`? A function is a black box that needs to be ran once for every row, and cannot be optimized by the query plan engine. Do you have a `WHERE` which filters records out? — Tim Schmelter, May 13 '15 at 15:17
Agree with @TimSchmelter 100%. Nested case expressions are not a performance issue but scalar functions are. — Sean Lange, May 13 '15 at 15:24
I don't know if SQL Server will actually evaluate all those current_businessday calls for each and every row or not, since they're all for the current date. Not sure if I understand why you can't use a variable, but I would try putting the result of the function in a variable, and replacing all the calls to the function with that variable. If that solves the performance issue, then at least we know what the issue is. — Andrew, May 13 '15 at 15:24
Curiously GetDate() is treated as a [runtime constant function](http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/04/23/conor-vs-runtime-constant-functions.aspx) within a query. ([Ref.](http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx)) It is often clearer to capture the current date/time, or `Current_BusinessDay( GetDate() )` in this case, in a variable and then use that value as needed. This is more important across multiple statements, as in a stored procedure, where the value may change from one statement to the next. — HABO, May 13 '15 at 15:39

score 1 · Accepted Answer · answered May 13 '15 at 15:41

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.

T-SQL alternatives to nested CASE for better performance?

1 Answers1