Re: How to accelerate UPDATE SQL?

From: Dino Hsu <dino1.nospam_at_ms1.hinet.net>
Date: Tue, 05 Feb 2002 09:33:05 +0800
Message-ID: <ombu5u8dh61nn6k7edlkbc2uiu09cru1or@4ax.com>

On 4 Feb 2002 11:54:29 -0800, edzard_at_volcanomail.com (Edzard) wrote:

>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
>
>Hello Dino,
>
>Here is a further question on your problem. Hope this
>contributes to finding a solution.
>
>First I want to get straight that where you say
>... The INSERT will take like forever, even ...
>you really mean the UPDATE takes forever. That is
>obvious from the contexts.

Sorry, it was a typo, it was UPDATE takes like forever...

>
>Looking at the UPDATE statement, it strikes that there
>is no WHERE clause on the table being updated (INVENTORY).
>So all INVENTORY rows are updated and for each the subquery
>is executed. This would explain the wait times, were it not
>that the affected number of rows is small, as you say. Do you
>mean that not all INVENTORY rows are affected, even though
>there is no WHERE clause for the UPDATE?
>
>Is that possible? May it for instance be an Oracle feature that
>rows are skipped from being updated if the sub-query returns no
>rows?

AFAIK, the joins happen here as a 'correlated sub-query', see http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/state21b.htm#2066913 for examples.

Actually, what I really want to do is this, but prohibited: SQL> l
  1 update

  2      (select
  3          b.SALESQTY,
  4          b.EXCHANGEQTY,
  5          b.COUNTQTY,
  6          c.OHQty,
  7          c.CTDQty
  8      from OHCTD a, FSRDRP b, INVENTORY c
  9      where
 10          a.SRCODE = b.SRCODE and
 11          a.FSC = b.FSC and
 12          a.SRCODE = c.SRCODE and
 13          a.FSC = c.FSC)
 14  set
 15      OHQty = SALESQTY + EXCHANGEQTY - COUNTQTY,
 16*     CTDQty = SALESQTY

SQL> /
    OHQty = SALESQTY + EXCHANGEQTY - COUNTQTY,     *
ERROR at line 15:
ORA-01779: cannot modify a column which maps to a non key-preserved table

In Access 97, the following UPDATE statement is valid: update

     OHCTD a 
         INNER JOIN FSRDRP b ON a.SRCODE = b.SRCODE and a.FSC = b.FSC
         INNER JOIN INVENTORY c ON a.SRCODE = c.SRCODE and a.FSC =

c.FSC
set

     c.OHQty = b.SALESQTY + b.EXCHANGEQTY - b.COUNTQTY,
     c.CTDQty = b.SALESQTY;

but in Oracle it is not.

>
>Edzard

In a word, what I want to see most is the beauty of symmetry between INSERT and UPDATE statements, and because of this, the performance should be close to each other. The problem here is the UPDATE statement I now have is hundreds of times slower than its INSERT counterpart. Probably there is a better UPDATE statement than using a correlated subquery, I hope someone can show me.

Thanks,
Dino Received on Mon Feb 04 2002 - 19:33:05 CST