My JSP is displaying a table that supports sorting and filtering on different columns, and also displays as multiple pages because the table has way too many rows to display on one screen without scrolling. Assuming the user does a lot of manipulation of the table, which is better: cache the data in a bean for quick re-display or re-query the database every time the user wants a new sort/filter/page? I want the application to be as fast as possible, but does supporting concurrent user sessions prohibit the caching option?
Usually trying to cache a large result set in the session object will lead to quickly consuming a lot of your memory. Depending on the number of users viewing these multiple pages and how likely they are to iterate through these multiple pages, sometimes it can make sense. It can also depend on the speed for the database to perform that query.
The best way I've seen to handle this is to use a JDBC 2.0 driver that supports cursoring through the results (best if it supports the
absolute(int i) method). If it can do this, you can query your result, quickly jump to the correct row of the result without transfering all that data, and then dump the records you do want (like records 76-100). Assuming your database query is rather quick, I've found this to be the best way to handle multiple page results.