Re: Search by index vs. sequential scan

On Tue, 20 May 2003 20:29:17 GMT, Robin Munn <rmunn@pobox.com> wrote: >There are actually several different daemons looking at the same table, >hence the daemonid. A daemon will do the following: > >SELECT * FROM worktable > WHERE daemonid = <my own daemon ID> > AND workdonetimestamp IS NULL> >If that query returns 0 rows, the daemon goes back to sleep. Otherwise, >it fetches all those rows into an array and starts doing its work. Each >row represents one work unit; for each row, the daemon starts a >transaction and runs the following: > >UPDATE worktable SET workdonetimestamp = NULL > WHERE daemonid = <my own daemon ID> > AND workdonetimestamp IS NULL... If every daemon is looking for its own id the simplest solution could be to put a "set isolation to dirty read" as first statement in your sql file. Speed should not be the problem as you get just a few hundred entries in your table over the day. Modify your table's lock mode to row by performing an "alter table worktable lock mode (row)" once as DBA. Now your update statement won't block all other rows on a page. HTH Axel