sybperl-l Archive

From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: Moving to a specific row
Date: Aug 31 2000 5:30PM

Jeff Greif writes:
 > I wrote this thing (groaning all the while) because the guy doing the web
 > pages complained about how slow it was to throw away many thousands of rows
 > to get at a page of 100 in the middle of a long result set, when the user
 > clicked Next.  From a forms-based app, I guess you'd use some kind of browse
 > cursor, but the HTTP environment in this case prevented holding the cursor
 > across multiple web pages.  Because there was a user interaction to get each
 > page, efficiency was not a huge concern (at least until hundreds of users
 > start doing it simultaneously).  There's an index on the ordering field, so
 > the basic queries are pretty efficient.  I'm not sure of the FETCH RELATIVE
 > @offset from the cursor though -- even with a server-side cursor it must be
 > pretty bad.

Well...

The correct way to do this in a set-based envionment is to use the
unique primary key in the where clause to get the next X rows of a
request.

So assuming you have an identity column as the primary key, you simply 
remember the last value retrieved for the current page, and when the
user hits "next" you do a request where the primary key > this value.

Pretty straightforward, and fast.

Michael


 > ----- Original Message -----
 > From: "Michael Peppler" 
 > To: "SybPerl Discussion List" 
 > Sent: Thursday, August 31, 2000 9:38 AM
 > Subject: Re: Moving to a specific row
 > 
 > 
 > >
 >  brevity>
 > >
 > > Interesting.
 > >
 > > What does the showplan look like for such a request? (ie - is it
 > efficient?)
 > >
 > > (and no, Sybase does not support this syntax)
 > 

-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@peppler.org    -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com