Optimising Pagination

[arun ramk]

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]

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

[Jan Cumps]

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?

[arulk pillai]

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.

[Jeanne Boyarsky]

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?