CODEWITHSUNDEEP
javamysqlhibernate
skelwa
skelwa

Reputation: 585

Hibernate @DynamicUpdate

We have a table say 'A' in our system that has around 120 columns (I know that the table is not normalised and table normalisation exists as a backlog item in our roadmap).

Currently, we are facing a problem where multiple threads while updating same table row overwrite some of the existing updated columns. We do not have dynamic update annotation on our table entity.

I was thinking of adding that annotation on the table as that can solve our problem as different threads work on different columns of the table and if the sql is generated at runtime to only update those columns then it will not overwrite any update done on other columns by different thread.

But I read that hibernate caches insert, update sql for table with all columns in case dynamic update is not there.

So, will using @DynamicUpdate be actually beneficial here or the cost of generating dynamic sql at runtime would cause performance slow-down?

The table in question has millions of records and an insert or update happens every other second (updates being more frequent).

Upvotes: 1

Views: 1448

Answers (1)

Amith Kumar
Amith Kumar

Reputation: 4874

I will definitely recommend @DynamicUpdate to you. And let me explain you why:

a) DB Performance: By lowering the number of columns to update, you are cutting down DB server overhead to check referential integrity & constraints for each column(Primary Key, Foreign key, Unique key, Not Null etc), and to update respective indexes comprising the updated column.

b) You are only trading off with rebuilding cached Query plan everytime, but for multi-threaded environment remember it will not make the difference, as cached query plans are only valid till given session life and thus only useful for consecutive udpates in the same DB session.

Upvotes: 2

Related Questions