What is application-level pagination? Someone wants to view 50000 records through a web screen (just stay with me...business reasons).
- Make the query, default/starting sorting order.
- Cache results locally on the application layer the current set (in our case, cache into a hypersonic, h2, derby database that writes to file as too much to fit in memory).
- Return first
results back to the web screen (say 50 records per page).
--person re-sorts the existing resultset, re-sort from local db result set (instead of re-querying the origin db), return first
Problems we ran into:
Certain embedded databases we found to not work out well for this challenge. Hypersonic and H2 both didn't see to handle (at least with default settings) the multi-user/asychronous(web/ajax) request nature of the sorts and were causing the result sets to not be accurate when 'pushed too hard' (a user requests a sort, then changes their mind in the middle of a sort and changes the sort again).
Derby however did seem to resolve this issue for us. Yes, there are different ways to handle pagination, however need to solve the business request of how the behaviour was expected to act.
If someone has some similar experiences with application-level caching of large result sets, re-orders, pagination, please share!
