Pagination with a twist

Question

I am using the Struts 2 framework but this question is not just Struts 2 related.

I am trying to have some sort of pagination in my website. Let's say I have a list of records that I want to show on a page. The list has 150 records but I want to show 50 on each page so in this case there will be 3 pages. Of course, the list will be fetched from the DB.

The twist is that we have a restriction where if a query takes more than 7 seconds, it gets killed (don't ask). So if we get 150 records all at the same time our query is taking more than 7 seconds. So we decided to get 50 records every time. So in this case we will go to the DB 3 times (50 50 50). The first time we will fetch the total (150) so we know how many times should we go back to the DB.

Can some of you share your thoughts on how you would handle something like this? I know some of you might say that tweaking the stored procedure or query is the best bet but that will be a painful road to take as we have loads of SPs and queries.

I would appreciate any examples.

Answer 1

Are you just looking for general tips on how to handle paging? You're on the right track in thinking that you should grab only the records you need from the database to keep the queries small.

Different RDBMSes have different approaches to enumerating and paging records. MySQL has the LIMIT keyword for this, MS SQL Server 2005 and up have the ROW_NUMBER() function, and Oracle has a ROWNUM special column. Depending on your RDBMS it should be easy to find examples of using these features.

Answer 2

You don't need to do an actual fetch the first time, you can fetch the count only (aggregate function query). A live paginated list is common specially in very large datasets, the only problem would be that there is no guarantee of page state. that is records might be inserted in an order in a previous page thus pushing an older record in your new page, and vice versa. Its generally acceptable to have this though.

Answer 3

An example query for pagination such that you get only limited number of records is :-

select * from records limit $start,50

Here I have assumed that your original query was select * from records. Just append your original query with "limit $start,50". Here when you want first 50 records start should be equal to 0, to fetch 50-100 records start should be equal to 50 and for 100-150 records start should be equal to 100. By appending "limit $start,50" to any query, the query takes 50 records starting from record number $start. I hope this would solve your problem of pagination.