1

I come across this link - Update single field using spring data jpa on search

In My application, one table is displayed in the front-end which has 100 columns, where user changes approximately 5 to 10 columns max.

However the front-end sends all the values and back-end update query has 100 columns in the SET.

Is this is a best practice? Some says - SET with all the columns doesn't impact as the JPA will do delete and insert internally or the DB does it. Is this is true?

What should be the best practice and does having all columns in the SET affects the performance in general?

Thanks

Spring Dev
  • 15
  • 4

1 Answers1

0

If the user has changed just columns and it is one row updated, then no, the performance would not be affected much. It would be affected, but in most cases optimizing that performance is not necessary unless you're handling a huge amount of updates. And when you're using JPA i would guess you do not actually populate the update yourself but using an entity where you update the affected fields? Then JPA would chose how to actually do the update (most probably sending all fields of the entity to the update).

If it would be 100 rows and the user changes data in 5-10 rows, then it would be better to only pass those 5-10 rows to the database update.

Tomas F
  • 7,226
  • 6
  • 27
  • 36
  • @Thomas F - How about adding DynamicUpdate - https://www.baeldung.com/spring-data-jpa-dynamicupdate ? Help me with your comments – Spring Dev Aug 06 '21 at 08:21
  • Looks interesting, it seem to be doing what you wish right? Then you just need to add that one to your entity. – Tomas F Aug 06 '21 at 08:25
  • JPA is a bit tricky though, it might be that you need to read the entity in question from the DB first, update the fields and then call save. So you must consider if reading the full entity from the DB is worth it compared to writing a custom update query to only update the columns that you wish... Are you certain you need this performance? A normal update is usually quite quick. – Tomas F Aug 06 '21 at 08:27
  • Does the above dynamic update - reads and write it? Does JPA reads and write its instead of Just update? Please kindly explain in detail. I am interested to know how it works – Spring Dev Aug 06 '21 at 08:53
  • In order to figure out which fields that are actually updated then the data need to be read somehow. It does not happen in the DB - so a query must be executed first to fetch the data you're interested in changing. Either this is done by you, by first reading the entity, changing the fields, then save - or automatically by spring (by figuring out which row you're interested in changing by using the entity id and reading the entity without you noticing). If you're interested to see the actual queries that are executed you can set the property spring.jpa.properties.hibernate.show_sql=true – Tomas F Aug 06 '21 at 09:00
  • So you mean DynamicUpdate will do a read and write because it has to compare? Is my understanding is correct? – Spring Dev Aug 06 '21 at 09:11
  • Yes, that is my qualified guess. I have not tried it. If you need to confirm it then I recommend that property for showing the sql. – Tomas F Aug 06 '21 at 09:14
  • If you read that link you sent about dynamic update, it confirms it. "In order to find out the changed columns, Hibernate needs to track the state of the current entity. So, when we change any field of an entity, it compares the current and the modified states of the entity. This means that @DynamicUpdate has a performance overhead associated with it. Therefore, we should only use it when it's actually required." – Tomas F Aug 06 '21 at 09:17
  • If DynamicUpdate works as read and update. How does the normal JPA does the update when DynamicUpdate doesn't added? Does it selects and updates or Updates all the columns? – Spring Dev Aug 07 '21 at 08:45
  • Normally it reads the entity and then an update on all columns. There are ways to get around it, for instance trying to implementing the Persistable interface, then you can control if you want to do an insert or update by using the isNew method on that interface. – Tomas F Aug 09 '21 at 08:52
  • Then what is the difference between Normal JPA update vs DynamicUpdate if that is the case. – Spring Dev Aug 10 '21 at 05:27
  • That with DynamicUpdate you can choose which columns to update, normal update sends all columns for update even when they are not changed. – Tomas F Aug 10 '21 at 05:36