Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: Hibernate Optimization
PostPosted: Mon Jun 05, 2006 11:38 pm 
Newbie

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


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 06, 2006 7:15 am 
Senior
Senior

Joined: Wed Sep 24, 2003 3:01 pm
Posts: 158
Location: Bragan´┐Ża Paulista - Brasil
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.

_________________
Tads


Top
 Profile  
 
 Post subject: Re: Hibernate Optimization
PostPosted: Tue Jun 06, 2006 8:20 am 
Beginner
Beginner

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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.