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?
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.