For the sake of discussion let's say you have a result set with 1M records,
you use an 'order by', and you want records 100-120.

Forget for the moment *how* you would retrieve records 100-120 ...

You'll have to run the initial query to build the 1M records (results most
likely placed into a worktable in tempdb). Then the records have to be
sorted (results placed into aother worktable in tempdb, or possibly a #temp
table in tempdb).

Assume you pull records 100-120 without a problem (gloss over the *how* for
the moment).

What happens next? Could your application/user then request to see records
120-150? And if so, how do you obtain them ... go through the process of
rebuilding & re-sorting your 1M record set *again*? or pull from the
previous record set?

If you answer the latter (re-use the previous record set), then you're
looking at having to temporarily store the sorted result set, but where to
store the set? in tempdb? in the application (ie, download all 1M records
into a buffer in the application)?

------------------------

If you were to run your query as a 'select/into #temp-table/order by' you
could add an identity column (eg, 'idcol = identity(12)') to your result set.

This would give you your records in sorted, numbered order.

Selecting records 100-120, or 120-150, would require a table scan of
#temp-table. Adding a clustered index 'with sorted order' on idcol would
allow for quicker access for repeated select's.

In place of #temp-table you could probably use a permanent table in tempdb.
This would allow you to use a common set of stored procs to build,
access, and clean-up these tables. One proc is passed your 'select'
statement; it builds a uniquely named table in tempdb
(select/into/identity()/sort) and passes back a 'handle' (table name?) to
the client. The client then uses this handle to access records X thru Y by
calling another stored proc which pulls records X thru Y from 'handle'
table. Obviously you'd need an additional proc for dropping said table
when it's no longer needed (would also need a background process -
crontab/shell script? - to periodically log in and remove any 'old' tables
just in case the client app fails to drop the table when it's no longer
needed). Maybe even build a high-level proc which takes a query and
record-request range and determines if table 'handle' table already exists
or not, builds 'handle' table if necessary, then pulls the desired range of
records.

------------------------

If you don't want to use a table (#temporary or permanent) then you have to
address the questions of a) where to store the 1M records (in the case
where you want to repeatedly pull from the same result set), b) how to
number the records (or use some variation of 'set rowcount'?), and how to
'manage' all of this in an eacy/common manner for a wild range of queries.





pokerdragon wrote:
> Hi all,
>
> Is there any kind of functionality, similar to a dynamic row ID, that I
> could use to retrieve a subset of rows from the middle of a result set,
> based simply on the position of the rows within the result set? For
> example, I'd like to be able to say, "Give me only the 100th through
> 120th rows of this result set". Obviously we use an order by in the
> query to guarantee that the entire result set always looks like we
> expect it to.
>
> I could just select all rows and let the code handle this, but this
> forces the entire result set - sometimes containing millions of rows -
> to come across the network, increasing traffic. I could limit the
> result set by setting rowcount to my maximum value (in the above
> example, 120) but depending on the query, table size, etc. this could
> still result in much unnecessary network traffic.
>
> This could be accomplished with a temp table or a cursor with some
> creativity, but I would like to have a dynamic solution... this option
> would require specific solutions for each unique result set I am
> parsing, and there are too many to go through the process of creating a
> solution for each one.
>
> If sybase internally numbered the rows in the result set in a somehow
> accessible way, this might be the solution, but I'm not aware of any
> such functionality in Sybase.
>
> Most, if not all, of the tables involved do not have identity keys, so
> I cannot use these to help. If I were to implement them, I would have
> to guarantee no gaps in the values, which I could do, but these are
> tables with very heavy activity and we're not willing to take the
> performance hit of not pre-caching identity values.
>
> Any thoughts or ideas on how to accomplish this would be appreciated.
> We're running ASE 12.5.3, ESD#1, Sun Solaris platform.
>
> Thanks in advance!
>
> -Mike
>