A bit of hibernate optimization

The context

Assume you are dealing with a hibernate operation which requires to copy a lot of objects in the database. For instance you are having 2 objects as described in the diagram bellow: budget and entries and you want to duplicate the budget and all it’s entries for some operation. There are several way to do this.

Budget - entries

First approach

In hibernate usual approach this is done most of the time as something similar to:

//duplicate the oldBudgetVersion
//load the old entries
query = session.createQuery("from BudgetEntry as e where e.version.id = :budgetVersionId");
query.setParameter("budgetVersionId", budgetVersion.getId());
List entries = query.list();
for (Iterator i = entries.iterator(); i.hasNext();) {
        BudgetEntry entry = (BudgetEntry) i.next();
	BudgetEntry newEntry = new BudgetEntry();
	newEntry.setAmount(entry.getAmount());
	//set all the other valies
	session.save(newEntry);
}
session.flush();

This is probably ok for a small number of entries but it will become not so efficient for more entries. Consider the number of sql’s:

• load the old budgetVersion (1)
• save the new budgetVersion (1)
• load the old entries (1) and the associated objects which might need referencing (n*?)
• save the new entries (n)

It can sum up to 400 sql’s for around 200 entries.

Second approach

Even if there are not many examples hibernate supports DML style syntax. For our case it can be implemented as:

query = session.createQuery("insert into BudgetEntry(version, structure, amount, ....) select " +
			"v, e.structure, e.amount, ... from BudgetEntry e, BudgetVersion v where e.version.id = :origBudgetVersionId and v.id = :newBudgetVersionId");
query.setParameter("newBudgetVersionId", newBudgetVersion.getId());
query.setParameter("origBudgetVersionId", budgetVersion.getId());
query.executeUpdate();
session.flush();

which sums up to only 1 sql. Note that there a bit of a trick in the join to actually make the new objects link to the proper parent but it’s worth every effort.

Conclusion

In my simple test with around 800 entries the first approach took a bit over 5 minutes while the second a bit under 5 seconds.

Comments:

Jedt Sitthidumrong - Feb 0, 2009

Hi I’m very happy to find you blog post about this. I’m thinking about to do some large import should only use some sql admin tool. But not I’m going to try it again with hibernate ! Jedt S. Bangkok

---

len - Feb 1, 2009

Glad you found it useful.

---