I'm developing a web application using spring and hibernate and Mysql. My database contains huge records (more than 2 lacs). Most of the time, for each user, i need to fire a query to select a considerable number of records (say 20 thousand). After fetching the result i need to disply it using pagination. So i don't nedd all the records at the same time, but by clicking on the next page (or a specific page number) link i need to show the respective records.
While executing the query, it takes long time and responds with out of memory error. I tried setting getHibernateTemplate().setFetchSize(200) but the performance is same. I tried different combination of changing the mysql driver, setting hibernate.jdbc.batch_size but all in vain.
How i can resolve this performance issue? wat approach i can follow to solve this? What parameters i need to take care for a large database application like this?