SEO

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

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
>

Sybase ASA 9 has an optional "START AT" clause as part of its TOP
construct, which would do what you want. I don't know it manages it
internally, though, and I believe you have to reissue the query if you
want a different set (or starting point) of rows, so it might not be the
best choice to help you scroll through a million rows.


In article <yq5Hf.595417$ki.458465@pd7tw2no>, user@example.net says...
> 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 ...

....

>
> > 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
> >
>

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

I'm aware of this clause in ASA... but we're not using ASA, we're using
ASE, which doesn't have that SQL extention available.

As indicated in my initial post, we considered temp tables and adding
an identity column, but there are a couple problems with that; one of
them being that often, an existing identity column is part of the
result set, and you can't put two identity columns on the same table.
We can't use those identity columns to achieve what we want because
they're full of gaps, and the sorting we put on the results will place
them out of order anyway. Most of these queries have joins as well,
also eliminating the possibility of using the identity key of a single
table.

The general idea was to encapsulate existing queries into some sort of
function or other mechanism so that all those queries didn't have to be
modified.. We've come up with a temporary solution that works:

query into #temp table
set rowcount <starting row - 1>
delete from #temp table
set rowcount <number of desired rows>
query from #temp table

This does force us to reload the entire result set whenever we want to
do a new query, however if we determine this to be an acceptable
permanent fix, if we keep track of where we are, we can use the
existing still-remaining data to query from, as long as we're going
forward. going backward we would of course need to rebuild the result
set. This would depend on where our transactional boundaries lie
however...

I would say the maximum number of rows being returned for a given
result set to be normally no more than 10,000. This is low enough to
be easily implemented as an application solution, but in this case
we're more concerned about network bandwidth usage than we are the
additional database server I/O, among other issues.

Thanks for your replies guys... this is a very valuable resource.

Hello,

I think you would like the scrollable cursor option available in ASE
15:

http://infocenter.sybase.com/help/to.../whatsnew8.htm

http://infocenter.sybase.com/help/to...g/sqlug765.htm

Thanks,
Neal