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

From: contrapositive <contrapositive_at_hotmail.com>
Date: Fri, 24 May 2002 19:07:00 -0400
Message-ID: <3ceecb7a$1_4@nopics.sjc>

Thanks for the feedback. See my comments below...

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3CEE5E03.51A43780_at_exesolutions.com...
> In addition, it appears from what you posted that you are individually
commiting
> or rolling back every record within your loop.

Actually I'm not. The INSERT INTO SELECT... may insert hundreds of records at a time. It determines what records to insert _based on_ the record currently active within the loop.

[...]
> 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.

Actually something _has_ potentially happened. ROLLBACK abandons the last order header created if no line items were ever added to it

Here is the stripped-down version (pseudocode). I'd be interested to know how you would remodel it.

FOR...LOOP
  IF time_to_build_an_order_header THEN
    IF has_lines THEN

      COMMIT
      --the last order header created had line
      --items; commit it now
    ELSE
      ROLLBACK
      --the last order header is never commited
      --because it had no line items

    END IF
    has_lines = FALSE --start again with a new order

    INSERT INTO order_headers
    --new order header

  END IF   INSERT INTO line_items SELECT...
  --the SELECT is based on the
  --currently active record within the loop

  IF SQL%RowCount > 0 THEN
    has_lines = TRUE
    --more processing
  END IF
END LOOP --rollback/commit block is repeated here to deal --with the last order

> And I
> hope the actual code has quite a few EXCEPTION blocks in it that you
snipped for
> brevity.
>

It does.

Thanks again.

-jk Received on Fri May 24 2002 - 18:07:00 CDT