I experience degrading performance when executing
many small database transactions
such as in the following:



The first 100 inserts take approx. 50ms, then the time
increases, up to approx. 1300ms for the last 100 inserts.
The bulk of the time is spent in the line "tx.commit()".
>95% of the CPU time is spent by the Java
interpreter (i.e. not by mysqld).

What could be wrong with the above code / with my
configuration? I'd expect that all inserts should take
approximately the same time.

I use Hibernate 3.1.3, MySQL 5.0.18 (same problem
with HSQL). Person is a simple table with only id and
name, there are no other tables. The above example
is an extract of a much larger application - I'm aware
that in the above example a single large transaction
would suffice.

The debug log mentions a number of other Person entities
during each commit (in addition the single one that
actually should be inserted), but I do not know if this might
be related to the problem:

6 Mrz 2006 13:01:29 DEBUG: listing entities:
26 Mrz 2006 13:01:29 DEBUG: Person{name=Person136, id=41640}
26 Mrz 2006 13:03:54 DEBUG: Person{name=Person824, id=42666}
...
26 Mrz 2006 13:01:29 DEBUG: more......

Thanks in advance and best regards,
Guido

You're not clearing the new Person objects out of the session, so with each commit I think that Hibernate is checking all of the previous Person objects to see if they have any changes that need to be flushed down to the database. Try adding a call to hibernateSession.clear() after the commit.

Thanks a lot - if I add Session.clear() the inserts indeed
all take the same time.

So I only have to see where are the
best points in the code to clear the session
(I have lots of methods interacting with Hibernate
and using objects handled by Hibernate
but want to hide Hibernate itself from the users of the
methods).

Yep - you could also think about controlling your session 'scope' instead of clearing it or just evicting specific objects from the session when you are done with them. Take a look here: http://www.hibernate.org/42.html for a good discussion of a session as a 'unit of work'.

Cheers,
Rich.