We are having some real performance problems, due to the large number of records we need to process. One example is where we need to modify a large number (say 10,000) entity beans. Here is sample code using the entity manager:

void moveDogs(List<Integer> dogIds, Owner newOwner)
{
for (Integer dogId : dogIds)
{
Dog dog = em.find(Dog.class, dogId);

dog.setOwner(newOwner);
em.merge(dog);
}
}

To process 5,000 records, the above takes 192 seconds. Doing the exact same thing, using prepared statements and a batch update, takes less than one second (see code below). Obviously I would prefer the former code, but our users just won't stand for that performance. My question is, are there any plans to have this kind of optimization built in to hibernate? (Where it would internally do the batch update).

void moveDogs(List<Integer> dogIds, Owner newOwner)
{
int newOwnerId = newOwner.getId();

try
{
HibernateSession hs = (org.jboss.ejb3.entity.HibernateSession) em;
Session session = hs.getHibernateSession();
Connection connection = session.connection();

PreparedStatement statement = connection.prepareStatement("update dog d set d.owner_id = ? where d.id = ?");

for (Integer dogId : dogIds)
{
statement.setInt(1, ownerId);
statement.setInt(2, dogId);
statement.addBatch();
}

statement.executeBatch();
}
catch (SQLException e)
{
logger.error(e);
}
}

The functionality you want already exists in hibernate; it's called HQL queries.

session.createQuery("...").executeUpdate();

http://www.hibernate.org/hib_docs/v3/re ... ryhql-bulk

The functionality you want already exists in hibernate; it's called HQL queries.

session.createQuery("...").executeUpdate();

http://www.hibernate.org/hib_docs/v3/re ... ryhql-bulk

We tried going that route, but the trick is there is no single query that can do the job. We need to execute an update on a list of IDs passed in. We also tried a query using "where in()" but the maximum number of entries that can handle is 1000, and we are doing upwards of 20,000.

It seems the batchUpdate is the only way we can get it done with one call to the database, which is how we are getting the great performance now.

hibernate automatically does many queries through a batch. Have you tried putting it inside of a transaction and committing only after all your queries have been created?

f.y.i. I've never tried this, but I don't see why it wouldn't work.

Yes, we have. It is just one transaction, but (I think) 5,000 individual update statements if we do it that way. At least that is what I attribute the slow down to.

try either of these two:
1) actually enable jdbc-level batching in Hibernate and then do the iteration code you do in your #1
2) segment the incoming id list into "workable sizes" and then do HQL update statements based on the segmented ids...

For option 1), is this how you enable it?

<property name="hibernate.jdbc.batch_size" value="10000" />

We've tried that, and it didn't change the performance.

For option 2), yes, I think that is a workable (we've done it in another case) and I imagine the performance would be comparable to the prepared statements. Thanks.