I am working in a finance project where each transaction table is having more than 4 Million records just for 3 months. I have user & branch related information on other tables.
I wrote a joined query by joining around 5 tables to summarize the report based on Branch, Time & type of transaction. But when I tried to run the query it is taking quite long time & sometime results in no response. timeout happens in servlet some time. I am using index for all necessary columns.
I am using JSP/servlet concept. And I am using Amazon EC2 small instance for running the jboss server.
I am really struggling with this for more than 2 weeks. Is there any way to improve the query performance?
I was advised to read the database into memory. But I do not know how to achieve this. Does anybody have an idea how to achieve his in java? Please help me.