We are working to improve performance of our import. We are inserting 10,000 objects at a time.

Hibernate is creating a series of insert statements, one for each object. The MySQL tuning book suggest using a single multi row insert to improve performance.

Is there any way to get Hibernate to produce one insert statement for the set of 10,000 objects, rather than 10,000 individual insert statements?

For example:

our java code looks like this:

session = currentSession();
transaction = session.beginTransaction();

Iterator iter = collection.iterator();

while ( iter.hasNext() )
{
Object obj = iter.next();
session.save(obj);
}
transaction.commit();

currently the generated sql looks like this:

insert into table_x (col1, col2, col3) values (1, 2, 3);
insert into table_x (col1, col2, col3) values (2, 2, 3);
insert into table_x (col1, col2, col3) values (3, 2, 3);
insert into table_x (col1, col2, col3) values (4, 2, 3);


we would like to get something like this:

insert into table_x (col1, col2, col3) values (1, 2, 3), (2, 2, 3), (3, 2, 3),
(4, 2, 3);

Any suggestions?

Well, the first thing you'd have to do would be to rewrite your DBMS. That's not valid SQL. There is no way to add more than one row with the INSERT VALUES command. Plain old SQL can only add multiple rows by using the INSERT SELECT or SELECT INTO syntax, neither of which help when the data you're inserting isn't already in the DB.

Some DBMSs have support for bulk insert: SQLServer can import any number of rows from a file, for example. But you'd have to use JDBC to take advantage of that, if your DBMS has it.