SQL - 在 SELECT 中有多个 CASE 语句对性能有什么影响 - Teradata [英] SQL - What is the performance impact of having multiple CASE statements in SELECT - Teradata

问题描述

所以我有一个查询需要在 SELECT 中使用一堆 CASE 语句.这不是最初的设计，而是妥协的一部分.

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

我的问题是将所有这些 CASE 语句更改为直接列引用会对性能产生什么影响.

My question is what affect would changing all those CASE statements to direct column references have on performance.

换句话说:如果我将每个 CASE 语句更改为一个列名并从查询中删除所有 CASE 语句，会对性能产生很大影响吗?

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?

我正在对此进行测试，以便确定性能是否受到影响，但我对 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)

感谢您的帮助！

推荐答案

与 WHERE 子句中的连接相比，case 语句的影响要小得多.

The case statements are going to be much less of a factor than the joins in the WHERE clause.

SQL 性能的主要驱动因素是 I/O——从磁盘读取数据.我认为它比按行进行的处理重要两个数量级.这只是一种启发式方法，并非基于对数据库的特定测试.

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.

另一方面，case 语句变成了非常原始的硬件命令——equals、goto 等.数据驻留在最靠近处理器的内存中，因此它会被压缩.您在 case 语句中没有做任何花哨的事情(例如喜欢或子查询).我想如果您删除语句中的大部分行，查询也会同样快.

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.

如果您遇到性能问题，请在 (VERS_NM、RPT_PERD_TYPE_CD、DATA_VLDTN_IND、Perd_END_RPT_DT) 上添加索引.这个由四部分组成的索引应该允许您在不调用原始表上的 I/O 请求的情况下获取最大日期.

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.

这篇关于SQL - 在 SELECT 中有多个 CASE 语句对性能有什么影响 - Teradata的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！