On Nov 1, 3:49 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Ken Denny wrote:
> > On Nov 1, 11:26 am, DA Morgan <damor..._at_psoug.org> wrote:
> >> Now my examples does not include a cursor loop. But if you have
> >> DMBS_ERRLOG then you are in a version of Oracle more recent than 8i.
> >> And that means you've no reason for ever writing a cursor loop: Ever!
>
> > It can save you a lot of typing. Is it worth an extra 1000 keystrokes
> > in code to save one or two microseconds in execution time?
>
> Lets see ... with cursor loop.
>
> CREATE OR REPLACE PROCEDURE slow_way IS
>
> BEGIN
> FOR r IN (SELECT * FROM parent) LOOP
> -- modify record values
> r.part_num := r.part_num * 10;
> -- store results
> INSERT INTO child
> VALUES
> (r.part_num, r.part_name);
> END LOOP;
> COMMIT;
> END slow_way;
> /
>
> -- 7.71 seconds
>
> without cursor loop.
>
> CREATE OR REPLACE PROCEDURE fast_way IS
>
> TYPE myarray IS TABLE OF parent%ROWTYPE;
> l_data myarray;
>
> CURSOR r IS
> SELECT part_num, part_name
> FROM parent;
>
> BEGIN
> OPEN r;
> LOOP
> FETCH r BULK COLLECT INTO l_data LIMIT 1000;
>
> FOR j IN 1 .. l_data.COUNT
> LOOP
> l_data(1).part_num := l_data(1).part_num * 10;
> END LOOP;
>
> FORALL i IN 1..l_data.COUNT
> INSERT INTO child VALUES l_data(i);
>
> EXIT WHEN r%NOTFOUND;
> END LOOP;
> COMMIT;
> CLOSE r;
> END fast_way;
> /
>
> -- 0.50 seconds
>
> <SARCASM>
> Oh my gawd my fingers are killing me. All the heavy lifting and hard
> work. Please excuse me for a minute. I need to sit down and catch my
> breath ... make an appointment to have my carpel tunnel syndrome
> treated ... make out my will.
>
> And all I did was speed the code up by a lousy 15.42 times. Hardly
> worth it eh.
> </SARCASM>
>
> Good thing the web is near anonymous Ken. You might have just talked
> yourself out of quite a few job opportunities if recruiters look for
> you on the web. A few microseconds my ....
Sorry. I didn't mean to imply that the savings was never more than a
few microseconds. There are many times where the savings using bulk
collect and avoiding cursor loops are enormous. What I meant to say
was that *SOMETIMES* the savings are minimal while the increase in
keystrokes are huge. Consider in your example if your insert had to
reference 100 or more individual columns and your parent table had no
more than 100 rows.
Received on Wed Nov 07 2007 - 09:14:45 CST
