Re: Slow table update

Laszlo Nagy wrote:

>
> Laszlo Nagy wrote:
> > SQL:
> >
> > update product set sz_category_id=null where am_style_kw1 is not null
> > and sz_category_id is not null
> Hmm, this query:
>
> ?select count(*) from product where am_style_kw1 is not null and
> sz_category_id is not null and sz_category_id<>4809
>
> opens in 10 seconds. The update would not finish in 2600 seconds. I
> don't understand.

If the table has some sort of FK relations it might be being slowed by the need to check a row meant to be deleted has any children.

Perhaps triggers ?

If the table is very bloated with lots of dead rows (but you did say you vacuum frequently and check the results to make sure they are effective?) that would slow it down.

A long running transaction elsewhere that is blocking the delete ? Did you check the locks ?

HTH,

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)