Re: big transaction slows down over time - but disk seems

Ben wrote:
>
>
> On Wed, 1 Nov 2006, Richard Huxton wrote:
>
>> 1. Avoid updating the same timestamp more than once (if that's happening)
>
> Each row is updated at most once, and not all rows are updated.
>
>> 2. Update timestamps in one go at the end of the transaction (perhaps
>> by loading updates into a temp table).
>
> Hey, that's not a bad idea. I'll give that a shot. Thanks!
>
>> 3. Split the transaction in smaller chunks of activity.
>
> I'd be happy to do this too, except that I need a simple way to rollback
> everything, and I don't see how I can get that with this.

Well, you could with a temp-table, but it probably won't be necessary if
you have one. You might wan to issue a vacuum on the updated table after
the transaction completes.

Note that this idea is built on a set of assumptions that might not be
true, so do test.

Oh - if you're processing rows one at a time with your stored procedure,
see if there's not a way to process the whole set. That can make a huge
difference.

--
   Richard Huxton
   Archonet Ltd