Re: ORDER BY vs MIN to implement FIFO

"Gary Floam" <floam_at_comcast.net> wrote in message news:<vvOdnbOeovfo40bdRVn-gw_at_comcast.com>...
> Min is much better. Why have the server prepare a cursor-full of records
> when you only want one of them.
>

And you want an index on the ID otherwise you'll be doing a FTS. You also have to assume job_id is related to chronological order. In most systems ID has nothing to do with true order. An ID would be the last way I would order something like this. Something more meaningful needs to be driving it, like a date stamp, or an order number, or some such thing.

>
> "Saeed" <sr_ng_at_goawaynms-sys-lts.demon.co.uk> wrote in message
> news:68tk9yACmv2AFw$c_at_nms-sys-ltd.demon.co.uk...
> > A table holds data of jobs. Some are waiting to be actioned. A query is
> > required to pull out the the one that has been in this stae the longest.
> > The two options are:
> >
> > SELECT job_id FROM jobs WHERE status = 'W' ORDER BY job_id
> >
> > Then OPEN, FETCH, CLOSE just one row.
> >
> > The other option is
> >
> > SELECT MIN(job_id) FROM jobs WHERE status = 'W'
> >
> > Tests indicate the ORDER BY option is slightly more expensive, but the
> > trace shows much higher disk reads than using MIN.
> >
> > Anyone got any opinions as to which would be the better choice?
> >
> > Kind regards,
> >
> > Saeed
> >
> > sr_ng 786
Received on Fri Jun 25 2004 - 06:39:32 CDT