Re: Help needed with slow update

Your statement is asking for trouble. It updates several rows in a table WHILE it makes a subselect on the same table, and, what's more, it makes a triple-join, including one self-join in that subselect. For each row it updates the subselect must be repeated, as your update may affect the result of the subquery. No wonder it takes it's time.....

Instead of the above one-shot query, you could make a PL/SQL-program, in which you, e.g., retrieve the rows that need an update, store them in a PL/SQL-table and then update the rows one-by-one.

• Kenneth Koenraadt plovmand_at_hotmail.com

On Tue, 27 Feb 2001 11:20:58 -0800, "joebob" <joebob_at_zipcon.n0t> wrote:

>This takes about an hour to run even though PRODUCT only holds 32000 records, PRICING holds 17000 records, and only 2400 records got
>updated last time I ran it. Since this needs to get run on a regular basis, I need to find some faster ways if there are any. Both
>PRODUCT and PRICING are indexed so I need to gain speed somewhere else:
>
>UPDATE PRODUCT SET STATUS_CODE = 'HOLD'
>WHERE STATUS_CODE <> 'HOLD' AND STRING1 = 'GROCERY' AND PRODUCT_ID NOT IN
>(SELECT DISTINCT T1.PRODUCT_ID
>FROM PRODUCT T1, PRODUCT T2, PRICING T3
>WHERE T1.PRODUCT_ID = T2.PARENT_ID
>AND T2.PRODUCT_ID = T3.PRODUCT_ID)
>
>Thanks if you can help.
>
>--
>To reply directly, replace the zero in my email address with the letter e.
>
>
>
Received on Wed Feb 28 2001 - 12:04:31 CST