Paging in sybase

Posted on 2003-03-03
Medium Priority
Last Modified: 2007-12-19
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.


Question by:Ravipillala
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8058080
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.

LVL 10

Accepted Solution

bret earned 180 total points
ID: 9076328
Some approaches to this problem are documented at http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Active Directory can easily get cluttered with unused service, user and computer accounts. In this article, I will show you the way I like to implement ADCleanup..
The top devops trends for 2017 are focused on improved deployment frequency, decreased lead time for change and decreased MTTR.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question