SQL - Quel est l'impact des performances d'avoir plusieurs instructions CASE dans SELECT - Teradata

J'ai donc une requête qui nécessite un tas de CAS relevés dans le SELECT. Ce n'était pas à l'origine du design, mais d'un compromis.

Ainsi la requête ressemble à quelque chose comme ceci:

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'  )
)

Ma question est de savoir quel effet le fait de modifier tous ces CAS, les déclarations de diriger les références de colonne ont sur la performance.

En d'autres termes: Si j'ai changé tous les CAS, l'énoncé de juste un nom de colonne et supprimé tous les CAS consolidés à partir de la requête y aurait-il un impact important sur les performances et pourquoi?

Je suis en train de tester cela afin que je puisse déterminer si le rendement est touché, mais je suis tout aussi intéressé par les détails de POURQUOI? (Les détails techniques de pourquoi)

Merci pour votre aide!

source d'informationauteur tarheels058