How Can I overcome slow response or memory limit with large data sets on Tomcat web application?

Question

Inventory Application built in four-layered model consisting of: applet-based graphical user interface; proprietary middle layer (Tomcat web application); Business logic in Oracle 10g stored procedures.; Data stored in Oracle 10g.

The Problem: The middle layer is designed for transactional communication, and not for moving large data sets (10,000+ rows) to the user interface or to reports. The application does not delimit the size of search results, which means that users are able to perform searches that return very large data sets to the front-end. Such requests cause the middle layer to respond slowly to this request as well as to other concurrent request, reaching the upper JVM heap memory limits and, in worst cases, causing the middle layer to crash.

Any suggestions/ help/ insight about the best solution would be much appreciated. Thank you very much.

Answer 1

You need to limit the amount of data that comes back from the server. The truth is is that there is no value in presenting 10,000 rows to a user at a time (Nobody can digest it). Break it up into smaller sets of say 100 and provide filtering mechanisms for the user to be able to find relevant data.
For reports you will need to do some fancy queries that generate the most common ad hoc reports. You might want to generate daily (or other periodic) reports offline through some batch mechanism so that the user can download these once they are ready, and that way you are not affecting your transactional processing. These would be generated through some other mechanism (outside of your app server) and written to an Excel, PDF or other file format for the user to download via a link