Hibernate Optimization

anuja · **Joined:** Fri Sep 23, 2005 10:15 am **Posts:** 7

Hi,

I'm developing a web application using spring , hibernate and Mysql. My database contains huge records (200,000). Most of the time, for each user, i need to fire a query to select a considerable number of records (say 20,000). 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.
Hibernate version:3.0
Name and version of the database you are using:Mysql 5.0.18-standard

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?

Thank & Regards ,
Anuja K

tads · **Posted:** Tue Jun 06, 2006 7:15 am

anuja,

You can use the following method of HibernateTemplate:

Code:

findByCriteria(final DetachedCriteria criteria, final int firstResult, final int maxResults);

but, in this case, u must to use Criteria.
There is other options. Look up the documentation.

szroland · **Joined:** Sat Jun 03, 2006 6:23 pm **Posts:** 28

anuja wrote:

While executing the query, it takes long time and responds with out of memory error.

I'm no expert here, but I have a few ideas.

If you absolutley want minimal Hibernate overhead, you should try and execute your query with createSqlQuery, without specifying any Entities. If that is still slow, you will need to further tune your query or your database.

With Hibernate, I'd try the followings. First simply use setFirstResult() and setMaxResult() to limit the number of rows fetched, and see if that helps. Try and set the amount of rows returned to as low as 10 or 20. Out of memory error indicates you didn't limit the returned rows, so that's the first and most important thing.

If you don't need all those records in the cache, maybe you should turn off cache interaction for the query:

Code:

q.setCacheMode(CacheMode.IGNORE);

But then again, if you only get a few records at each interaction, cache handling shouldn't be much of an overhead, especially if you are likely to be able to use that data later.

If all that fails, maybe you could show us your generated query.

Roland