Teradata performance tuning

General
Enthusiast

Teradata performance tuning

Hi All,

 

Iam facing performance issue with below correlated subquery can you guys suggest me on tuning

 

select distinct
ecg.ka_type
,ecg.ka_num
,ecg.store_num
,ecg.file_dte
,ecg.invoice_num
,oim.cust_id
,oim.cust_stg_inv_id
,oim.cust_inv_dte
,ecg.bol_num
,ecg.sscc_cde
,ecg.upc_cde
,ecg.asn_qty
,ecg.dely_qty
,ecg.inven_uom_cde
,ecg.cust_asn_dely_dcrp_cde
from
v_cust_asn_dely_dcrp_file ecg
,v_invoice_sub3 oim
where
ecg.ka_type is not null
and
ecg.ka_num is not null
and
ecg.file_dte is not null
and
ecg.inven_uom_cde is not null
and
ecg.cust_asn_dely_dcrp_cde is not null
and
ecg.sscc_cde is not null
and
oim.ord_id in
( select distinct dasm.asn_detl_primary_col_val
from dsptch_asn_sscc_map dasm
where dasm.sscc_cde = ecg.sscc_cde
and dasm.asn_detl_dely_dte between (ecg.file_dte - 14 ) and (ecg.file_dte + 14) )
and oim.cust_id in (
(case
when (ecg.ka_type = 'OWN') then
( select distinct c.cust_id
from cust_base c
where c.grp2_lvl_1_ancstr_id = ecg.ka_num
and c.cust_id = oim.cust_id
)
when (ecg.ka_type = 'LV1') then
( select distinct c.cust_id
from cust_base c
where c.grp2_lvl_1_id = ecg.ka_num
and c.cust_id = oim.cust_id
)
else -1 end
)
)

 

Thanks,

Pavan

3 REPLIES 3
Teradata Employee

Re: Teradata performance tuning

Hi Pavan,

 

Where is your join between your view v_cust_asn_dely_dcrp_file ecg and v_invoice_sub3 oim ?

Without a join, you're doing a cartesian product, which is usually a bad thing.

Enthusiast

Re: Teradata performance tuning

Hi Walder,

Yes that’s right it is cartesian join I need to tune and test in QA only as TD got recently upgraded for the same code which is successfully running in PROD.

Thanks,
Pavan
Highlighted
Enthusiast

Re: Teradata performance tuning

Hi Waldar,

 

Tweaked query got stuck in joining cust base table please help me with the query.

 

select * from
v_invoice_sub3 oim, v_cust_asn_dely_dcrp_file ecg
, (select distinct dasm.asn_detl_primary_col_val as primaryVal from dsptch_asn_sscc_map dasm, v_cust_asn_dely_dcrp_file ecg
where ecg.ka_type is not null
and
ecg.ka_num is not null
and
ecg.file_dte is not null
and
ecg.inven_uom_cde is not null
and
ecg.cust_asn_dely_dcrp_cde is not null
and
ecg.sscc_cde is not null and dasm.sscc_cde = ecg.sscc_cde
and dasm.asn_detl_dely_dte between (ecg.file_dte - 14 ) and (ecg.file_dte + 14))temp1
where oim.ord_id = temp1.primaryVal

 

Thanks,

Pavan

Tags (1)