Re: Help with simple PL/SQL

From: DFS <nospam_at_dfs_.com>
Date: Thu, 13 Oct 2005 20:44:42 -0400
Message-ID: <_bD3f.7807$1X5.5635@fe05.lga>

DA Morgan wrote:
> DFS wrote:

>> DA Morgan wrote:
>>
>>> William Robertson wrote:
>>>
>>>> You can simplify it even firther with something like (untested):
>>>>
>>>> CREATE OR REPLACE PROCEDURE setlistorder
>>>>    ( p_projectid INTEGER )
>>>> IS
>>>>    i INTEGER := 1;
>>>> BEGIN
>>>>    FOR r IN
>>>>    (
>>>>        SELECT projcpid, itemorder
>>>>        FROM   table_project
>>>>        WHERE  projid = projectid
>>>>        ORDER BY itemorder
>>>>    )
>>>>    LOOP
>>>>        UPDATE table_project
>>>>        SET    itemorder = i
>>>>        WHERE  projcpid = r.projcpid;
>>>>
>>>>        i := i + 1;
>>>>    END LOOP;
>>>> END;
>>>>
>>>> However I might have missed what all the variables were for. It
>>>> also seems like something you should be able to do with a straight
>>>> UPDATE statement.
>>>
>>> Why would you use a cursor loop to do what could be done 100X faster
>>> with array processing?
>>
>>
>> 100x sounds exaggerated, don't you think DA?  Notice each iteration
>> contains an update statement.
>>
>> In case you're interested, that loop will operate on no more than 300
>> records.  It currently executes in half a second or so.
>>
>> But I'm curious what the array processing code would look like.
>

> No I don't and I'll prove it to you.

>

> -- step 1
> CREATE TABLE parent (
> part_num NUMBER(10),
> part_name VARCHAR2(15));

>

> -- step 2
> CREATE TABLE child AS
> SELECT *
> FROM parent
> WHERE 1=2;

>

> -- step 3
> DECLARE

>

> j PLS_INTEGER := 1;
> k parent.part_name%TYPE := 'Transducer';

>

> BEGIN
> FOR i IN 1 .. 20000
> LOOP
> SELECT DECODE(k, 'Transducer', 'Rectifier',
> 'Rectifier', 'Capacitor',
> 'Capacitor', 'Knob',
> 'Knob', 'Chassis',
> 'Chassis', 'Transducer')
> INTO k
> FROM dual;

>


>      INSERT INTO parent VALUES (j+i, k);

> END LOOP;
> COMMIT;
> END;
> /

>

> -- step 4 - this is your cursor loop
> CREATE OR REPLACE PROCEDURE slow_way IS

>

> BEGIN
> FOR r IN (SELECT * FROM parent)
> LOOP
> INSERT INTO child
> VALUES
> (r.part_num, r.part_name);
> END LOOP;
> COMMIT;
> END slow_way;
> /

>

> -- step 5 - this is my array processing
> CREATE OR REPLACE PROCEDURE fast_way IS

>

> TYPE myarray IS TABLE OF parent%ROWTYPE;
> l_data myarray;

>

> CURSOR c IS
> SELECT *
> FROM parent;

>

> BEGIN
> OPEN c;
> LOOP
> FETCH c BULK COLLECT INTO l_data LIMIT 500;

>


>      FORALL i IN 1..l_data.COUNT

>      INSERT INTO child VALUES l_data(i);

>


>      EXIT WHEN c%NOTFOUND;

> END LOOP;
> COMMIT;
> CLOSE c;
> END fast_way;
> /

>

> -- step 6 - put it to a test
> set timing on
> exec slow_way
> exec fast_way

>

> I get the following:
> 1.29 seconds with a cursor loop
> 0.07 second with array processing
> for 20,000 records.

>

> SELECT 1.29/.07 FROM dual;

>

> That is > 18X faster. I have seen it as high as ~100X in some
> circumstances with 9i. With 10g Oracle optimized the FETCH
> to use bulk collection so the change is less dramatic.

>

> Play with the number of records in the table and the array
> size and your mileage will vary.

Wow. 18x speedup is plenty awesome.

I ran your code and got 1.04 and 0.01 for the slow and fast ways (I'm running Oracle 9.2).

The procedure I use, with the cursor loop as Will Robertson suggested, registers Elapsed: 00:00:00.00 so I don't think array processing will be a time-saver for my small datasets.

(btw, I know you like Access, so I'll tell you it takes Access about 4 seconds using the cursor loop equivalent, and about 2 seconds using DAO recordset edits)

The more I use Oracle the better I think of it. It's just a fantastic dbms.

Thanks for the demo - I may be able to incorporate those techniques in other ways. Received on Thu Oct 13 2005 - 19:44:42 CDT