Re: Search then Delete Performance

On 09/14/10 5:55 PM, Michael Hull wrote:
> So fairly simply, I have a daemon running on a machine, which accesses
> this DB. Clients connect and request the details for say 1000
> simulations, at which point the daemon takes 1000 entries from the
> unassigned table and moves them to the assigned table. The once the
> client is finished with those jobs, it signals this to the daemon,
> which then move those jobs from 'assigned' to 'complete'.
>
> So this is fairly simple to implement, but my problem is that it is very slow.
>
>

instead of moving data from one table to another, it might be better to
just have a table of simulations, then another table which just contains
the PK of each simulation, and a flag that says its assigned or
unassigned (and maybe the client its assigned to? and anything else
thats related to this assignment?)... so instead of moving your big
table rows, which involves deleting them from one table and inserting
them into another, you just update the row of this small table. if you
create this small table with a fillfactor like 75%, the updates likely
will easily be handled by HOT