Home » RDBMS Server » Performance Tuning » PL/SQL Block (LOOP) Comparison (Oracle 10gR2 10.2.0.5, AIX 6.1)
PL/SQL Block (LOOP) Comparison [message #558052] Tue, 19 June 2012 01:30 Go to next message
Rektanocrit
Messages: 24
Registered: September 2011
Location: Philippines
Junior Member

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

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

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;

Re: PL/SQL Block (LOOP) Comparison [message #558055 is a reply to message #558052] Tue, 19 June 2012 01:37 Go to previous messageGo to next message
Littlefoot
Messages: 21687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't think so (but I might be wrong).

insert into table2 (col1, col2, col3)
select col1, col2, col3
  from table1
where col1 = :block1.text1
might, on the other hand, beat both of your previous suggestions.
Re: PL/SQL Block (LOOP) Comparison [message #558058 is a reply to message #558055] Tue, 19 June 2012 01:45 Go to previous messageGo to next message
Rektanocrit
Messages: 24
Registered: September 2011
Location: Philippines
Junior Member

I'm sorry for the double post..

I agree with your code but what if the result of the select statement returns more than 1 record? That's why I changed it to the second statement. I just inherit this module/program and I'm just trying to improve its codes. I'll test it and reply here the result. Thanks Littlefoot.
Re: PL/SQL Block (LOOP) Comparison [message #558059 is a reply to message #558058] Tue, 19 June 2012 01:52 Go to previous messageGo to next message
Littlefoot
Messages: 21687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
All three codes will do the same; there's no difference in that. But, your loops will do that row by row (some people call it "slow by slow"). Generally speaking, you should avoid PL/SQL when the same can be achieved with pure SQL.
Re: PL/SQL Block (LOOP) Comparison [message #558060 is a reply to message #558059] Tue, 19 June 2012 02:01 Go to previous message
Rektanocrit
Messages: 24
Registered: September 2011
Location: Philippines
Junior Member

Woah, you sir, is a beast! Thanks! Very Happy

Solved.
Previous Topic: combination index issue
Next Topic: Creation of Index
Goto Forum:
  


Current Time: Sat Sep 18 23:57:23 CDT 2021