Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    26

    Unanswered: PL/SQL Block (LOOP) Comparison

    Is there any difference between these two statement (regarding performance)?

    Code:
    PROCEDURE proc1
    IS
       CURSOR cur1
       IS
          SELECT col1, col2, col3
            FROM table1
           WHERE col1 = :block1.text1;
    BEGIN
       FOR x IN cur1
       LOOP
          INSERT INTO table2
                      (col1, col2, col3
                      )
               VALUES (x.col1, x.col2, x.col3
                      );
       END LOOP;
    END;
    VS

    Code:
    PROCEDURE proc1
    IS
    BEGIN
       FOR x IN (SELECT col1, col2, col3
                   FROM table1
                  WHERE col1 = :block1.text1)
       LOOP
          INSERT INTO table2
                      (col1, col2, col3
                      )
               VALUES (x.col1, x.col2, x.col3
                      );
       END LOOP;
    END;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Is there any difference between these two statement (regarding performance)?
    What happened when you tried?


    But running an INSERT in a loop is nearly always a very bad idea. You can replace that with a single insert statement which will be magnitutes faster:

    Code:
    PROCEDURE proc1
    IS
    BEGIN
        INSERT INTO table2 (col1, col2, col3)
        SELECT col1, col2, col3
        FROM table1
        WHERE col1 = :block1.text1
    END;

  3. #3
    Join Date
    Feb 2012
    Posts
    26
    I'm currently changing all procedures with the second one, after that I will try and check the difference of the two.

    I agree with what you said, but I think some of the select statement returns more than 1 record that is why I just changed it to the second statement.

  4. #4
    Join Date
    Feb 2012
    Posts
    26
    My bad. I'm wrong. I've changed the procedures like what you said, single insert statement.

    Thanks shammat, you're the best!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •