Hibernate @DynamicUpdate(value=true) @SelectBeforeUpdate(value=true) performance

Question

i am start using this 2 hibernate annotations in my APP.

@DynamicUpdate(value=true)
@SelectBeforeUpdate(value=true) 

first i will try to explain what i understand about it to know if i am right about it.

@DynamicUpdate(value=true)

updates only the modified values in the entity Hibernate needs to track those changes

@SelectBeforeUpdate(value=true)

creates a select before update to know which properties has been changed this is useful when the entity has been loaded and updated on different sessions Hibernate is out of tracking entity changes

is this 2 affirmations correct?

my main concern is.

in DB performance which is better or faster updates all the fields in the entity at once or generate a select to know which columns update and update only the modified columns?

Answer 1

The situation depends on your circumstance. If your table is very simple (has no foreign key constraints, only few columns, few indexes), then updating the full record is going to be faster.

If, however, your table has many foreign key constraints and indexes, it will be faster to first select and then update the differences. This is because PostgreSQL has to do the following work for each column in the update:

• Check foreign key constraint
• Update related indexes

Furthermore, the changes add bloat to the tables which must be cleaned up by vacuum.

Keep in mind that if you use dynamicUpdate on a database with many tables, and your updates look very different, you'll start evicting cached query plans. Those plans cost resources to compute fresh. Though, cached plans might only be useful to subsequent queries in the same session anyhow.