> -----Original Message-----
> From: Ann W. Harrison [mailto:aharrison@...]
> Sent: Wednesday, May 30, 2001 9:42 PM
>
> At 03:28 PM 5/30/2001 -0400, David Jencks wrote:
>
> ...
>
> There's already code in the engine to limit the number of rows returned,
> but no code to handle a request that says "give me rows 21 to 30." It
> could be done, hideously inefficiently, on the server side, I suppose, but
> it would require lots of changes.

I think that "hideously inefficiently" but "on the server side" is still
nice, since it avoids sending a lot of data from the database server to the
client application.

In Web Applications (and more and more in other n-tier applications) I
often need to "page" trough data. One database server that makes this
possible and relatively simple even with dynamically generated SQL (and
without the trouble of generating database objects for SQL built on-demand)
is Oracle.

In Oracle you can achieve the range-selection functionality by using the
magic "ROWNUM" column and SELECTing over other SELECTs.

I am mentioning Oracle because my experience with it shows that selecting a
range of data _in_the_database_server_ pays a lot in performance for
applications that have to page trough data.

In Oracle, a generic way to select (on the database server side) a sub-range
from the data returned by a select statement in the form:
SELECT col1, col2, ...colN
FROM
table1, ... tableN
WHERE
someCondition
ORDER BY
someSortOrder

between the <someFirstRowNumber>-th row and the <someLastRowNumber>-th row
is to change it to this statement:

SELECT *
FROM ( SELECT col1, col2, ...colN, ROWNUM AS myRowNumAlias
FROM
table1, ... tableN
WHERE
(someCondition)
AND (ROWNUM <= someLastRowNumber)
ORDER BY
someSortOrder
)
WHERE myRowNumAlias>=someFirstRowNumber


This might still force Oracle to select all the data (depending on the sort
order and existing indexes and so) BUT at least it only transfers the data
that is really needed to display a page. So, the selection might still be
"hideously inefficiently" but it is "on the server side".

I performed some benchmarking and the performance advantage over using the
initial SELECT statement and then skipping trough data on the client side
was VERY CLEAR even for small data sets.

BTW, the outer SELECT can be avoided when someFirstRowNumber=1. There are
several considerations on why I use the outer level that have to do with
limitations on the use of the magic ROWNUM column. However, this
considerations are out of the scope of this issue. What matters is that
this approach:
- Really works on making paging trough data faster.


Even with such a feature, caching pages of data might still be necessary,
but it sure gives a lot of flexibility on only fetching those pages that
are actually requested by the user.

I sure consider this a very important feature, after having it.

I hope such functionality makes its way into Interbase. I don't mind if it
is trough ROWNUM and SELECT-over-SELECT instead of being trough a specific
range clause (I actually like it this way).


BTW, thanks for all the great work with Interbase. Small and fast is still
beautiful. (Small is something I can not say about Oracle.)


Have fun,
Paulo Gaspar