Stored Procedure taking too long, is there a better way to do this / optimize?

Question

Currently, I am trying to bulk insert a large amount of data (~500,000 rows) across a database link. I am grabbing the data from Materialized Views. I was going to add indexes but read somewhere that this will actually slow the process down. After I insert the rows, I am grabbing the Unique IDs and inserting them into a flagging table so that they are marked as 'inserted' and aren't inserted again. However this process has been stuck for about 30 minutes now. Is there a better way to do this? (Below is my code).

create or replace PROCEDURE   SEND_DATA
IS
   CURSOR cursora
   IS
      SELECT DISTINCT unique_id_1
        FROM mv_1;

   CURSOR cursorb
   IS 
      SELECT DISTINCT unique_id_2
       FROM mv_2;

ca cursora%ROWTYPE;
cb cursorb%ROWTYPE;

    sent_flag NUMBER(10);
BEGIN
    SELECT flag_id
     INTO sent_flag
     FROM flag f
    WHERE f.flag_tx = 'SENT';
---
Delete FROM TABLE1@db1
      WHERE to_date(to_char(LOCAL_TIMESTAMP,'mm/dd/yyyy'),'mm/dd/yyyy') || code in 
 (SELECT distinct to_date(to_char(LOCAL_TIME_TS,'mm/dd/yyyy'),'mm/dd/yyyy'), code FROM MV_1);
COMMIT;
    Delete FROM TABLE1@db1 
          WHERE type || timestamp in (SELECT DATA_Type_TX || UTC_TS FROM MV_1);
    COMMIT;
    insert into TABLE1@db1(DATE, TYPE, VALUE, LAST_UPDATE, FLAG, LOCAL_TIMESTAMP)
    SELECT DATA_DATE,  NAME, VALUE, SYSDATE, null, LOCAL_TIME
  FROM MV_2 A;

COMMIT;
OPEN cursora;

LOOP
 FETCH cursora into ra;
 EXIT WHEN cursora%NOTFOUND;
 INSERT INTO flag(
    SUBMIT_ID,
    FLAG_ID,
    CREATE_USER_ID,
         CREATE_DT)
   VALUES (
    rdba.SUBMIT_ID,
    SENT_FLAG,
    '1',
         sysdate);
END LOOP;
CLOSE cursora;
COMMIT;
---
EXCEPTION
      WHEN OTHERS
      THEN
           NULL;
       RAISE;
    END SEND_DATA;

Don't do the insert in a loop. Use an `insert ... select` instead — , Aug 29 '18 at 20:38
is there ever any benefit of using that loop/cursor approach over a bulk insert? — John Wick, Aug 29 '18 at 20:43
The dreaded row-by-row approach is always slower (some refer to it as slow-by-slow) — , Aug 29 '18 at 20:45
the row by row approach is the cursor correct? not the bulk insert? (excuse me for the stupid question) — John Wick, Aug 29 '18 at 20:46
There is no "bulk insert" in your code. You need to fetch a batch of rows into the collection, then use insert together with FORALL statement in order to have "bulk select/insert" functionality. See this article for details: [On BULK COLLECT](https://blogs.oracle.com/oraclemagazine/on-bulk-collect). But in your case the easiest and the fastest way is simple: INSERT ... SELECT ... statement. See this question for example: https://stackoverflow.com/questions/7323407/insert-select-statement-in-oracle-11g — krokodilko, Aug 29 '18 at 21:09

score 2 · Accepted Answer · answered Aug 30 '18 at 06:00

There are several flaws in your procedure, actually it should fail.

create or replace PROCEDURE   SEND_DATA IS
   CURSOR cursora IS
      SELECT DISTINCT unique_id_1
        FROM mv_1;

   CURSOR cursorb IS 
      SELECT DISTINCT unique_id_2
       FROM mv_2;

Cursor cursorb is not used on procedure, why do you declare it?

Delete FROM TABLE1@db1
      WHERE to_date(to_char(LOCAL_TIMESTAMP,'mm/dd/yyyy'),'mm/dd/yyyy') || code in 
 (SELECT distinct to_date(to_char(LOCAL_TIME_TS,'mm/dd/yyyy'),'mm/dd/yyyy'), code FROM MV_1);

This should fail, because first you concatenate two columns but in IN () you select two columns. Anyway, remove DISTINCT clause - it is useless.

    Delete FROM TABLE1@db1 
          WHERE type || timestamp in (SELECT DATA_Type_TX || UTC_TS FROM MV_1);

You should not use reserved keywords like TIMESTAMP as column name.

LOOP
 FETCH cursora into ra;
 EXIT WHEN cursora%NOTFOUND;
 INSERT INTO flag(SUBMIT_ID, FLAG_ID, CREATE_USER_ID, CREATE_DT)
   VALUES ( rdba.SUBMIT_ID, SENT_FLAG, '1', sysdate);
END LOOP;

Why do you enclose a numeric values by quotes (i.e. '1')? This code should fail as well, because variable ra and rdba are not declared. I assume it was

LOOP
 FETCH cursora into ca;
 EXIT WHEN cursora%NOTFOUND;
 INSERT INTO flag(SUBMIT_ID, FLAG_ID, CREATE_USER_ID, CREATE_DT)
   VALUES ( ca.unique_id_1, SENT_FLAG, '1', sysdate);
END LOOP;

Rewrite this as

INSERT INTO flag (SUBMIT_ID, FLAG_ID, CREATE_USER_ID, CREATE_DT)
SELECT DISTINCT unique_id_1, SENT_FLAG, 1, sysdate
FROM mv_1;

Assuming above would present the correct logic

EXCEPTION
      WHEN OTHERS
      THEN
           NULL;
       RAISE;

This part is completely useless. WHEN OTHERS THEN NULL; means "ignore any error" but in the next row you raise it.

thanks, and yeah I had to modify the proc so that it's a smaller 'more readable' example and probably made some silly mistakes in the process of doing so (sorry!). The reason im using quotes around '1' is because that's a varchar field but the id that we want in there is a number (weird I know). What do you suggest as the optimal 'Exception' in this case? Thanks as always for your continued assistance :) — John Wick, Aug 30 '18 at 14:45
If you are looking for the same behavior of exceptions, simply skip it entirely. — Wernfried Domscheit, Aug 30 '18 at 14:48
what would be the proper way of handling this? what I'm worried about is 1 entry/row being mad, and my whole process failing out as a result. — John Wick, Aug 30 '18 at 15:02
Well, the first action would be: Remove all the `COMMT;` commands. Make one COMMIT at the end - or make it even outside by the invoker. — Wernfried Domscheit, Aug 30 '18 at 15:41
I noticed that sometimes when im moving large amounts of data; that the process gets tied up. I read somewhere that it'd help if I committed as often as I can ? (excuse my ignorance) — John Wick, Aug 30 '18 at 16:10

Stored Procedure taking too long, is there a better way to do this / optimize?

1 Answers1