Subject Re: [IB-Architect] 'Order By' Issue
Author Jason Wharton
> Yes, but time has changed, so that interactive applications
> are more common than simple batch processing;
> it means that time to first record _is_ often important.
> Retrieving only part of result set is also important,
> especially with internet applications.

I agree very much so.

I have discovered that if the ORDER BY contains more than one column and
DESC is used (for all columns of course) that it will refuse to use an index
that has matching DESC columns. This is presenting a problem with a feature
I am trying to provide the hordes coming from the PDOX world. I won't even
recognize a PLAN when I hard code it to use the index.

In short, I am making it so that everyone who has PDOX apps written in
Delphi will be able to quickly port them to InterBase and retain much of the
functionality that they formerly enjoyed. Using indexes for navigation is
how IB will emulate PDOX and it works really very nicely.

What I would love to see is the ability to give the optimizer a hint that it
is more desirable for the first n records to be fetched than assuming it is
a whole batch job where all records are going to be fetched.

One way this could be accomplished is to take advantage of the ALL keyword.


This would signify that it is going to be a batch job where all records will
be fetched. I realize that this makes the default behavior favor the
navigational model instead of the batch one so feel free to shoot it down.

Perhaps the TOP n (or was it NEXT n) records would be a good solution. I
wouldn't mind a bit as long as I could distinguish an end of the n records
verses the true EOF. I suppose counting them would suffice too.

I know Jim isn't going to like IB getting mucked up to provide a stable PDOX
equivalent but as I see it, it is a merging of the best of both worlds and
something of great value to all.

Jason Wharton
CPS - Mesa AZ