assuming I will have millions of objects for batch insert. Even though we can control the batch size, still a huge number of database access, I worried about the performance.

any suggestion?

Don't worry, measure :-).

In our project we noticed that batch update improves performance by orders of magnitude. We also noticed that (at least for Oracle 9i) it can make sense to use a batch size of up to 1000 (Hibernate suggest 5 to 30 or so).

Erik

hi erik,

thanks for your kindly advice.

In our production system, we might have 18 millions records need to be inserted for one operation.

I am thiniking should I handle at this level( I mean at the DB access level), or should this be handled at higher level(application level), or maybe some asynchronous approaches need to be considered? I have no clue now.

Should I delegate this to the caller which is at the higher level of the application?

anyway, 1000 batch size sounds much better than 0-30. thanks a lot.

Hi,

The experience from our project was that you have to tune the application.

We had about 8 millions record to insert and/or update (there was quite a bit of logic in the way to do the inserts/updates). With the first version of our program we would have needed days (if not weeks) - we stopped it of course earlier. We also had OutOfMemoryException every now and then.

Then we began to tune. I cannot remember exactly how long it took in the end (I think about 6 hours, which was acceptable for our client).

We compared the Hibernate application with a PL-SQL application that was developped in parallel (just in case we would have failed...). Java/Hibernate was faster - though the PL-SQL guy told us that he could have improved the performance of his program by doing things differently, but as we had reached the expected performance he didn't try to improve his program.

Of course every application is different, so what was right for us may not apply for you.

Erik

hi Erik,

I found your reply is very helpful.

if I have a table with (user_id, user_name.....), another table device(device_id, device_name .....), then I have an association table

user_device(user_id, device_id, ....)

How do I do the hbm mapping ?

In fact, my use case here is that I will have device_id in hand, from there, I would like to find the user_id, eventually I will get other user attributes that I 'd like to know based on user_id, it is a simple join if using JDBC, I don't know how to do it using hibernate for the mapping, it complains the way I mapped is wrong.

? thanks.

hi erik,

I figured that I need to use many-to-many association and composite key.

thanks