We are trying to solve a problem with a legacy UI and a legacy schema where a complex query with multiple joins gets called at an interval of 5 mins in a multi user environment for a million records the performance goes down drastically. To address this we tried keeping the whole resultset from the query in memory using ehcache on a Remote server (a VM with 8GB RAM and Weblogic Application) using RMI and ehcache’s inbuilt querying facilities we are able to achieve a good performance fetching the records that are required. We have built an API that helps monitor and update each record when there is a change or an insert. The problem we are facing is that caching the whole resultset (1 million records) in memory of the ehcache and querying it frequently leads to almost a 6GB+ usage the query performance degrades gradually until it seems that a GC is done and then again the performance comes back to normal (after observing the usage on Visualvm). Each record from the resultset is stored as an object that with its primary key value as its key. What would be a good way to work around this problem? Considering the objects are set to never expire and all unwanted records are removed manually using code whenever they are updated Is there a better way to look at this problem as a whole?
Quick list of options: