Re: Determining offsets to jump to grab a group of records

On Wed, Jun 11, 2008 at 4:39 PM, David Lambert <davidl@dmisoft.com> wrote:
>
> We have already looked into using CURSORS but they must be within a
> transaction and we could have many of these grids open at any given time
>  looking at different tables.
>
> So the end result is that we are trying to give users the freedom to go
> through their data in a grid like fashion with seeking and positioning.
>
> We have used direct WHERE clauses in our asp.net applications but we wanted
> the desktop application to be a little bit more responsive and easy to use.
>
> Is there a better way to approach this?

Yes there is.  Use an indexed id field of some kind.

select * from table where idfield between 0 and 100;
select * from table where idfield between 1000000 and 1000100;

Will both be equally fast.  Offset / limit syntax requires the db to
materialize <offset>+<limit> rows for the query.  between and an id
does not.