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.

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.

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?

Hi alexgw,

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

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.

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.

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.

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"?).

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.