Re: How to accelerate UPDATE SQL?

dino,

1. do explain on select statement.
2. there is no WHERE clause in update statment( the where clause you have is related to SELECT other than UPDATE). this may result same rows get updated many times.

good luck
ted chyn  

Dino Hsu <dino1.nospam_at_ms1.hinet.net> wrote in message news:<5vsr5u47dmego0foatsrh1ih7jv1o7g410_at_4ax.com>...
> Dear all,
>
> I have been bothered for months by the poor performance of UPDATE, I
> have tried this on several databases with the same result. The
> following is a typical example: the scenario is to update the
> INVENTORY table, when the corresponding records don't exist, do
> UPDATE, when not, do INSERT. The INSERT will take like forever, even
> when the affected number of rows is small whereas INSERT... SELECT
> comes out almost instantly. If this problem cannot be worked out, I am
> forced to use CREATE TABLE AS SELECT + DELETE + INSERT... SELECT
> instead of UPDATE... SELECT, which is really ugly.
>
> SQL> desc inventory
> Name Null? Type
> ----------------------------- -------- --------------------
> DEF_DATE NOT NULL DATE
> SRCODE NOT NULL CHAR(2)
> FSC NOT NULL CHAR(7)
> OHQTY NUMBER(7)
> CTDQTY NUMBER(7)
> TYPE CHAR(1)
>
> SQL> desc fsrdrp
> Name Null? Type
> ----------------------------- -------- --------------------
> DEF_DATE DATE
> SRCODE CHAR(2)
> FSC CHAR(7)
> SALESQTY NUMBER(7)
> EXCHANGEQTY NUMBER(7)
> COUNTQTY NUMBER(7)
>
> SQL> desc ohctd
> Name Null? Type
> ----------------------------- -------- --------------------
> DEF_DATE NOT NULL DATE
> SRCODE NOT NULL CHAR(2)
> FSC NOT NULL CHAR(7)
> OHQTY NUMBER(7)
> CTDQTY NUMBER(7)
>
> insert into INVENTORY
> (select
> a.DEF_DATE,
> a.SRCODE,
> a.FSC,
> b.SALESQTY + b.EXCHANGEQTY - b.COUNTQTY OHQty,
> b.SALESQTY CTDQty,
> '0' TYPE
> from OHCTD a, FSRDRP b, INVENTORY c
> where
> a.SRCODE = b.SRCODE and
> a.FSC = b.FSC and
> a.SRCODE = c.SRCODE (+) and
> a.FSC = c.FSC (+) and
> c.SRCODE is null);
>
> update INVENTORY c
> set (OHQty, CTDQty)=
> (select
> b.SALESQTY + b.EXCHANGEQTY - b.COUNTQTY OHQty,
> b.SALESQTY CTDQty
> from OHCTD a, FSRDRP b
> where
> a.SRCODE = b.SRCODE and
> a.FSC = b.FSC and
> a.SRCODE = c.SRCODE and
> a.FSC = c.FSC);
>
> Any comments are highly appreciated.
>
> Regards,
> Dino
Received on Mon Feb 04 2002 - 10:00:55 CST