0

I have an Oracle join query that picks data very slow. It is like 1000 rows for 7 mins. Please could you help in writing the code in a different way so the data is pulled faster. The next steps for it is using the Select values and dumping the data into MySQL table. I am using Pentaho tool here. Thanks

    select 
null id, 
ss.ILOAN_CODE  ,
ss.INST_NUM  ,
ss.INST_AMT  ,
ss.INST_PRINCIPAL  ,
ss.INST_INTEREST  ,
ss.BALANCE_PRINCIPAL  ,
ss.INST_DUE_DATE  ,
ss.PAID_FLAG  ,
ss.LATE_FEE  ,
ss.PAYMENT_DATE  ,
ss.INST_AMT_PAID  ,
ss.INST_AMT_DUE  ,
ss.REV_CHECK_NUM  ,
ss.REV_CHECK_AMT  ,
ss.CREATED_BY  ,
ss.DATE_CREATED  ,
ss.UPDATED_BY  ,
ss.DATE_UPDATED  ,
ss.INST_DAYS  ,
ss.MATURED_INTEREST  ,
ss.UNPAID_INTEREST  ,
ss.ADJ_INST_PRINCIPAL  ,
ss.ADJ_INST_AMT  ,
ss.ADJ_INST_INTEREST  ,
ss.ADJ_BALANCE_PRINCIPAL  ,
ss.ADJ_MATURED_INTEREST  ,
ss.ADJ_UNPAID_INTEREST  ,
ss.IS_PRINTED  ,
ss.RTN_FEE_AMT  ,
ss.WAIVE_FEE_AMT  ,
ss.LATE_FEE_AMT  ,
ss.APR_BALANCE_PRINCIPAL  ,
ss.ACHDEPOSIT_DATE  ,
ss.ACHRETURN_DATE  ,
ss.ACHCLEAR_DATE  ,
ss.APR_INST_INTEREST  ,
ss.APR_UNPAID_INTEREST  ,
ss.CSO_FEE  ,
ss.MATURED_CSO_FEE  ,
ss.UNPAID_CSO_FEE  ,
ss.CSO_FEE_BALANCE
from  ST_IL_SCHEDULE ss,
    ST_IL_MASTER sm,
    BO_MASTER bm
where  sm.iloan_code = ss.iloan_code
     and sm.bo_code = bm.bo_code 
     and ss.ILOAN_CODE in (select distinct loan_Number from SVP_LOAN_MASTER_INVENTORY)
Ujjwal Chowdary
  • 125
  • 2
  • 5
  • 18

1 Answers1

2
and ss.ILOAN_CODE in (select distinct loan_Number from SVP_LOAN_MASTER_INVENTORY)

This is candidate for being slow. You don't need distinct here and also please use explicit join for readability.

Try:

Select 
null id, 
ss.ILOAN_CODE  ,
ss.INST_NUM  ,
ss.INST_AMT  ,
ss.INST_PRINCIPAL  ,
ss.INST_INTEREST  ,
ss.BALANCE_PRINCIPAL  ,
ss.INST_DUE_DATE  ,
ss.PAID_FLAG  ,
ss.LATE_FEE  ,
ss.PAYMENT_DATE  ,
ss.INST_AMT_PAID  ,
ss.INST_AMT_DUE  ,
ss.REV_CHECK_NUM  ,
ss.REV_CHECK_AMT  ,
ss.CREATED_BY  ,
ss.DATE_CREATED  ,
ss.UPDATED_BY  ,
ss.DATE_UPDATED  ,
ss.INST_DAYS  ,
ss.MATURED_INTEREST  ,
ss.UNPAID_INTEREST  ,
ss.ADJ_INST_PRINCIPAL  ,
ss.ADJ_INST_AMT  ,
ss.ADJ_INST_INTEREST  ,
ss.ADJ_BALANCE_PRINCIPAL  ,
ss.ADJ_MATURED_INTEREST  ,
ss.ADJ_UNPAID_INTEREST  ,
ss.IS_PRINTED  ,
ss.RTN_FEE_AMT  ,
ss.WAIVE_FEE_AMT  ,
ss.LATE_FEE_AMT  ,
ss.APR_BALANCE_PRINCIPAL  ,
ss.ACHDEPOSIT_DATE  ,
ss.ACHRETURN_DATE  ,
ss.ACHCLEAR_DATE  ,
ss.APR_INST_INTEREST  ,
ss.APR_UNPAID_INTEREST  ,
ss.CSO_FEE  ,
ss.MATURED_CSO_FEE  ,
ss.UNPAID_CSO_FEE  ,
ss.CSO_FEE_BALANCE
from  ST_IL_SCHEDULE ss,
    inner join ST_IL_MASTER sm on (sm.iloan_code = ss.iloan_code)
    inner join BO_MASTER bm on (sm.bo_code = bm.bo_code)
    inner join SVP_LOAN_MASTER_INVENTORY slm on (ss.loan_code = slm.loan number)

If that not helps please consider creating indexes on columns used in join.

Kacper
  • 4,798
  • 2
  • 19
  • 34
  • Hello Kacper, Thanks it gives me the data faster. I am testing to see if the data i get is exact match to production. – Ujjwal Chowdary Dec 05 '16 at 19:17
  • @user2148257 Query does exactly the same what yours but it is always worth checking. Good luck. – Kacper Dec 05 '16 at 19:33
  • Hello Kacper, I have tested the code in the pentaho and it takes too long to read the query but implemented the same method as you mentioned (inner join and removed distinct) It works great for other query. But for this particular table it don't work. Please can it changed it any other way. Please that would be helpful. Thanks – Ujjwal Chowdary Dec 06 '16 at 14:50
  • @user2148257 I can't see way to improve the query. Thing you can do are indexes on columns used in join. If proper indexes are in place you need to go deeper in more physical aspects (like fragmentation, chained rows) or ask DBA for help. – Kacper Dec 06 '16 at 15:03
  • Okay I will contact DBA. Thanks for the help :) – Ujjwal Chowdary Dec 06 '16 at 18:13
  • Hello Kacper, Please check below answer i have just posted. It also picks up the data faster. But I want to confirm with you will it be the same? – Ujjwal Chowdary Dec 23 '16 at 15:26
  • You replaced `ST_IL_SCHEDULE` with `ST_IL_TRANS` table. Except of that it seems to be the same – Kacper Dec 23 '16 at 16:35
  • Ok I have four tables which uses the same style of query. I have edited it now. But the change that I made from the original one is I wrote the step that is taking time above the where condition and it boosts up the speed. – Ujjwal Chowdary Dec 23 '16 at 16:47
  • So i brought the pain step above the where condition (select distinct loan_Number from SVP_LOAN_MASTER_INVENTORY) svp – Ujjwal Chowdary Dec 23 '16 at 16:48