[hibernate-dev] Multiple-object batched inserts

[Brett Wooldridge]

I thought his question was a little too technical for the general list, so I
thought I¹d ask it here.  Is there a technical reason why Hibernate does not
or cannot support the batching of multiple (different) object insertions
within the same session?

I have code that is semantically equivalent to:

Session session = sessionFactory.getCurrentSession();
for (int i = 0; i < 1000; i++) {
   Foo foo = new Foo();
   session.save(foo);

   Bar bar = new Bar();
   session.save(bar);
}
session.flush();

If Foo or Bar is not in the equation, batching occurs as desired.  However,
if both Foo and Bar are inserted during the session in alternating fashion
the result is that in the ActionQueue the 'insertion list' is interleaved
with Foo's and Bar's.  This is natural enough it seems.  However, when
ActionQueue.executeActions() is called the following occurs...

1) executeAction() iterates through the insertion list calling execute() on
each Executable in the list
2) the Executable.execute() method calls insert() on the associated
persister, in the case a SingleTableEntityPersister.  It seems to realize
that it CAN batch these inserts and so ...
3) insert() calls the BatchingBatcher.prepareBatchStatement(sql) which is
actually a method on AbstractBatcher.  This is where things go "awry"...

The prepareBatchStatement(sql) does this thing where it compares the SQL
statement that was provided with the _last SQL that it executed_ and if they
match it re-uses the _last PreparedStatement_ which it also hung onto,
otherwise it closes the existing PreparedStatement and creates a new one --
effectively ending the current "batch".

In this case, because the objects are interleaved in the insertion list the
result is that the prepareBatchStatement() is called with:

insert into foo (a, b, c) values (?, ?, ?)
insert into bar (x, y, z) values (?, ?, ?)
insert into foo (a, b, c) values (?, ?, ?)
insert into bar (x, y, z) values (?, ?, ?)
insert into foo (a, b, c) values (?, ?, ?)
insert into bar (x, y, z) values (?, ?, ?)
...

The result being that each subsequent statement terminates the statement
prepared before it, preventing batching from occuring.  Well, it logs a
debug message saying "Executing batch size: 1" for each statement.

So my question is this ... is there any reason not to use a Map<sql,
PreparedStatement> construct in the BatchingBatcher such that the
appropriate PreparedStatement can be located and re-used based on the
incoming SQL rather than only retaining the _last_ SQL statement which is
guaranteed to "flap" in the case of alternating (or multiple) objects?

If there is no technical reason why this shouldn't be done or wouldn't work,
I will be happy to make the change and submit a patch.  [Can you tell how
bad I need batching in this scenario?]

Thanks,
Brett Wooldridge
The ZipTie Project
www.ziptie.org