|
|
所以我有一个查询，在SELECT中需要一堆CASE语句。这不是原始的设计，而是折衷的一部分。
( V% | O+ N m% q因此查询看起来像这样：
* F g) l0 a! m! wSELECT
$ X5 E; y8 _4 s CONT.TABLE.FINC_ACCT_NM,( K, y8 a- ^4 O
CONT.TABLE.FINC_ACCT_ID,: ]1 x3 v! h+ f* P7 J, U1 x
CONT.TABLE.CURR_END_OF_PERD_ACTL_VAL," n5 E, s' s* }% T/ \& J! i; U% g
CONT.TABLE.PREV_END_OF_PERD_ACTL_VAL,
; c) e3 T- r0 }+ J7 P+ E CONT.TABLE.VARNC_PLAN_VAL,
+ {' j1 E/ o5 j* P CONT.TABLE.OUTLOOK_BDGT_PLAN_VAL,
, N7 z0 S7 o& y! N9 ~ o5 Z CONT.TABLE.PERD_END_RPT_DT,
! N6 t F! Z: ~/ V7 d: l CONT.TABLE.PLAN_VERS_NM,+ Q R) R/ }) f- e( s4 W8 Z
CONT.TABLE.FRMT_ACTL_CD,: r2 t' C' n$ Z$ c
CONT.TABLE.FRMT_PLAN_CD, J$ P* k6 c% w( B
CONT.TABLE.RPT_PERD_TYPE_CD,3 n' s9 r1 {9 X
CASE $ Q# ^# i( Z% G) h' F
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Net Interest Income'
. w+ j4 w; f0 o6 C$ \ |( Z WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Non Interest Income'
- |+ V) A% O3 |: k1 z5 r9 v WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Non-Interest Expense'
. p! L* |7 I3 V1 F' C% o( v WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Total Marketing Expense'& |* p+ b! E/ `! S b
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Total Operating Expense'
/ C& ^+ f) A" G6 J WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Pre-Provision Earnings (before tax)'
( @, K; U1 R7 C( X }; j WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Net Charge-offs'
: P/ i$ p- p- `! c0 F4 S WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Other'
% \. B! o. Z: K! U5 x: W! D WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then ' Allowance Build (Release)'- M2 n+ q) J2 A3 O
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Provision Expense'
- s2 A5 U9 T) m, \ WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Pretax Income'1 T G4 A/ [* P& X; m2 _! E
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Tax Expense'" ?; d# X/ C' O- l) N5 F {
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'NIAT'
, ~- n4 k$ h# R! A WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'EPS'$ h+ M* R4 P D7 @5 L( j' P
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Ending Loans - HFI'8 |; c$ W! A0 s* X2 T' U+ u
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'avg' then 'Average Loans - HFI'. ]! _& O5 F: K* i, U+ X
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'avg' then 'Average Earning Assets'5 \* @+ S! O: @
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Ending Deposits'
( @5 S, R- \3 p/ R5 U5 N; h# X WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'avg' then 'Average Deposits'
% Q# J1 M" P# X: q) |* @7 { WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'NIM on Loans') |$ v5 N! t' ^+ j) S( u. [- b; G* ]3 v
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Revenue Margin'
* o+ w+ x2 I) ^! _6 [! {4 ^ WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'AC579' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Charge off rate'
' H/ d! |5 B+ R# r/ e* N WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Efficiency ratio'
, ?1 o% J& G. r WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'ROA'$ R6 q- ~4 {2 o& M1 p; i3 v+ M
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'ROE'+ {2 L* r l: @7 Q) }0 C2 z
WHEN ( CONT.TABLE.FINC_ACCT_ID )= 'XXXX' and ( CONT.TABLE.BAL_TYPE_CD ) = 'EOP' then 'Return on Allocated Capital (ROAC)', G3 z" ]- V1 X1 F0 _0 b' M9 Y
8 b* z! f$ B0 M) K/ w# I" G% k* q
ELSE ( CONT.TABLE.FINC_ACCT_NM ) end
# p0 b: `$ e& Q/ E- L6 wFROM t% j4 M: y5 _( g
CONT.TABLE
1 t0 ^- A5 G1 J# @+ b. sWHERE. ], M( ] V( X$ X' M
(
0 S; Z& ]/ G5 G (
0 M* k' z% p. ]/ R ( ( CONT.TABLE.PERD_END_RPT_DT ) = (' @$ }+ \. E% O. }3 P; u
SELECT Max(Perd_END_RPT_DT) % K7 R& z/ A* }, i) C
FROM CONT.TABLE
D& U; e W6 c( V4 q- a5 HWhere VERS_NM='Actual'2 x$ l1 ?) q/ M( K6 s' U3 k
AND RPT_PERD_TYPE_CD = 'Q'2 @* V2 B8 H9 S3 ~( Q
AND DATA_VLDTN_IND='Y'& t7 j$ W' `6 N o
)
/ w; m# D: h' t s7 i AND RPT_PERD_TYPE_CD = 'Q'+ P% M( G6 Q! l% j7 o
AND DATA_VLDTN_IND='Y' )
4 r, ^7 X( n) D* } OR& t( s( ?5 c; k2 L7 s3 @) T) v: l$ P
( ( CONT.TABLE.PERD_END_RPT_DT ) = (5 J$ w+ w t! s- B
SELECT Max(Perd_END_RPT_DT) ! p* K( K) V- G$ B R
FROM CONT.TABLE- P ?; S& A- d: P U3 ]
Where VERS_NM='Actual'% U Q6 M3 [+ M" O1 N1 u. n9 `: t8 K+ c
AND RPT_PERD_TYPE_CD = 'M'8 x+ R7 l6 x$ S+ H$ Z |- H
AND DATA_VLDTN_IND='Y'
) ]+ p3 \5 I1 Y0 h/ J7 M7 z)2 b+ S) w; M N' a* {9 T* T5 L5 ~
AND RPT_PERD_TYPE_CD = 'M'
6 M+ p% x/ U% Y1 q" {" w! L AND DATA_VLDTN_IND='Y' )
4 X% [. p7 y; a: j% e# F )
: x7 \* r7 N2 j AND! [: b1 S. t0 f Y G( \. P }( X
( ( CONT.TABLE.DATA_VLDTN_IND )='Y' )+ A0 Z* b2 s! x: ^
AND$ z/ N; p0 |# P2 a+ S; l- L4 q9 l
( ( CONT.TABLE.FINC_ACCT_ID )IN ('AC0006470','AC8000199','AC8002145','AC0006586','AC8000094') AND ( CONT.TABLE.DEPT_ID )='OR80637' )
/ g2 p5 [! q: f' R6 G+ b ), {, H0 u$ }3 W5 E. O: N
我的问题是，将所有这些CASE语句更改为直接列引用会对性能产生什么影响。1 F# D. f/ t2 Y }5 D- D6 e
换句话说：如果我将每个CASE语句更改为一个列名，并从查询中删除了所有CASE语句，那么会对性能产生很大影响，为什么？
4 [8 y) Q1 M5 F3 H+ V+ @我正在对此进行测试，因此我可以弄清楚性能是否受到影响，但是我对WHY的细节也同样感兴趣？（为什么的技术细节）7 i" C( D+ H M7 h
谢谢你的帮助！
8 T1 r+ Y+ t8 r2 K 2 G4 [+ j& p7 P5 I b3 D# p
解决方案:2 Z+ M! J$ P/ z% O$ _' H, m* t
8 t! Z6 B& z- H& D2 k/ n' z5 D& ~/ R6 I4 |7 F X
! I% b' t, K! @3 R
与WHERE子句中的联接相比，case语句的影响要小得多。3 z0 X0 @+ L& k3 A8 d- s' G
SQL性能的主要驱动力是I / O －从磁盘读取数据。我认为它比行处理要重要两个数量级。这只是一种启发式方法，并不基于数据库上的特定测试。
8 n5 A1 v( K" i5 r" P" X您正在执行自联接，这将需要大量的工作来读取表或进行大量的索引处理工作。' a9 ]; x& `9 B% W2 M' m4 O
另一方面，case语句变成了非常原始的硬件命令-
6 R6 Q; A3 @* p2 F$ Cequals，gotos等。数据驻留在最靠近处理器的内存中，因此它将进行压缩。您对case语句（例如like或子查询）没有任何幻想。我可以想象，如果删除语句中的大多数行，查询的速度将一样快。
/ ^3 b# c3 X: r* p' \如果您对性能有疑问，请在（VERS_NM，RPT_PERD_TYPE_CD，DATA_VLDTN_IND，Perd_END_RPT_DT）上添加索引。这个由四部分组成的索引应该使您能够获得最长时间，而无需在原始表上调用I, {6 G; X! W( m% y8 |
/ O请求。 |
|
技术问答
365 人阅读
|
0 人回复
|
2023-09-14
