Dear all,

I was told Hibernate 3.x will support bulk insert.

Right now I have hibernate 3.0.5 installed. I want to do an experiment to prove bulk insert works on my system.

I am new to hibernate. Is there a simple way to prove this?

When people say "bulk insert", what does this mean? Multiple insert statements within one transaction? How to implement this using hibernate?

thanks,

xiuping

Um.

I guess you need to tell us what bulk inserts means to you. And then we can tell you how to do it in Hibernate 3.1.

Hi,

I don't exactly know. There used to be a rumor that Hibernate can not support bulk insert however jdbc can.

I need to prove hibernate is able to handle bulk insert.

However, I am not exactly clear what is "bulk insert". In your past experience, what usually is the case of "bulk insert"?

Multiple insert statements inside one prepare statement maybe? I maybe wrong. Please advise.

thanks

xiuping

If that is what you want to do, use StatelessSession in HB 3.1.


Or, follow these instructions for HB 3.0:

http://www.hibernate.org/hib_docs/v3/re ... ch-inserts

thanks, Gavin.

do I need to turn on the following line in my hibernet.cfg.xml or some where?

hibernate.jdbc.batch_size 20

Any additional configuration need to be done other than the above?

I have another question:

Does this mean that the bulk insert size is only 20 each time (i.e., 20 insert statements within one session). Can the batch size be set to as big as 10,000 or only within 10-50?

Is this what hibernet 3.1 supported? I am sorry for my ignorance.

thanks,

xiuping

Gavin,
I am new to hibernate as well. I did read the reference .pdf but I am getting some serious performance difference if I use JDBC straight with java assist based transforms and using Hibernate to populate the results.

I my own version of OR mapping tool (which is not as sophisticated as Hibernate but is fast) I create javassist transforms. So essentially I create a class which has code like this:

public void exportObject(PreparedStatement preparedStatement, Object o) {
statement.setString(1, dataObject.getFirstName());
statement.setString(1, dataObject.getMidName());
statement.setString(1, dataObject.getLastName());
statement.setInt(1, dataObject.getAcctNum());
// ... and so on
}

Using this approach I am getting nearly 4-5 times better performance while writing objects to a table. Is there something that I am missing in hibernate. I will keep scouring the forums but if you do know how to solve this please post.

I have already set the following in the cfg file:
<property name="hibernate.jdbc.batch_size">20</property>
<property name="hibernate.cache.use_second_level_cache">false</property>

Thanks,
kd

hi Gavin and kd,

I found out one more interesting fact lunch time:

0) I tried what Gavin suggested, from the debugging log, I can tell it indeed doing batch mode !!! one prepare statement for insert, then re-use the prepare statement to add batch insert, one execute for all the insert, this is great !!! I am using hibernate 3.0.5

1) However, I noticed something that I don't know how to solve:

since I am using oracle, the sequence number I am generating, each time it requires one prepare statement to generate the next sequence number, and then close; then prepare again, close prepare statement again; so on and so forth.

It is ironic to me because the insert has achieved the batch mode; however, the sequence generation here still do it one by one, it screwed up the batch insert. With this behavior of oracle sequence gen, can we still say it is batch mode? Is there any approach to improve it?

thanks,

xiuping

Use seqhilo

<generator class="seqhilo"?

Inside oracle, do I need to create seqhilo? using "create seqhilo MY_SEQHILO_ID" statement?

I will try it tonight. Did you see I proved bulk insert working on hibernate 3.0.5, this is wonderful.

thanks a lot, Gavin,

xiuping

I'm interested in the Stateless Sessions in Hibernate 3.1, been searching all around for some documentation, examples to see if will suit my needs, any pointers? thanks.

Oh, my God !

I just bought and reading a book "Hibernate in Action" and I saw the name "Gavin King", I just realized that Gavin who replied my first post here is the author of the book!

I am still doing the experiment on batch insert, will discuss the result later.

My understanding of a bulk insert operation is different from a batch insert operation. A bulk insert operation is that a ** single ** DML such as HQL or SQL will result in zero or multiple records inserted into a table.

Hibernate 3.0.5 doesn't seem to support bulk insert operation, although bulk update and delete operations are supported.

However, there is a work-around to make bulk insert operations work in Hibernate 3.0.5:

http://beanlib.sourceforge.net/Hibernat ... readme.txt
http://beanlib.sourceforge.net/Hibernat ... ert/3.0.5/

Is there a better way ?

INSERT *is* supported in HQL, just as SELECT, UPDATE, and DELETE are.

although only the INSERT ... SELECT ... form is supported (i.e. not INSERT .... VALUES ...)

Supported in Hibernate 3.0.5 ? This is contrary to the experiements I did, which thrown an exception basically saying INSERT is not allowed in the HQL.