SQL Query Takes 9 Hours for 120K Records

Question

I have a small query in which I need to do some simple calculations; however, it is taking forever to finish. I have used similar queries before on larger data and performance wise was way better. I tried doing sorting on source tables but the same happens my ETL moves 2 Rows per second... below is my query (any advise regarding tuning it is more than welcome)

SELECT      
    t1.APPID,
    t1.APP_INS_ID,
    t1.APP_INS_ID_DUE_DTAE,
    TRUNC(LAST_DAY(ADD_MONTHS(t1.APP_INS_ID_DUE_DTAE, -1))) +1 AS EndDateOfPaymentMOnth,
    t1.APP_INS_ID_PAYMENT_DATE,
    t1.ispaid,
    (SELECT COUNT(APP_INS_ID) 
     FROM tbl1 t2 
     WHERE t1.APPID = t2.APPID 
       AND t2.APP_INS_ID <= t1.APP_INS_ID 
       AND T2.Is_due = 1 
       AND ispaid = 'Y'  
       AND TRUNC(APP_INS_ID_PAYMENT_DATE) <=  TRUNC(LAST_DAY(ADD_MONTHS(t1.APP_INS_ID_DUE_DTAE, -1))) + 1) AS TOTAL_PAID
FROM 
    tbl1 t1  
WHERE  
    t1.is_due = 1 
    AND t1.APP_INS_ID_DUE_DTAE < TRUNC((SYSDATE - (EXTRACT(DAY FROM SYSDATE)) + 1))

Basically I am trying to calculate how many installments are paid up to installment due date.

Your help is really appreciated!

Here is a simple data with expected output:

APPID   APP_INS_ID  APP_INS_ID_DUE_DTAE EndDateOfPaymentMOnth 
APP_INS_ID_PAYMENT_DATE     ispaid  TOTAL_PAID
1|  1|      1-Aug-14|       1-Aug-14|       2-Oct-14|           Y|  0|
1|  2|      1-Sep-14|       1-Sep-14|       2-Oct-14|           Y|  0|
1|  3|      1-Oct-14|       1-Oct-14|       2-Oct-14|           Y|  0|
1|  4|      1-Nov-14|       1-Nov-14|       2-Oct-14|           Y|  4|
1|  5|      1-Dec-14|       1-Dec-14|       24-Nov-14|          Y|  5|
1|  6|      1-Jan-15|       1-Jan-15|       9-Dec-14|           Y|  6|

you sould avoid the subquery ( count(APP_INS_ID) for each row of main query .. try add a proper (simplyfied) data sample and the expcected result — ScaisEdge, May 12 '19 at 08:50
Please generate an [Explain Plan](https://docs.oracle.com/database/121/SQLRF/statements_9010.htm#SQLRF01601) and append it to the question. Just run `EXPLAIN PLAN FOR your_query`, then `SELECT * FROM table( dbms_xplan.display )` and copy a reslut of last query - **as a text, not bitmap !!!** and append it to the question — krokodilko, May 12 '19 at 09:22
Perhaps the execution plan is not the best. Perhaps an index on tbl2 would help. Who knows, without details about the execution plan and the tables involved? — William Robertson, May 12 '19 at 10:10
There's all sorts of reasons why queries might perform badly. Please read [this answer to understand how to provide the information we need to provide assistance](https://stackoverflow.com/a/34975420/146325). — APC, May 12 '19 at 11:22

score 0 · Answer 1 · answered May 12 '19 at 11:20

0

Thank you all for your help. I resolved by issue by applying indexing on that table. Now my job moves 30k records / second.

answered May 12 '19 at 11:20

user2597012

581
4
9
28

SQL Query Takes 9 Hours for 120K Records

1 Answers1