At 02:38 PM 1/2/2001 +1030, Corey Wangler wrote:

>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.

>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.

>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.

>Even bettter, the
>engine could keep one step ahead... returning a record
>and then finding the next straight away so as to
>minimize fetch response time.

And it might pack a buffer with the next n rows (however
many fit) so the client could unpack them at leisure
while the engine gets more. And it does.

>I'm not sure what the engine currently does in these
>areas, but such features could help to write speedy
>applications.

And they do. The cost of reading a page to get a
single row is very significant. Reading all the
qualifying rows off a single page to sort later
saves enough random disk reads to totally bury the
cost of the sort.

Regards,

Ann
www.ibphoenix.com
we have answers