Hibernate : dynamic-update dynamic-insert - Performance Effects

Question

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 1

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

Answer 2

I think many indices also slow down updates and inserts, so, beside large columns, dynamic-update should be good for tables with great width/content per row and many indices. You know, in "real life", databases aren't always with small, normalized tables...

Rebuilding indices on large tables may take much longer than the overhead for creating and parsing SQL queries.