Hibernate version:
3.1.2

Name and version of the database you are using:
Oracle 10g

A little background:

We would like to do batching on inserts. We find this works terrific in our app as long as we are processing the same sql statement consecutively.

However, if we are trying to use batching while inserting two different objects at the same time, the batching becomes inneffective- due apparently to the requirement that a JDBC batch must basically be made up of the same type of SQL statements.

For example, with a batch size of 4, we insert:

Dog 1
Dog 2
Dog 3 <-- Hibernate executesBatch here
Cat 1
Cat 2 <-- Hibernate executesBatch here
Dog 4 <-- Hibernate executesBatch here
Cat 3 <--Hibernate executesBatch here

We see that we never were able to fill up a full batch of 4 items, because the batch is executed everytime the insert statement changed.

I'm curious if anyone else has run into this and has a work around or if there is just some hibernate configuration I am missing? I've done a fair bit of searching but haven't come up with much so far.

What we have done for now, is save up all the Dogs and Cats in separate collections, and then run through the collections at the end of processing to save. However, this requires a lot more memory for us and I don't think it's really a great solution.

After read your post:

I want to ask you when your Dog and Cat object is insert into DB? There are inserted together?

Or some is inserted at diferent moment? I want to say, yo can do a "submit" to insert a Object or many objects. So, what kind of scene do you mean?

I alternate saving Dogs and Cats. For example:



So I alternately save Dog's and Cat's, which effectively disallows Hibernate to take advantage of batch inserts. It looks like AbstractBatcher could be modified to keep a map of open prepared statements instead of just one, and allow batching for this type of situation. (maybe a new MultipleStatementBatcher subclass?)

I was just curious if either 1) I'm missing something, 2) someone else found a work around, or 3) I could get some guidance from the Hibernate folk on the best way to go about adding this type of feature into Hibernate, if in fact it doesn't exist.

Thanks,

Erik

First, you should know that Hibernate isn't designed to do batch update/insert, even the authors of H3 say that.

Basically, the thing is : you have to do a session.flush() and session.clear() regularly to prevent your app from crashing with an OutofMemoryError or kind of. In fact, you know that every entities that you save() are cached in the session (also called first level cache), so if you don't flush it regularly, you runs out of mem.

However, there're some best practices to follow, that Gavin King gave on the blog and is now part of the ref doc. Have you read this :
http://www.hibernate.org/hib_docs/v3/re ... batch.html
and this :
http://blog.hibernate.org/cgi-bin/blosx ... find&path=

Thanks, I have seen those before.

Memory management is not a problem for me and not the point of this thread. My question/issue is with how Hibernate deals with JDBC batching.

Java may not be the best place to do the bulk data processing, but that's not a choice I have right now. Also, in my case, we do a lot more with this data than simply transform it, I can't even imagine trying to push all of our business logic into stored procedures. We've been able to use Java and Hibernate very effectively so far, and I feel that hibernate has saved us a lot of development time.

I'm surprised Hibernate doesn't already handle batching with multiple statements, which makes me feel like I might be missing a config option somewhere.

After changing my app so the SQL statements were grouped by type so JDBC batching worked properly through Hibernate (i.e. all dogs saved at once, then all cats saved at once), throughput increased by roughly 230%. This is on an application we have already spent weeks tuning, and was an extremely significant improvement for us. However, our "work around" was rather hack-ish, it would be nice to have built in Hibernate support for multiple statement batching.

No comments from any devs? Seems like an interesting issue here.

It looks like multiple prepared statement caching is only used on Queries, and not on an Update/Inserts. Is this a correct observation? The Oracle JDBC driver can do update caching behind the scenes, but again, the way Hibernate uses prepared statements it effectively disables it in this situation.