Ann,

> >TOP {n} would be handy to limit the amount of resources
> >.... we could limit the resource usage by
> >using TOP 10000, and even though it would go thru all
> >records in the result set for ordering purposes, only those
> >within the 10000 window would be kept in the sort file (other
> >records would be discarded during the fetching).
>
> That wouldn't work as well as you might think, given
> the sort algorithm used. Besides, the major cost is
> retrieval, not sorting.

The person is only after a fixed number of records and has no interest in
any beyond that amount. Certainly this could make one algorithm more
favorable to justify this optimization. Scan the records in natural order
and use less resources to contain the temporary result cache is the
suggestion. Sounds like a great idea to me.

> >If ordering can be done *without* a temporary result set,
> >e.g. via index navigation, you would get results very
> >quickly (minimal processing before returning a record).
> >Can the database engine currently handle this?
>
> Yes, and for large result sets it is often SLOWER to
> finish than a sort. The first row comes faster, but
> the total time is LONGER. Really and truly. Tested
> and verified.

Right, we aren't questioning that. We want the first row(s) as fast as
possible.

> >couldn't the engine just go to the first
> >record that suits the criteria, and then return it?
>
> Sure. Can and does.
>
> >Subsequent fetch(s) would step through the index and
> >stop at the next valid record and return it...
>
> Reading in thousands of pages, overflowing the cache,
> kicking out pages, re-reading them, kicking out others
> that are needed again later, and so on.

I highly doubt that walking a few records into the index is going to do such
a thing. In most cases where the first records are of interest I doubt more
than 1,000 records will be requested.

We aren't asking for a total change of behavior in the engine but an
extension of more flexible behavior. Leave things as they are now, just give
us the option to tell the server to act the way we want in our circumstances
that warrant it. If we can't then our desired application interfaces are
dead in the water.

PS. I sure would like to hear back from you concerning the compound
descending order by optimization issues...

Thanks,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com