Running long

Database

Running long

Hi!

I hve this insert qry which runs for a long time.This is cmplex qry but I thnk we can remove some unwantes case stmts.
Please suggest how to tune it or remove the unnecesaary prts.
eg:
case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’
when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'
when F.ASGN_STS = 'ABC' then 'ABC'
when F.ASGN_STS in ('UST', 'VWX') then 'VWX'
when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’
when F.ASGN_STS in ('DEF') then 'XYZ'
when F.ASGN_STS = 'IJK' then 'WMU'
else ‘Uns’ end = 'XYZ' or

can be changed to:
when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'
when F.ASGN_STS in ('DEF') then 'XYZ'
else ‘Uns’ end = 'XYZ' or

INSERT INTO IND ( GID, TGT_LVL_ID )
select distinct '478b6a70-0be7-1000-8029-0a416d330000' as c1, D1.c1 as c2
from
(select A.CNTCT as c1
from PRSN_D_CUST A
where ( A.CNTCT in
(select distinct D1.c1 as c1 from
((((((((((((
(
(
(select distinct B.QLFD_ID as c1
from M_C_IND B
where ( B.GID = '123' )
intersect
select A.CNTCT as c1
from PRSN_D_CUST A
where ( A.PIN between '1' and '999999999' )
)
intersect
select distinct C.CNTCT as c1
from SUBPLAN_MNTHLY_CUST C
where ( 0 < (coalesce(C.BALANCE , 0)) )
)
except
select A.CNTCT as c1
from PRSN_D_CUST A
where ( (coalesce(( CAST((( TIMESTAMP '2011-04-01 00:00:00' - A.BIRTH_DT ) YEAR(4)) AS INTEGER) ) , 0)) < 18 )
)
except
select distinct A.CNTCT as c1
from
PRSN_D_CUST A,
PRSN_DX D /* Al_PRSN_DX (PRSN_DX) */ ,
POSITION_D E /* Dim_POSITION_D_WMA */ ,
POSITION_DX F /* Dim_POSITION_DWMA */
where (
A.PR_POSTN = E.POSITION_NAME and F.ROW = E.ROW
and A.CNTCT = D.ROW
and (
case when D.ENGAGED = 'Y' then 'Y' else 'N' end = 'Y' or
case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y'
when F.ASGN_STS in ('LMN', 'OPQ', 'PQR', 'UST', 'STU', 'VWX') then 'N' else 'U' end = 'Y' or
case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’
when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'
when F.ASGN_STS = 'ABC' then 'ABC'
when F.ASGN_STS in ('UST', 'VWX') then 'VWX'
when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’
when F.ASGN_STS in ('DEF') then 'XYZ'
when F.ASGN_STS = 'IJK' then 'WMU'
else ‘Uns’ end = 'XYZ' or
case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’
when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'
when F.ASGN_STS = 'ABC' then 'ABC'
when F.ASGN_STS in ('UST', 'VWX') then 'VWX'
when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’
when F.ASGN_STS in ('DEF') then 'XYZ'
when F.ASGN_STS = 'IJK' then 'WMU'
else ‘Uns’ end = 'ABC'
or D.FNX2_ATRB_03 is not null
) ) )
except
(select A.CNTCT as c1
from
PRSN_D_CUST A
where ( case when A.M_SG_CD = 'a' then 'A. HN'
when A.M_SG_CD = 'b' then 'B. A I'
when A.M_SG_CD = 'c' then 'C. A II'
when A.M_SG_CD = 'd' then 'D. MMPHW' when A.M_SG_CD = 'e' then 'E. MMP A I'
when A.M_SG_CD = 'f' then 'F. MMP A II'
when A.M_SG_CD = 'g' then 'G. E A'
when A.M_SG_CD = 'h' then 'H. MM
when A.M_SG_CD = 'u' then 'I. UNC'
when A.M_SG_CD = 't' then 'K TO'
else 'J. Unknwn'
end = 'A. HN'
or
case
when A.M_SG_CD = 'a' then 'A. HN'
when A.M_SG_CD = 'b' then 'B. A I'
when A.M_SG_CD = 'c' then 'C. A II'
when A.M_SG_CD = 'd' then 'D. MMPHW'
when A.M_SG_CD = 'e' then 'E. MMP A I'
when A.M_SG_CD = 'f' then 'F. MMP A II'
when A.M_SG_CD = 'g' then 'G. E A'
when A.M_SG_CD = 'h' then 'H. MM
when A.M_SG_CD = 'u' then 'I. UNC'
when A.M_SG_CD = 't' then 'K TO'
else 'J. Unknwn'
end = 'B. A I'
or
case
when A.M_SG_CD = 'a' then 'A. HN'
when A.M_SG_CD = 'b' then 'B. A I'
when A.M_SG_CD = 'c' then 'C. A II'
when A.M_SG_CD = 'd' then 'D. MMPHW'
when A.M_SG_CD = 'e' then 'E. MMP A I'
when A.M_SG_CD = 'f' then 'F. MMP A II'
when A.M_SG_CD = 'g' then 'G. E A'
when A.M_SG_CD = 'h' then 'H. MM
when A.M_SG_CD = 'u' then 'I. UNC'
when A.M_SG_CD = 't' then 'K TO'
else 'J. Unknwn'
end = 'I. UNC'
or
case
when A.M_SG_CD = 'a' then 'A. HN'
when A.M_SG_CD = 'b' then 'B. A I'
when A.M_SG_CD = 'c' then 'C. A II'
when A.M_SG_CD = 'd' then 'D. MMPHW'
when A.M_SG_CD = 'e' then 'E. MMP A I'
when A.M_SG_CD = 'f' then 'F. MMP A II'
when A.M_SG_CD = 'g' then 'G. E A'
when A.M_SG_CD = 'h' then 'H. MM
when A.M_SG_CD = 'u' then 'I. UNC'
when A.M_SG_CD = 't' then 'K TO'
else 'J. Unknwn'
end = 'J. Unknwn'
or case
when A.M_SG_CD = 'a' then 'A. HN'
when A.M_SG_CD = 'b' then 'B. A I'
when A.M_SG_CD = 'c' then 'C. A II'
when A.M_SG_CD = 'd' then 'D. MMPHW'
when A.M_SG_CD = 'e' then 'E. MMP A I'
when A.M_SG_CD = 'f' then 'F. MMP A II'
when A.M_SG_CD = 'g' then 'G. E A'
when A.M_SG_CD = 'h' then 'H. MM
when A.M_SG_CD = 'u' then 'I. UNC'
when A.M_SG_CD = 't' then 'K TO'
else 'J. Unknwn'
end = 'K TO' )
union
(select A.CNTCT as c1
from PRSN_D_CUST A
where (
case when A.M_SG_CD = 'a' then 'A. HN'
when A.M_SG_CD = 'b' then 'B. A I'
when A.M_SG_CD = 'c' then 'C. A II'
when A.M_SG_CD = 'd' then 'D. MMPHW'
when A.M_SG_CD = 'e' then 'E. MMP A I'
when A.M_SG_CD = 'f' then 'F. MMP A II'
when A.M_SG_CD = 'g' then 'G. E A'
when A.M_SG_CD = 'h' then 'H. MM
when A.M_SG_CD = 'u' then 'I. UNC'
when A.M_SG_CD = 't' then 'K TO'
else 'J. Unknwn' end = 'C. A II' )
intersect
select distinct X.CNTCT as c1
from PRSN_F_CUST X
where ( X.AS_OF_DT = 20110101
and 375000 < X.RA_GRA_ACM_AMT + X.SRA_GSRA_ACM_AMT + X.PAN_ACM_AMT + X.TSP_ACM_AMT + X.IRA_ACM_AMT + X.IAN_ACM_AMT + X.MFS_ACM_AMT + X.CSA_ACM_AMT ) )))
except
(select A.CNTCT as c1
from PRSN_D_CUST A
where ( (
case when A.M_SG_CD = 'a' then 'A. HN'
when A.M_SG_CD = 'b' then 'B. A I'
when A.M_SG_CD = 'c' then 'C. A II'
when A.M_SG_CD = 'd' then 'D. MMPHW'
when A.M_SG_CD = 'e' then 'E. MMP A I'
when A.M_SG_CD = 'f' then 'F. MMP A II'
when A.M_SG_CD = 'g' then 'G. E A'
when A.M_SG_CD = 'h' then 'H. MM
when A.M_SG_CD = 'u' then 'I. UNC'
when A.M_SG_CD = 't' then 'K TO'
else 'J. Unknwn' end = 'G. E A' or
case when A.M_SG_CD = 'a' then 'A. HN'
when A.M_SG_CD = 'b' then 'B. A I'
when A.M_SG_CD = 'c' then 'C. A II'
when A.M_SG_CD = 'd' then 'D. MMPHW'
when A.M_SG_CD = 'e' then 'E. MMP A I'
when A.M_SG_CD = 'f' then 'F. MMP A II'
when A.M_SG_CD = 'g' then 'G. E A'
when A.M_SG_CD = 'h' then 'H. MM
when A.M_SG_CD = 'u' then 'I. UNC'
when A.M_SG_CD = 't' then 'K TO'
else 'J. Unknwn' end = 'H. Mass Market'
)
and (A.CUSTOMER_STS = 'PU-IA' or A.CUSTOMER_STS = 'PU-IRA' or A.CUSTOMER_STS = 'PU-ONLY' or A.CUSTOMER_STS = 'PU-RETAIL') )
intersect
select distinct X.CNTCT as c1
from PRSN_F_CUST X
where ( X.AS_OF_DT = 20110401 and X.RA_GRA_ACM_AMT + X.SRA_GSRA_ACM_AMT + X.PAN_ACM_AMT + X.TSP_ACM_AMT + X.IRA_ACM_AMT + X.IAN_ACM_AMT + X.MFS_ACM_AMT + X.CSA_ACM_AMT < 10000 ) ))
except
select distinct A.CNTCT as c1
from PRSN_D_CUST A,
CLUSTER_D_CUST Y
where ( A.CLUSTER = Y.ROW
and (A.CUSTOMER_STS = 'IA-ONLY'
or A.CUSTOMER_STS = 'OTHER'
or A.CUSTOMER_STS = 'RETAIL'
or A.CUSTOMER_STS = 'TUITION-ONLY'
or
case when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG' when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'
when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'
when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'
when Y.CST_ST_CD = 'e:AN' then '5. Ann'
when Y.CST_ST_CD = 'g:OT' then '6. Other'
else '7. Unknwn' end = '4. Non-Rtrmnt Services'
or
case when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG' when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'
when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'
when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'
when Y.CST_ST_CD = 'e:AN' then '5. Ann'
when Y.CST_ST_CD = 'g:OT' then '6. Other'
else '7. Unknwn' end = '5. Ann'
or case
when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG'
when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'
when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'
when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'
when Y.CST_ST_CD = 'e:AN' then '5. Ann'
when Y.CST_ST_CD = 'g:OT' then '6. Other'
else '7. Unknwn' end = '6. Other'
or case when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG'
when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'
when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'
when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'
when Y.CST_ST_CD = 'e:AN' then '5. Ann'
when Y.CST_ST_CD = 'g:OT' then '6. Other'
else '7. Unknwn' end = '7. Unknwn') ) )
except
select Z.TGT_LVL_ID as c1 from IND Z
where ( Z.GID = 'ABC' ) )
except
select Z.TGT_LVL_ID as c1 from IND Z
where ( Z.GID = 'DEF' ) )
except select Z.TGT_LVL_ID as c1
from IND Z
where ( Z.GID = 'GHI ) )
except
select Z.TGT_LVL_ID as c1 from IND Z
where ( Z.GID = 'IJK' ) )
except
select Z.TGT_LVL_ID as c1 from IND Z
where ( Z.GID = 'LMN' ) )
except
select A.CNTCT as c1 from PRSN_D_CUST A
where ( A.STATE = 'FO' ) )
intersect
select Z.TGT_LVL_ID as c1
from IND Z
where ( Z.GID = 'IND_DFT_GLL_AUD' ) )
) D1) )
) D1

Thanks!!

4 REPLIES
N/A

Re: Running long

Who is writing all those queries you post here?
Most of the source code is (how shall i put this) "overly complex" :-)

You probably don't need any of those CASEs, your example is exactly the same as:
where F.ASGN_STS in ('EFG', 'HIJ','DEF')

Dieter

Re: Running long

You ar eright..they are complex..this s an oracle code cnvtd to teradata ..Not sure if a tool was used to do this..I I understand u...are you saying that we can use
where F.ASGN_STS in ('EFG', 'HIJ','DEF') instead of the entire statement given below.
But I cann se that one of the first statements itself is case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y', and you did not mention ABC or IJK.

Entires stataemnt:
case when D.ENGAGED = 'Y' then 'Y' else 'N' end = 'Y' or
case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y'
when F.ASGN_STS in ('LMN', 'OPQ', 'PQR', 'UST', 'STU', 'VWX') then 'N' else 'U' end = 'Y' or
case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’
when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'
when F.ASGN_STS = 'ABC' then 'ABC'
when F.ASGN_STS in ('UST', 'VWX') then 'VWX'
when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’
when F.ASGN_STS in ('DEF') then 'XYZ'
when F.ASGN_STS = 'IJK' then 'WMU'
else ‘Uns’ end = 'XYZ' or
case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’
when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'
when F.ASGN_STS = 'ABC' then 'ABC'
when F.ASGN_STS in ('UST', 'VWX') then 'VWX'
when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’
when F.ASGN_STS in ('DEF') then 'XYZ'
when F.ASGN_STS = 'IJK' then 'WMU'
else ‘Uns’ end = 'ABC'
or D.FNX2_ATRB_03 is not null
N/A

Re: Running long

This must be a tool creating the SQL, no human mind will be so twisted.

where F.ASGN_STS in ('EFG', 'HIJ','DEF')
was for the CASE before the INSERT in your first post.
And there was no "case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y'".

The new CASE is probably:

D.ENGAGED = 'Y'
OR F.ASGN_STS IN ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK')
OR D.FNX2_ATRB_03 is not null

Dieter

Re: Running long

LOL!!!! Ok..thats what I thought about the nw case..thank u so much for confirming!