Re: slow update but have an index

On Fri, 17 Aug 2001, Feite Brekeveld wrote:

> I have a table with no relations to other tables. It has a sequence
> number field (integer) and a status field being a char.
>
> There is a unique index on the seqno field.
>
> Now this table has about 80,000 records. I need to update 74,000 status
> fields. So I made a dump, and hacked the dump into SQL statements like:
>
> update accounting set status = 'C' where seqno = 1566385;
> ...
> and the other 74,000
>
> This is awfully slow. How come ? The index on the seqno should give
> speedy access to the record.

Unless you are putting all these updates in one transaction, each UPDATE
will be in its own separate transaction. This means you get
per-transaction overhead on every query.

I bet you will find this runs a lot faster if you 'BEGIN TRANSACTION'
before all the updates and 'COMMIT TRANSACTION' at the end.
--
Tod McQuillin