Re: Speed up this code for me, please

From: Mark Gumbs <mgumbs_at_nospam.hotmail.com>
Date: Wed, 9 Jun 1999 08:50:58 +0100
Message-ID: <375e1a91.0@145.227.194.253>

1. Define too slow. 50,000 may take a while (but not too long)
2. How about placing the commit inside the loop if your updates never require you to rollback the WHOLE transaction.
3. You do 3 fetches of c_customers where 1 may be enough.

The following is shorter (judge for yourself about sweeter!)

DECLARE CURSOR c_customers is
SELECT rowid customer_rowid,

        customer_number,
        address

FROM NEW_TABLE v_address c_customers.address%TYPE;

BEGIN
    for v_customers in c_customers loop

      v_address := NULL;

      BEGIN
        SELECT  t2.address
        INTO    v_address
        FROM    CUSTOMERS T1,
                ADDRESES T2
        WHERE   T1.customer_number = v_customers.customer_number
        and     T1.cust_id = T2.cust_id;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
         v_address := null;

      END;

      UPDATE  VZN.VZN_GL_TRANSACTIONS
      SET     address = v_address
      WHERE   rowid = v_customers.customer_rowid;

      -- Commit here??
      commit;

    END LOOP;

• Or here?? COMMIT;

END; Mark

---

Paschal Mushubi wrote in message <375DFF7F.533F3E40_at_cognos.com>...
>I want to update NEW_TABLE with data from T1 and T2 (aliases)
>There are over 50,000 records to update. The number may grow..
>There are no indexes on the target table except on the primary key.
>This procedure is too slow. Can you make it faster? Suggestions, please.
>Here is the example code (the real procedure is much longer but uses
>same
>logic)
>DECLARE
>CURSOR c_customers is
>SELECT rowid customer_rowid,
> customer_number,
> address
>FROM NEW_TABLE
>v_customers c_customers%ROWTYPE;
>BEGIN
> OPEN c_customers;
> FETCH c_customers INTO v_customers;
> OPEN c_customers;
> FETCH c_customers INTO v_customers;
> WHILE c_customers%FOUND LOOP
> BEGIN
> SELECT CUST.address
> INTO v_customers.address
> FROM CUSTOMERS T1,
> ADDRESES T2
> WHERE T1.customer_number =
>v_customers.customer_number
> and T1.cust_id = T2.cust_id;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> v_customers.address := null;
>
>END;
> BEGIN
> UPDATE VZN.VZN_GL_TRANSACTIONS
> SET address = v_customers.address
> WHERE rowid = v_customers.customer_rowid;
> END;
> v_customers.address := NULL;
> FETCH c_customers INTO v_customers;
> END LOOP;
> CLOSE c_customers;
> COMMIT;
>END get_customer_address;
Received on Wed Jun 09 1999 - 02:50:58 CDT