This discussion is archived
13 Replies Latest reply: Dec 4, 2012 2:40 PM by rp0428 RSS

Bulk INSERT performance issues with collections

977909 Newbie
Currently Being Moderated
I am developing stored procedure for Oracle 10g and I am hitting pretty heavy performance bottle neck while trying to pass list of about 2-3k items into procedure. Here's my code:

----------------------------------------------------------

TYPE ty_id_list
AS TABLE OF NUMBER(11);

----------------------------------------------------------


PROCEDURE sp_performance_test (
p_idsCollection IN schema.ty_id_list )
AS

TYPE type_numeric_table IS TABLE OF NUMBER(11) INDEX BY BINARY_INTEGER;
l_ids type_numeric_table;
data type_numeric_table;
empty type_numeric_table;

BEGIN

EXECUTE IMMEDIATE
'ALTER TABLE schema.T_TEST_TABLE NOLOGGING';
COMMIT;

SELECT COLUMN_VALUE BULK COLLECT INTO l_ids
FROM TABLE(p_idsCollection);

FOR j IN 1 .. l_ids.COUNT LOOP
data(data.count+1) := l_ids(j);

IF(MOD(data.COUNT,500) = 0 ) THEN
FORALL i IN 1 .. data.COUNT
INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
VALUES (data(i), 'Y');
data := empty;
END IF;
END LOOP;
IF(data.count IS NOT NULL) THEN
FORALL i IN 1 .. data.COUNT
INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
VALUES (data(i), 'Y');
END IF;
COMMIT;

EXECUTE IMMEDIATE
'ALTER TABLE schema.T_TEST_TABLE LOGGING';
COMMIT;

END sp_performance_test;

----------------------------------------------------------

So the thing that adds up to the process quite drastically seems to be this part: data(data.count+1) := l_ids(j); If I skip getting element from the collection and change this line to data(data.count+1) := j;, procedure execution time will be 3-4 times faster (from over 30s to 8-9s for 3k items) - but this logic obviously is not the one i want.

Can You guys give me a hint where could I improve my code to get better performance on inserting data? If any improvements can be done really.

Thanks,
Przemek
  • 1. Re: Bulk INSERT performance issues with collections
    6363 Guru
    Currently Being Moderated
    I think this code should just be
    insert into schema.t_test_table (ref_id, active)
    select column_value, 'Y' from table(p_idscollection);
  • 2. Re: Bulk INSERT performance issues with collections
    spajdy Pro
    Currently Being Moderated
    1/ table schema.T_TEST_TABLE is thera any index or trigger ?
    2/ why you copy IN collection to new collection ?
    You should use it directly
    PROCEDURE sp_performance_test (
    p_idsCollection IN schema.ty_id_list )
    AS
    BEGIN
    
    EXECUTE IMMEDIATE
    'ALTER TABLE schema.T_TEST_TABLE NOLOGGING';
    COMMIT;
    FORALL i IN 1 .. p_idsCollection.COUNT
    INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
    VALUES (p_idsCollection(i), 'Y'); 
    COMMIT;
    
    EXECUTE IMMEDIATE
    'ALTER TABLE schema.T_TEST_TABLE LOGGING'; 
    COMMIT;
    
    END sp_performance_test;
    3/ it is this procedure called from other PL/SQL code or form other language (JAVA) ?
    If it is called form PL/SQL there should be a simple way - puge SQL
    INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
    SELECT ...
    FROM ...
    WHERE ..

    4/ if you must use your procedure and want to insert data in chunks try following
    PROCEDURE sp_performance_test(p_idscollection IN schema.ty_id_list) AS
      v_count PLS_INTEGER;
      v_low PLS_INTEGER;
      v_hi PLS_INTEGER;
      v_step_size PLS_INTEGER := 500;
    BEGIN
    
        EXECUTE IMMEDIATE 'ALTER TABLE schema.T_TEST_TABLE NOLOGGING';
        COMMIT;
        v_count := p_idscollection.count;
        v_low := 1;
        LOOP
          v_hi := least(v_low + v_step_size -1,v_count);
          FORALL i IN v_low .. v_hi
            INSERT INTO schema.t_test_table(ref_id, active)
            VALUES(p_idscollection(i),'Y');
          COMMIT;
          v_low := v_hi + 1;
          EXIT WHEN v_low > v_count;
        END LOOP;    
    
        EXECUTE IMMEDIATE 'ALTER TABLE schema.T_TEST_TABLE LOGGING';
        COMMIT;
    
    END sp_performance_test;
    If performace problems still persist then trace you session and look where time is spent.
  • 3. Re: Bulk INSERT performance issues with collections
    Purvesh K Guru
    Currently Being Moderated
    No Database at hand but, I guess your code should be this way
     PROCEDURE sp_performance_test (
       p_idsCollection IN schema.ty_id_list )
     AS
     
     TYPE type_numeric_table IS TABLE OF NUMBER(11) INDEX BY BINARY_INTEGER;
     l_ids type_numeric_table;
    -- data type_numeric_table;                    --------------> Not Required
    -- empty type_numeric_table;                 --------------> Not Required
     
     BEGIN
     
     EXECUTE IMMEDIATE
       'ALTER TABLE schema.T_TEST_TABLE NOLOGGING';
    -- COMMIT;              --> No Commit required
    
     LOOP
      SELECT COLUMN_VALUE BULK COLLECT INTO l_ids Limit 100          ---> Use Limit to avoid Overloading PGA.
      FROM TABLE(p_idsCollection);
    
      FORALL i IN 1 .. l_ids.COUNT
        INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
        VALUES (l_ids(i), 'Y');
    
      exit when l_ids.count = 0;
     END LOOP; 
    
     COMMIT;
     
     EXECUTE IMMEDIATE
       'ALTER TABLE schema.T_TEST_TABLE LOGGING'; 
     --COMMIT;              --------------> Not Required
     
     END sp_performance_test;
    I would suggest not performing DDL's in a Package execution.
  • 4. Re: Bulk INSERT performance issues with collections
    Suman Rana Explorer
    Currently Being Moderated
    Hi Purvesh,

    I have some concerns abt your code..

    PROCEDURE sp_performance_test (
    p_idsCollection IN schema.ty_id_list )
    AS

    TYPE type_numeric_table IS TABLE OF NUMBER(11) INDEX BY BINARY_INTEGER;
    l_ids type_numeric_table;
    -- data type_numeric_table; --------------> Not Required
    -- empty type_numeric_table; --------------> Not Required

    CURSOR C1 is SELECT COLUMN_VALUE aa FROM TABLE(p_idsCollection);


    BEGIN

    EXECUTE IMMEDIATE
    'ALTER TABLE schema.T_TEST_TABLE NOLOGGING';
    -- COMMIT; --> No Commit required

    LOOP
    FETCH C1 BULK COLLECT INTO l_ids LIMIT 100;

    -- SELECT COLUMN_VALUE BULK COLLECT INTO l_ids Limit 100 ---> Use Limit to avoid Overloading PGA.
    FROM TABLE(p_idsCollection);                                                     -> We can not use LIMIT caluse along with SELECT Query, we can use this with explicit CURSOR


    FORALL i IN 1 .. l_ids.COUNT
    INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
    VALUES (l_ids(i), 'Y');

    -- exit when l_ids.count = 0;
    EXIT WHEN C1%NOTFOUND; ----> We can not use this clause before FORALL
    END LOOP;

    COMMIT;

    EXECUTE IMMEDIATE
    'ALTER TABLE schema.T_TEST_TABLE LOGGING';
    COMMIT;              ------------> Not Required

    END sp_performance_test;
  • 5. Re: Bulk INSERT performance issues with collections
    Purvesh K Guru
    Currently Being Moderated
    Yes. You are correct. Apology as it slipped from my mind.

    I, in fact, agree with solution by 3360. Its concise and simple.
  • 6. Re: Bulk INSERT performance issues with collections
    Suman Rana Explorer
    Currently Being Moderated
    Yes solution provided by 3360 is very simple..but here issue is if u want to Insert a huge amount record into table..this query could create memory issue.. but using BULK COLLECT with LIMIT clause would not create memory consumption issue since here we are inserting record on batch chunk basis..
  • 7. Re: Bulk INSERT performance issues with collections
    padders Pro
    Currently Being Moderated
    If p_idsCollection is large then you have a memory issue however you look at it.
  • 8. Re: Bulk INSERT performance issues with collections
    6363 Guru
    Currently Being Moderated
    Suman Rana wrote:
    Yes solution provided by 3360 is very simple..
    And will be the fastest method.
    but here issue is if u want to Insert a huge amount record into table..this query could create memory issue..
    What specific memory issue and what error are you referring to?
    but using BULK COLLECT with LIMIT clause would not create memory consumption issue
    Same question on memory issue? Also you do know that bulk collect fetches the results into memory and so uses more memory than the code I posted, which is why you need limit clause.

    http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html

    >
    The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory."
    Specifically, memory for collections is stored in the program global area (PGA)
    >
    since here we are inserting record on batch chunk basis..
    And using more memory and doing it slower as a result.
  • 9. Re: Bulk INSERT performance issues with collections
    John Spencer Oracle ACE
    Currently Being Moderated
    While I agree with 3360 that a simple insert into select is the best approach based on what you have posted, I have to ask, how is the parameter p_idsCollection beng populated? Is there some user sitting in front of a computer somewhere typing in 2 - 3 K items to populate the list, or is the list getting populated through a database query somewhere?

    If it is the latter, then the whole procedure may be redundant, and you could just do it with a simple insert select form wahtever query populates the list, which would be even faster than 3360's solution.

    John
  • 10. Re: Bulk INSERT performance issues with collections
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi
    3360 wrote:
    Suman Rana wrote:
    Yes solution provided by 3360 is very simple..
    ...
    What specific memory issue and what error are you referring to?
    my guess is he meant UNDO issue. it's possible, but rather unlikely given that the op is only passing a couple of thousand records to the procedure (unless each is megabytes big).

    Best regards,
    Nikolay
  • 11. Re: Bulk INSERT performance issues with collections
    6363 Guru
    Currently Being Moderated
    Nikolay Savvinov wrote:
    Hi
    3360 wrote:
    Suman Rana wrote:
    Yes solution provided by 3360 is very simple..
    ...
    What specific memory issue and what error are you referring to?
    my guess is he meant UNDO issue. it's possible, but rather unlikely given that the op is only passing a couple of thousand records to the procedure (unless each is megabytes big).
    Inserts generate the smallest amount of UNDO and it is not related in any way to the size of the row since all it contains is ROWIDs.

    Regardless of what he meant, what he wrote was completely wrong.
  • 12. Re: Bulk INSERT performance issues with collections
    977909 Newbie
    Currently Being Moderated
    Thanks for all tips guys and to answer some of Your questions:

    1/ This procedure is being called from Java code so essentially procedure would be desired way to go.

    2/ I initially tried INSERT-SELECT in the way similar to (don't have access to db so sorry for the syntax errors)
    insert into schema.T_TEST_TABLE select column_value from table(p_idsCollection);
    but that seemed to be slightly slower than my code from above for about 3k records.

    Does it make sense to split this 3k into smaller chunks (500?) and perform INSERT-SELECT on these? Just a guess.

    3/ Idea of re-writing collection into l_ids was to take advantage of type_numeric_table type index, i am not entirely sure if this does make sense either, it was just a blind try pretty much. I am looking for Your opinion guys if it's even worth to do it.

    4/ @spajdy, I tried Your way of splitting and it gained me like 8s immediately so great thanks for that tip!

    5/ Yes destination table does have 2 or 3 indexes and a trigger that takes ID value from the sequence

    Thanks for all Your help and I'm looking forward more ideas guys!
  • 13. Re: Bulk INSERT performance issues with collections
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post be sure to provide your 4 digit Oracle version.
    >
    1/ This procedure is being called from Java code so essentially procedure would be desired way to go.

    2/ I initially tried INSERT-SELECT in the way similar to (don't have access to db so sorry for the syntax errors)
    insert into schema.T_TEST_TABLE select column_value from table(p_idsCollection);
    but that seemed to be slightly slower than my code from above for about 3k records.

    Does it make sense to split this 3k into smaller chunks (500?) and perform INSERT-SELECT on these?
    >
    No - that doesn't make sense.

    What makes sense is to just use Java to do the INSERTS directly using Oracle's batch processing. You are spending more time monkeying around about functions and collections and everything you could have had the Java code written and executed by now. And it would have been faster and more scalable.

    K.I.S.S. - simpler is better. Not sure how you got into trying to use collections and stored procedures when all you need is simple Java.

    See the 'Setting the Statement Batch Value' section in the JDBC Developer's Guide. It has example code as well.
    http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraperf.htm#sthref1162

    You could use just use
    1. A FOR loop that provides your 3k different values
    2. An OraclePreparedStatement to do the insert
    3. Set the batch size to 100
    4. Have a cup of coffee while it finishes - it will only take a few seconds.

    This code isn't tested but you can check the example in the doc.
    int [] myInts = new int [3000];
    // call a method to populate your array here
    conn.setAutoCommit = false;
    OraclePreparedStatement ps = (OraclePreparedStatement) conn.prepareStatement
       ("insert into schema.t_test_table (ref_id, active) values (?, 'Y')");
    ps.setExecuteBatch(100);
    
    for (int i = 0; i < myInts.length(); i++) {
       ps.setInt (1, myInts);
    int rows = ps.executeUpate();
    }
    conn.commit;
    ps.close()
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points