Jason Wharton wrote:

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

Thanks for the input Jason... yeah, that is exactly what I
meant. Basically, I want a way to prevent extremely large
result sets from bringing the server to it's knees due to
excessive resource usage for the temporary result set, which
would be limited in size by using TOP {n}.

Some may say you should not run such large queries.... but
if the user does not know how many records a search will
bring back, you can't restrict such activity :(

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

Yeah, I'm thinking in terms of the user interacting
with an application.... only grabbing the first 30 or
so records, viewing up to 100 or 1000 records total
as the user scrolls down in a grid. The overall speed
is not important, as long as we can get say 30 records
at a time quickly.

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

Yeah, probably would be used in conjunction with "TOP 1000",
and would most likely deal with less than 100 records at a
time as the user interacts with the data (as discussed above).

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

Jason, you hit the nail on the head... guess since we're
using IBObjects for our application we'd have similar needs ;)

As you said previously, the database engine seems to be
designed with batch processing in mind. These (optional)
optimizations would allow us to get great performance for
user interface operations, especially where unavoidably
large results sets are involved as we are only ever
interested in say the first 1000 records.... and we'd be
grabbing less than 100 records at a time.

Cheers,
Corey.

Corey Wangler
Designed Software Solutions
Adelaide, Australia.

=====================================

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