-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 
Author Message
 Post subject: Performance problems with insertions.
PostPosted: Wed Mar 14, 2007 4:14 pm 
Beginner
Beginner

Joined: Thu Mar 01, 2007 9:40 am
Posts: 23
Location: UK
Hi

Hibernate version: 2.1.8
Database: Oracle 8i

I am currently facing performance issues with insertions in hibernate.
I have an object graph similar to below:
Company
Employee
WorkArea

I have one-to-many associations between Company to Employee. And I have 100's of Employees to insert into the database. Note that this is just a simple case, but my object graphs goes into various levels have 100's of objects.

Insertions into such a structure using Hibernate takes something like half an hour.. which is extremely too high. Looks like hibernate is not performing batch updates wherever required.

Can somebody help me use hibernate the right way to insert 100's of records.

Thanks,
--
Sam.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 14, 2007 5:59 pm 
Newbie

Joined: Wed Mar 14, 2007 4:30 pm
Posts: 2
Sam,

from my experience Hibernate provides the best performance. It depends of course on what kind of hardware you got. In my environment I had two servers, and one was performing 10 times slower than another. Relations were much more complicated than in your case (multiple many-to many and one-to-many), but on a best server I got ~40 ms per entity.

The bottleneck is most probably on a database side. Try to index table on column contained primary key; it will boost inserts (but will slow updates a little bit). In order to assure that index would help first truncate the tables, insert you records and get metrics. Then do it again and compare. If there is significant difference so indeing will help.

Switching to thick driver would be a help as well.


Top
 Profile  
 
 Post subject: What is your transaction model
PostPosted: Thu Mar 15, 2007 2:11 am 
Newbie

Joined: Wed Mar 14, 2007 4:35 am
Posts: 3
Location: Melbourne, Australia
Even for slow hardware and drivers, inserting 100s of rows should not take half an hour. Thousands of rows should take less than a minute.

What is your transaction model? Are you committing the transaction after every insert?

To rule out hardware, database, triggers and suchlike: how long does it take if you run a SQL insertion script with 100s of inserts directly against the database?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 15, 2007 4:38 am 
Expert
Expert

Joined: Tue Jan 30, 2007 12:45 am
Posts: 283
Location: India
Hi alexgw,

don't use session.flush() after every save.Use it after end of all save.That would reduce your DB hit

_________________
Dharmendra Pandey


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 15, 2007 6:09 am 
Beginner
Beginner

Joined: Thu Mar 01, 2007 9:40 am
Posts: 23
Location: UK
Hi,

Thanks for the replies.

Note that I'm using Hibernate 2.1.8. Does this version support batch insert. I should assume it does, as this is one of the basic requirements of an OR mapping tool.

I'm not flushing any data at the moment. My code for inserting the data looks like this at the moment:

HibernateUtil.beginTransaction();
Session session = HibernateUtil.getSession();
session.saveOrUpdate(employee);
HibernateUtil.commitTransaction();

Hence.. the commits happen only after the complete structure is inserted. I have approximately 15,000 rows to insert at various one-to-many relation tables... This seems to be taking too much time in Hibernate 2 to insert.

I currently do not have any indexes on the tables. I'm gonna add few and check if its improving the performance.

I have the following line set in configuration file:
hibernate.connection.statement_cache.size = 25
hibernate.jdbc.batch_size = 30

Hence, I'm assuming that hibernate uses these to optimize the insertions.

Can somebody provide any thoughts or inputs on how I can get Hibernate to work optimally?

Thanks,
--
Sam.

_________________
Sam.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 15, 2007 12:28 pm 
Beginner
Beginner

Joined: Thu Mar 01, 2007 9:40 am
Posts: 23
Location: UK
Hi,

I did some analysis and tweaking, and was able to improve the performance to some extend.. But still it's pretty high. One of the primary performance boosters was usage of c3p0 caching tool. That improved performance by around 100%. But still it takes around 4 mins to insert around 6000 records/objects. Which is too high. My requirement is to have it insert within 20 seconds. That looks like a dream now with Hibernate.

On analysing, I found couple of issues. I have the following structure of objects
Class A
Class B
Class C
Class D

Note that A has a collection of B objects. And B has a collection of C and D objects. I'm invoking hibernate, saying session.save(A) which is supposed to cascade-insert B, C and D objects automatically.
What I've found is that hibernate 2 doesn't use the batch facility very well. What it tries to do is insert A first using batch update, and then B and then C and D. It then goes back to next element of A collection.

In this case.. after A is inserted, the batch update is finished, and a new batch insertion is started for the next A in collection. THis is probably because the prepared statements is changed between A and B.

Ideally, hibernate should have taken all A first, persisted it using batch insert. Then create a collection of all B's. Persist that using batch insert, and then perform similar for C and D.

This would have performed much better.

Anybody has any experience around this area, and any idea how to get hibernate to work in this mode?

Thanks in advance.

_________________
Sam.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 15, 2007 11:10 pm 
Newbie

Joined: Tue Mar 13, 2007 9:49 pm
Posts: 8
Even i use the most dirty way to insert.
3000 records are not much for your situation.

I suspect that it is not related to the usage of hibernate, but other issue else.

If I were you, i would try to compare it in different way (by ignoring hibernate)

1. Run the insert statement myself (the *.sql includes 3000 lines of insert statement)

2. Execute the insert by pure JDBC


Then you might have located the bottleneck.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 16, 2007 2:14 am 
Expert
Expert

Joined: Tue Jan 30, 2007 12:45 am
Posts: 283
Location: India
Hi
try hibernateTemplate which is in hibernateDaoSupport.Pass it list of employee.That would insert all record.This feature is alaliable in 3.1.2 version .Dont know about Hibernate 2.1.8.


have a look

http://oldwww.springframework.org/docs/ ... plate.html

http://www.springframework.org/docs/api ... pport.html


Try to use

saveOrUpdateAll(Collection entities)
Save or update all given persistent instances, according to its id (matching the configured "unsaved-value"?).

_________________
Dharmendra Pandey


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 16, 2007 5:09 am 
Beginner
Beginner

Joined: Thu Mar 01, 2007 9:40 am
Posts: 23
Location: UK
Thanks Dharmendra.
But I don't currently use Spring. I intend to use Hibernate as standalone, without Spring. Hence, I don't want to create a dependency on Spring.

--
Sam.

dharmendra.pandey wrote:
Hi
try hibernateTemplate which is in hibernateDaoSupport.Pass it list of employee.That would insert all record.This feature is alaliable in 3.1.2 version .Dont know about Hibernate 2.1.8.


have a look

http://oldwww.springframework.org/docs/ ... plate.html

http://www.springframework.org/docs/api ... pport.html


Try to use

saveOrUpdateAll(Collection entities)
Save or update all given persistent instances, according to its id (matching the configured "unsaved-value"?).

_________________
Sam.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 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.