Please help me in optimizing the below query

Analytics

Please help me in optimizing the below query

The below query is taking nearly an hour to return data. Any help in optimizing it's performance would be highly appreciated. Thanks!

SELECT
b2.agrm_id as agrm_id
,cal.Target_dt as asof_dte
,b2.pymt_rst_mth_nbr as adjper_pymt_nbr
, case when btn.scdld_first_pymt_dte is null
then null
when b2.blln_term_dte is null
then null
else (btn.scdld_first_pymt_dte - b2.blln_term_dte)/30
end as ball_term
,c.int_rte_idx_type_cde as index_cde
,c.srce_int_rte_idx_dsc as index_ss_desc
,c.int_rte_idx_strc_type_cde as index_structure
,c.int_clcn_bsis_type_cde as int_calc_basis_cde
,c.int_clcn_meth_cde as int_method_cde
,c.int_rte as rate_cur

,lst_rte.int_rte as rate_prev
,c.int_type_cde as rate_typ
,b.pymt_amt_type_cde as pymt_amt_type_cde
,b2.sch_pymt_freq_cde as pymt_freq_cde
,b2.pymt_rst_mth_nbr as pymt_rst_mth_nbr
,b.pymt_sch_amt as pymt_sch_amt
,b.pymt_sch_amt_due_dte as pymt_sch_amt_due_dte

FROM RDSDW_L9_NPCR.V_WorkingDates cal

inner join (select
a1.agrm_id
, a1.int_rte_id as int_rte_id
, b2.int_rte_id as last_int_rte_id
, b2.effv_dte as effv_dte2
, a1.effv_dte
, a1.xprn_dte
, a1.pymt_rst_mth_nbr
, a1.sch_pymt_freq_cde
, a1.blln_term_dte
, row_number() over (partition by a1.agrm_id, a1.effv_dte, a1.xprn_dte
order by a1.agrm_id, effv_dte2 desc) as record_num
from RDSDW_L9_CORE.PYMT_SCH a1
left outer join RDSDW_L9_CORE.PYMT_SCH b2
on a1.agrm_id = b2.agrm_id
and a1.int_rte_id <> b2.int_rte_id
and a1.effv_dte > b2.effv_dte
qualify record_num =1 ) b2
on cal.Target_dt between b2.effv_dte and b2.xprn_dte
inner join RDSDW_L9_CORE.PYMT_SCH_AMT b
on b.agrm_id = b2.agrm_id
and cal.Target_dt between b.effv_dte and b.xprn_dte

inner join RDSDW_L9_CORE.INT_RTE c
on b2.int_rte_id = c.int_rte_id
and cal.Target_dt between c.effv_dte and c.xprn_dte

left outer join ( Select
c3.agrm_id
,cal2.target_dt as btn_asof_dte
,d4.scdld_first_pymt_dte

From
RDSDW_L9_CORE.AGRM_TO_APPL c3
,RDSDW_L9_CORE.APPL d4
,RDSDW_L9_CORE.AGRM_TERM_DTL e5
,RDSDW_L9_NPCR.V_WORKINGDATES cal2
Where
c3.appl_id = d4.appl_id
and c3.agrm_id = e5.agrm_id
and e5.blln_term_ind = 'Y'
and cal2.Target_dt between c3.effv_dte and c3.xprn_dte
and cal2.Target_dt between d4.effv_dte and d4.xprn_dte
and cal2.Target_dt between e5.effv_dte and e5.xprn_dte ) btn
on btn.agrm_id = b2.agrm_id
and btn.btn_asof_dte = cal.target_dt

left outer join RDSDW_L9_CORE.INT_RTE lst_rte
on lst_rte.int_rte_id = b2.last_int_rte_id
and cal.Target_dt between lst_rte.effv_dte and lst_rte.xprn_dte;
2 REPLIES
SN
N/A

Re: Please help me in optimizing the below query

hi,

few options to try, the table size is an important deciding factor though.....

1. try to have the derived tables (b2, btn) as seperate volatile tables and then do the left outer join on volatile tables.

create volatile table b2 as(

(select
a1.agrm_id
, a1.int_rte_id as int_rte_id
, b2.int_rte_id as last_int_rte_id
, b2.effv_dte as effv_dte2
, a1.effv_dte
, a1.xprn_dte
, a1.pymt_rst_mth_nbr
, a1.sch_pymt_freq_cde
, a1.blln_term_dte
, row_number() over (partition by a1.agrm_id, a1.effv_dte, a1.xprn_dte
order by a1.agrm_id, effv_dte2 desc) as record_num
from RDSDW_L9_CORE.PYMT_SCH a1
left outer join RDSDW_L9_CORE.PYMT_SCH b2
on a1.agrm_id = b2.agrm_id
and a1.int_rte_id <> b2.int_rte_id
and a1.effv_dte > b2.effv_dte
qualify record_num =1 ) with data on commit preserve rows;

2. In the derived table btn, specify join between tables and apt join columns.
3. Analyse and see if you can use the index columns as join columns.
4. sequence the table joins in appropriate order (may be all inner joins first and the outer joins at the end - unless business requires it in current order)
based on the amount of data in each of the tables involved in join.

above all, do an explain and see what join mechanism does the optimizer use in query plan.

Re: Please help me in optimizing the below query

Hi,

Would there be a better performance if we select the required fields from the table to the derived tables instead of the whole table being used for joins. This would reduce the SPOOL size.
Please try and let me know if there is any improvement in the performance...

Thanks,
Arun