Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1925
  • Last Modified:

Paging in sybase

Hi All,
Can anyOne help me in this one,
My problem is:
I have to fetch records from the sybase in a page wise manner,like for first page i have to display 10 records and in the second page 10-20 records etc..
but my main preoblem is in that table 3 Bilion records are there ,but at worst condition my select query fetches 1 Milion records.to execute this query it is taking a lot of time.
Currently in my SP i had implimented Rowcount(Rowcount=10 if it is in a first page,20 if second page) etc..
but when the user keep on moves to next Page.. the execution time keeps on increasing.
is there any way to fetch the records from query specifying the startpoint and endpoint.like
select records from 10 to 20
select records from 30 to 40
select records from 40 to 50 etc..
to fetch only 10 records always Query will take less time to execute.
Please Help me out from this problem.
Thanks in advance.


1 Solution
Instead of "select * from table mytable" and grabbing everything, you can use "select * from mytable where keycol > @min_value and keycol < @min_value + displaypagesize".  In this example, keycol should preferably be the primary key, or at least a unique indexed column that is sequential.  

What I've seen most often is that the client will grab a full results set and manage display paging itself.  It gets easier to manage updates that way, and it's often more efficient.  If your select statement is returning more than five or so display pages, it's unlikely that a user would ever really need to browse so many rows and your design isn't really optimized for what a user is likely to do or need.

Hope this helps.

Some approaches to this problem are documented at http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now