Java Design: Dynamic Query or Session data storage

Question

I recently took over a supporting project where one of the web applications is performing very badly in terms of loading the page with data. Upon troubleshooting, I found out that the number of records it pulls from DB is 8541 and stored in a list called originalList. Then the list is filtered based on certain conditions and stored in a list called filteredList with the count of 2170. Both these lists are stored in session objects. The filteredList is then shown on the screen. For this entire process, it takes more than a minute because of which its getting timedout sometimes (Please note that the max timeout is set to be 1 minute on the proxy server and we cannot changes that). This happens during on-load of the screen and no manual intervention.

Reason to store both the lists in session is: The screen also has some search criteria where user fills up certain search boxes and clicks the search button. System is trying to filter the data from these the list called originalList based on the search criteria, instead of fetching the data from DB. This is triggered by user.

Questions:

• Is it advisable to store large amount of data in the session?
• Why is it taking more than a minute for just loading the ~2000 records on screen?
• Will the large amount of data in session impact the data getting loaded on the screen?
• Is it ok to change the design to get the data from DB instead of accessing from session when the search is triggered by user?

Technologies used:

• SQL Server
• Java 1.6
• Struts 2
• Plain JDBC (No ORM frameworks)
• Plain JavaScript (No jQuery)

As it is developed in a very old fashion, I cannot change the frameworks.

Answer 1

Is it advisable to store large amount of data in the session?

No. You are storing 10K (8000 + 2000) records in session. Session is per user. If you have 1000 users, you are storing 1 MILLION records in memory. How much RAM do you have ? And BTW, even with an infinite amount of RAM, it is a waste, it is not needed, it has no added value... it is wrong.

Why is it taking more than a minute for just loading the ~2000 records on screen?

We should see your code, but considering how the session thing has been handled, there could be other culprits... it could be anything, anywhere. Enable devMode, put debug-logging, try finding where the bottleneck is.

Will the large amount of data in session impact the data getting loaded on the screen?

Probably.

Is it ok to change the design to get the data from DB instead of accessing from session when the search is triggered by user?

ABSOLUTELY.

You, or the guys who have written the software, do not know (or have unexplicably avoided) the simplest solution for this problem: PAGINATION.

It is even easier if you are using plain JDBC.

You have 8000 records, (or 8 million records, it doesn't matter): instead of loading all of them, then applying filtering and ordering, you

• send a page size (default: 20 records per page)
• send a page parameter (default: 1)
• send a criteria parameter (default: nothing)
• send an order parameter (default: id field, or date field, or whatever)

then you create the query saying you want a PAGE_SIZE number of records, starting from PAGE * PAGE_SIZE, WHERE the filters are taken from CRITERIA, ORDER by the ordering parameter.

Here is how to do it with SQL Server.

You will always hit the database, but only to take out 20-50-100 records, not 8000. The RAM will be always free, the List will be one and small, and the page will load in less than 1-2 seconds.

P.S: as suggested by Scary Wombat, if you have a small number of records (8000 is small) and an high number of accesses, you could consider caching the list in a Singleton (so not in session, once per user, but in a centralized object. It will bring more complexity since you'd have to handle data refreshing, synchronization, etc... so in this case I'll just go with the pagination.

---

NOTE: there is no need to change framework, but I'll definitely consider migrating to the latest version of Struts 2.3.x . Maybe some adjusting are needed, but you will gain a lot in terms of security and performance (depending on the version you're now).