Sean,

I agree with you. Our document archive system does this. We have a Java
application server that runs all queries in separate threads. It can
then return the first page to the user as soon as it is ready whilst
then continuing to generate all the other pages (even if there are
50,000 rows) and keep them in the application server (actually we only
keep the first few pages in ram the rest as spooled to disk).

Most of the time the large queries are a stupid query by the user, but
not always, sometimes the user needs the system to list all the
documents for a very large site so that they can be processed
automatically (eg written to CD-ROM for a contractor).

It would be good through to be able to choose that queries like this are
optimized for finding the first n sorted rows because as we present them
page by page to the user it does give the database longer to process the
later rows. Time to first page is a key figure in performance analysis
for our application and probably many others.

Dave



Leyne, Sean wrote:

> David,
>
> First let me say that I believe that IB/Firebird needs to support the
> TOP {x} syntax and your top 10/100 example is right on point and is a
> real shortcoming for IB/Firebird, but...
>
> With respect to your point about interactive/web applications. I
> personally have a problem with people who expect the DB server to be
> responsible for the navigation management you refer to. This should be
> made the responsibility of a middle-tier solution (it doesn't need to be
> complex...) to manage the result list and return the data as
> appropriate to the web server. There are numerous benefits to this
> approach, one of them is that the query is only performed once and the
> result persisted (in a Delphi CDS if you want) - no server
> overhead/action is then required to navigate the list.
>
> To expect the server to perform the navigation you want could require
> the query to be re-executed for each navigation - this promises to be an
> extremely 'expensive' solution and would be a sure way to bring a server
> to it's knees, not to talk about putting a Client session to "sleep"
>
> Also, let's not forget that the only way to determine the size of a
> result set is to navigate to the end of the set.
>
> I realize that mySQL does provide some of the functionality you are
> referring to, but then again I don't think that it comes a mile close to
> being a real SQL server product.
>
>
> Sean