Hibernate : dynamic-update dynamic-insert – Performance Effects

Using dynamic-update or dynamic-insert has positive, though generally slight only on performance, as also mentioned by http://www.mkyong.com/hibernate/hibernate-dynamic-update-attribute-example/

But the reference documentation mentions that this could have negative performance effects also as mentioned below in http://docs.jboss.org/hibernate/core/3.3/reference/en/html/mapping.html#mapping-declaration-class :

Although these settings can increase performance in some cases, they can actually decrease performance in others.

Can anybody please suggest some example/scenario mentioning negative performance impact of the same?

Answer

Hibernate caches the actual INSERT/SELECT/UPDATE SQL strings for each entity and the obvious benefit is that it doesn’t have to compute the SQL when you want to persist, find or update an entity.

However, when using dynamic-insert or dynamic-update, Hibernate has to generate the corresponding SQL string each time and there is thus a performance cost on the Hibernate side.

In other words, there is a trade-off between overhead on the database side and on the Hibernate side.

My point of view is that dynamic insert and dynamic update can be interesting for tables with a fat blob column or tables with a huge number of columns. In other cases, I’m not convinced that dynamic insert or update always means performance boost (I do not use them by default). But as always, you should measure it.

See also

• Re: Request for dynamic update-SQL for some feedback from the Hibernate developers