Ann Harrison wrote:

> There is one place where TOP<n> would be useful. Currently, an order
> by clause causes InterBase to retrieve and sort the result set, unless
> the order by terms match an index exactly and the query is simple
> (single table?). In that restricted case, InterBase retrieves the
> rows in index order. You probably think that index order would be
> faster than sorting, but you would be wrong, in general. The one case
> where walking in index order is reliably faster is when you want only
> the TOP<n> rows. So TOP could be a nice hint to the optimizer.

I'm intrigued.

Are you saying that, where an index on acolumn exists

"select top 10 from atable order by acolumn"
followed by fetching all of the rows from the result set

would be faster than

"select from atable order by acolumn"
followed by fetching just the first 10 rows from the result set

and if so that this would be the case just for Interbase, or that it is
the case for all likely implementations of a relational database? The
former situation doesn't argue for offering "TOP n" in Interbase (if a
developer wishes to tweak specific performance on Interbase, there are
presumably better ways), the latter seems unlikely.

My take on this (although, I am definitely not in a position to
implement it):

- The "pure standard SQL" stance is very appealing. In addition to
providing a simple test for whether or not a feature "should" be
implemented ("not in the standard? then it shouldn't be implemented"), I
really like the idea of a database query parser that will pick me up
immediately on non-portable SQL. It means that I can start any project
on Interbase without risking a loss of portability should I want/need to
migrate later. This in turn opens the way for more projects to treat
this open-source database as their default server, and thus have more
projects, those which don't later feel the need to switch, stay on
Interbase.

- Making it easy for applications built for other DBMSs and other SQL
dialects is highly desirable.

Two approaches spring to mind:

- Keep hacking new ad-hoc features onto the query parser as people ask
for/implement them. This essentially creates
yet-another-vendor-specific-dialect that offers no immediate advantage
over other servers and eliminates both of the pure SQL benefits listed
above. If you happen to want to add two incompatible features from
different non-standard dialects of course, this just won't work.

- Provide a means for an application's SQL to be interpreted in terms of
a specified dialect. This does not mean that the core developers should
attempt to implement every dialect under the sun, merely that a
framework for dialect implementation and selection should be provided.
This way, if someone is sufficiently motivated to implement non-standard
features of other dialects (e.g. "TOP n"), there need be no argument
over whether or not the feature should be implemented, that person can
simply implement it as part of another dialect (or indeed, implement a
new dialect that includes this feature).

Needless to say, I'm advocating the second approach. Perhaps Interbase
already has this feature, or something similar, I'm a bit of a newbie to
Interbase in particular and haven't made as much time to study it as I
would have liked.

The additional dialects could then be bundled and offered on an "as is"
basis. They aren't neccessarily as complete, stable, up to date or fast
as the "official SQL (default)" dialect, but they are available for
transitional use by people porting code around the place. (It is almost
worth making non-standard dialects run slower. "Need to improve
performance? Start by switching to standard SQL!")

My $0.02.

- Raz