Pagination using the database(DB2)

[siddartha kandikonda]

This post is about how to implement pagination when you have a very large number of records and you need to retrieve small chunks of records to be displayed on each page from the database at a time.note: this is a pure "from the scratch" approach and should be treated as the last resort i.e if nothing else works
my implementation was to display 50 records in each page(DB2 database in the backend) and the user should be able to navigate back and forth using "Next" and "Previous" buttons
Implementing "Next" button:
---------------------------------
This is the easier part of the implementation, assume you have a table with 2 columns Emp ID and Emp Name and Emp ID is the key, lets say you retrieved the first 50 records from the database in descending order(showing the latest employees first) on the first page , so if i have 500 employees in all, this will fetch records with Emp ID through 500-450, when the user clicks on "Next" you need to show records from 449-400 in the next page, all you have to do is get hold of the last record in the previous list and run a query, which goes something like this

select Emp ID,Emp Name
from employee
where Emp ID < 450
order by DESC
fetch first 50 records only

this can go on and on till you reach the last page ,

Implementing "Prev" button:
--------------------------------
This is a little tricky , lets suppose i am on page#3 viewing records from 399-350 ,if i want to go to page#2 which is records 449-400 , i need to get hold of the first record in the previous page and run a query like this

select Emp ID,Emp Name
from employee
where Emp ID > 399
order by ASC
fetch first 50 records only

here if you noticed i switched to "order by ASC" , this is the important because if i didnt order the records in ASC , this query would have fetched the first fifty records in the table i.e 500-450 , so by sorting in the reverse order and fetching the first 50 records i was able to get the records 449-400

however this implementation has a problem , this query will return the records in the order 400-449 , but in my pages i need all records to be displayed in the descending order , so you will have to reverse the order of the items fetched from the database in your java code, which can be done using Collections.sort() and Comparator , below is snippet of the code i used
Collections.sort(lstItems, Collections.reverseOrder(<Object>.<Name of the comparator>));

Agreed that this implementation will create a lot of database calls , but this works prefectly if you cannot fetch say 10K records at one go and handle the pagination on java side using lists etc

[Winston Gutkowski]

siddarth kandikonda wrote:Agreed that this implementation will create a lot of database calls , but this works prefectly if you cannot fetch say 10K records at one go and handle the pagination on java side using lists etc

Actually, the worst thing about it is that you probably don't need to do anything at all.

As far as I know you can set the cache limit for JDBC either externally or by method call (not sure exactly how myself; but I'm sure someone else here will), which means that you really only need to do the call as if you want all possible rows. Then you just deal with the ResultSet as you need to to get your 'pages'. The only problem might be if you need some sort of 'go to page n' facility, but it may even be smart enough to deal with that.

Lesson: Don't over-engineer before you know you need to.

Winston

[siddartha kandikonda]

totally agree with you, there must be really easy ways to do this, this could be something unique to the kind of setup im dealing with.ok,let me see if im understanding this correctly, you are suggesting that JDBC can handle this out of box,its going to take care of giving me the set of records i want on each page,but is it going to fetch that 10K records at one go and hold it in cache?woudn't that hit my performance?

[Winston Gutkowski]

siddartha kandikonda wrote:...ok,let me see if im understanding this correctly, you are suggesting that JDBC can handle this out of box,its going to take care of giving me the set of records i want on each page,but is it going to fetch that 10K records at one go and hold it in cache?

Yes. And I suspect you can set the cache limit a lot lower than 10K (and it's more likely to be based on memory - eg, 64Kb - anyway).

...woudn't that hit my performance?

Depends what you want, but unless it's outlandish (or the 'find me a random page' thing I suggested above) I doubt that you'll have too many problems. Databases (and SQL, being a Data-directed language) are great for this sort of thing.

Winston

[siddartha kandikonda]

Thanks winston for pointing me in the right direction,i just started looking at what JDBC offers for pagination

[Winston Gutkowski]

siddartha kandikonda wrote:...just started looking at what JDBC offers for pagination

My advice: don't look for 'pagination', because that's what you want to do with it. Look for something more like 'cache limits' or 'retrieval limits'.

Winston