At 11:12 PM 21/03/2003 +0000, you wrote:

>Hi everybody!
>
>I need to get rows from a large table that contains more than 100,000
>records, and I wish to retrieve them in chunks of 10,000. I'm looking
>for strategies about writing a stored procedure to acomplish this. I'm
>aware that if I were using Firebird, I could use SELECT FIRST n SKIP m,
>but I'm releasing in these days and we can't afford another 3 weeks of
>testing, due to the change from Interbase to Firebird.
>
>I'm more acquanted with Oracle and SQL Server, so my first ideas where
>about using temp tables and internal table row numbers, but AFAIK, those
>features are not available in IB/FB, and since I'm building this
>solution looking for the best performance possible, I'm asking for
>your advice.

Absent the use of FIRST or ROWS clauses, you could use the strategy we used
before those constructs were added to the language. Write a stored
procedure that curses through an ordered set, accepting a unique set of
ordinal input parameters - or the RDB$DB_KEY - to delimit the starting
point of the set which is returned on a single call to the procedure. A
row-by-row counter in the SP (which can itself be initialised by an input
parameter) provides the stop point for the FOR...SELECT cursor inside the SP.

In your client application, work entirely within one transaction context,
which is isolated by REPEATABLE READ and which (if possible) captures the
values of the last row's unique ordering keyset, before performing a Commit
with Retain as each batch is finished with.

You could consider using the output set's RDB$DB_KEY as your its
ordinator. RDB$_DBKEY is a unique row enumerator that IB stores
internally. It cannot be treated like a stable "row number" outside the
boundaries of a single transaction context but, within a single
transaction, it is stable and useful. Read up on the details in Claudio
Valderrama's papers at www.cvalde.com, or in the Using Firebird manual, if
you have it.

If your resultset's keys don't themselves provide a predictable numerical
sequence from which you can calculate the "next" initial keyset, then it's
possible to provide a mechanism to have the client task operate on the
whole keyset in order to determine the "next" set of input parameters.

In that case, include another SP which is run first, to capture and
enumerate the keyset and pass it across to the client for use as a key
buffer for the entire process. If you have some means to hold the keys in
a scrollable buffer, you will add the ability to "window" the chunks of
rows and fetch each chunk at will. Some application environments may be
capable of doing this key buffering for you automatically (e.g. IB Objects
v. 4 has a technique known as "horizontal data refinement" which enables this).

heLen