Re: HELP -- Insert's Take Forever! (code attached)

James B wrote:

> Hi,
>
> Sounds like the tables you are using need some work - they may be
> heavily fragmented due to all this inserting. Have a look at chained
> rows to see if this is the case. If it is you may want to re-build the
> table.
>
> If you have the chance to rebuild the table it would be a good idea to
> organise the storage so that insert do not add to the chaining so much
> - there is a clause in the create table command to add space for
> inserts and updates mid-table but I can't think of it now :)
>
> Partitioning the table might be an idea too - order records lend
> themselves quite nicley to division by order date.
>
> Just a couple of thoughts
>
> J
>
> >
> > HELP! I'm working with a PL/SQL program that combines or consolidates orders
> > from a database. My problem is that the inserts are SLOW AS DEATH!
> >

In addition, it appears from what you posted that you are individually commiting or rolling back every record within your loop. That is a performance killer. Preferably put one commit after END LOOP. And if that means resizing rollback segments so be it. If you can't ... I would at the minimum code an IF statement and a counter variable and commit every 50,000 or 150,000 or whatever records.

I would definitely also remodel your procedure to avoid the rollback the way you are using it. Consider this code snippet:

FOR r_header IN c_all_orders(p_region, SYSDATE) LOOP   v_is_new := (v_customer_id != r_header.customer_id);   IF v_is_new THEN
    IF NOT v_has_lines THEN
      ROLLBACK; --b/c we created a combined order with no detail lines; this

You are rolling back and nothing has happened. And even if it had this is a poor use of rollback. I would suggest that you rethink this entire procedure. And I hope the actual code has quite a few EXCEPTION blocks in it that you snipped for brevity.

Daniel Morgan Received on Fri May 24 2002 - 10:36:38 CDT