How to retrieve data fast from a table containing Huge no of rows?
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Saravana_Bhavan
Posted On:   Thursday, June 19, 2008 03:54 AM

I am using MS SQL SERVER 2000 as the database. In my database I have a table called customer which contains the fields called cust_id, Cust_Name and some othe fields. I want to display those information in a popup window when I double click a text box in my (JSP) Form. Because my database table contains huge date It takes lot of time to retrieve all records. So I want to display only n no of (say 50) at a time. If I scroll down/up or click a button called Next/Previous it has to fetch another n no of records and display it. How to do it? Can anybody help me?    More>>
			

I am using MS SQL SERVER 2000 as the database.

In my database I have a table called customer which
contains the fields called cust_id, Cust_Name and some
othe fields. I want to display those information in a
popup window when I double click a text box in my (JSP) Form.

Because my database table contains huge date It takes lot
of time to retrieve all records. So I want to display
only n no of (say 50) at a time. If I scroll down/up or
click a button called Next/Previous it has to fetch
another n no of records and display it.


How to do it?

Can anybody help me?

   <<Less

Re: How to retrieve data fast from a table containing Huge no of rows?

Posted By:   Stephen_McConnell  
Posted On:   Thursday, June 19, 2008 12:01 PM

This question has been answered many times (even by me) on this site. However, since the Search feature is "temporarilly out of order", I found a discussion on how to do this on another web site.


Pagination using JSP and Beans. This will get you started.


Basicially, what you need to do is create the "Previous" and "Next" buttons. When you do the query on the database the first time, you limit the number of records you read in. Most SQL dialects have their own method of limiting that... check up on your particular Dialect in MS SQL Server 2000, since I don't usually work with that.


Once you read in the number of records you want to display on the page the first time, display them, and you save the "key" values for the First record in the dataset you have read and the "key" for the last record you have read in a "hidden" .


Now you have the first of your group of records on the page. When you press the "Next" button, your code recognizes that it is the "next" button pressed and uses the "lastRec" key value to get all the needed number of records in the database Greater than that key value. If the "Previous" button has been pressed, you get the appropriate number of records Less than the "firstRec" value.


You display those records retrieved and then save the "firstRec" and "lastRec" keys off again... and you are ready to display the next page.


Hope this helps. There are tons of examples out there for this.... What you are trying to do is JSP Pagenation.... There are even Tags available to do this.


Stephen McConnell

About | Sitemap | Contact