File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Optimising Pagination Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Optimising Pagination" Watch "Optimising Pagination" New topic
Author

Optimising Pagination

arun ramk
Greenhorn

Joined: Aug 09, 2008
Posts: 26
Hi,

My project is a JSP/STRUTS/Oracle 9i web application.

This project deals with medium size of data (approximately 80,000 - 100,000 records to be displayed to the user). Currently, there is a custom pagination working already. However, it is not the best design (in my opinion). This is how they do the pagination. Firstly, they load all the 80,000 records into a file, and display it the first 50 rows. I do not think that it is a good idea as loading the 80,000 records in the beginning may exhaust the server. Additionally, the system may be accessed by more than 40 users at a time. I cant imagine if 40 of them requested the 80,000 records to be displayed.

Now, I was thinking to replace the custom pagination with the already exist pagination implementation or create another custom pagination that only retrieves the first 50 rows instead of retrieving the whole 80,000 records at a time. However, here comes the complication.

The first 10 rows that are displayed in JSP are not really 50 records in DB. It may be more than 50 records (in fact, it is around 80 records). Then there are lots of business logics to merge these 80 records into 50 records shown in JSP. Is there any caching facility available? So that i can manage with single database hit?

Hence, what would be the best approach here? Can the already existing pagination be optimized here?
Jelle Klap
Bartender

Joined: Mar 10, 2008
Posts: 1836
    
    7

The most recent edition of the Javaranch Journal discusses several approaches to pagination in some detail.
[ August 14, 2008: Message edited by: Jelle Klap ]

Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

Then there are lots of business logics to merge these 80 records into 50 records shown in JSP
Is it possible to create a view, or a fancy query, that performs this business logic in one go?


OCUP UML fundamental and ITIL foundation
youtube channel
arulk pillai
Author
Ranch Hand

Joined: May 31, 2007
Posts: 3274
What you can look at is "true pagination", which brings only required number of records from the database. Hibernate has support via setFirst and setMax methods. For example in oracle you do rownum < 10 and rownum > 0 etc. Google for "hibernate true pagination" etc to see some examples.


500+ Java Interview Questions and Answers | Java job hunting know how & Java resumes
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31054
    
232

Originally posted by arulk pillai:
What you can look at is "true pagination", which brings only required number of records from the database.

Don't you need a one to one mapping between the rows in the database and the rows on the screen for "true pagination" to work?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Optimising Pagination