SQL - What is the performance impact of having multiple CASE statements in SELECT - Teradata
So I have a query that requires a bunch of CASE statements in the SELECT. This was not the orginal design but part of a compromise.
So the query looks something like this:
SELECT
CONT.TABLE.FINC_ACCT_NM,
CONT.TABLE.FINC_ACCT_ID,
CONT.TABLE.CURR_END_OF_PERD_ACTL_VAL,
CONT.TABLE.PREV_END_OF_PERD_ACTL_VAL,
CONT.TABLE.VARNC_PLAN_VAL,
CONT.TABLE.OUTLOOK_BDGT_PLAN_VAL,
CONT.TABLE.PERD_END_RPT_DT,
CONT.TABLE.PLAN_VERS_NM,
CONT.TABLE.FRMT_ACTL_CD,
CONT.TABLE.FRMT_PLAN_CD,
CONT.TABLE.RPT_PERD_TYPE_CD,
CASE
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Net Interest Income'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Non Interest Income'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Non-Interest Expense'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Total Marketing Expense'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Total Operating Expense'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Pre-Provision Earnings (before tax)'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Net Charge-offs'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Other'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Allowance Build (Release)'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Provision Expense'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Pretax Income'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Tax Expense'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'NIAT'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'EPS'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Ending Loans - HFI'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'avg' then 'Average Loans - HFI'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'avg' then 'Average Earning Assets'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Ending Deposits'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'avg' then 'Average Deposits'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'NIM on Loans'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Revenue Margin'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'AC579' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Charge off rate'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Efficiency ratio'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'ROA'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'ROE'
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Return on Allocated Capital (ROAC)'
ELSE ( CONT.TABLE.FINC_ACCT_NM ) end
FROM
CONT.TABLE
WHERE
(
(
( ( CONT.TABLE.PERD_END_RPT_DT ) = (
SELECT Max(Perd_END_RPT_DT)
FROM CONT.TABLE
Where VERS_NM='Actual'
AND RPT_PERD_TYPE_CD = 'Q'
AND DATA_VLDTN_IND='Y'
)
AND RPT_PERD_TYPE_CD = 'Q'
AND DATA_VLDTN_IND='Y' )
OR
( ( CONT.TABLE.PERD_END_RPT_DT ) = (
SELECT Max(Perd_END_RPT_DT)
FROM CONT.TABLE
Where VERS_NM='Actual'
AND RPT_PERD_TYPE_CD = 'M'
AND DATA_VLDTN_IND='Y'
)
AND RPT_PERD_TYPE_CD = 'M'
AND DATA_VLDTN_IND='Y' )
)
AND
( ( CONT.TABLE.DATA_VLDTN_IND )='Y' )
AND
( ( CONT.TABLE.FINC_ACCT_ID )IN ('AC0006470','AC8000199','AC8002145','AC0006586','AC8000094') AND ( CONT.TABLE.DEPT_ID )='OR80637' )
)
My question is what affect would changing all those CASE statements to direct column references have on performance.
In other words: If I changed every CASE statement to just a column name and removed all CASE statements from the query would there be a large impact on performance and why?
I am testing this out so I can figure out if performance is affected but I am just as interested in the details of WHY? (Technical details of why)
Thanks for your help!
Answer
The case statements are going to be much less of a factor than the joins in the WHERE clause.
The main driver of performance in SQL is I/O -- reading the data from disk. I think of it as two orders of magnitude more important than the processing going on in rows. This is just a heuristic, not based on specific tests on a database.
You are doing self-joins, which will require either lots of work reading the table or a fair amount of work dealing with indexes.
The case statement, on the other hand, gets turned into very primitive hardware commands -- equals, gotos, and the like. The data resides in memory closest to the processors, so it is going to zip along. You are doing nothing fancy in the case statement (such as a like or a subquery). I would imagine that the query would be just as fast if you removed most of the lines in the statement.
If you are having issues with performance, put an index on (VERS_NM, RPT_PERD_TYPE_CD, DATA_VLDTN_IND, Perd_END_RPT_DT). This four-part index should allow you to get the max date without invoking I/O requests on the original table.