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

On Wed, Jun 11, 2008 at 4:39 PM, David Lambert <davidl(at)dmisoft(dot)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.