From the Hibernate Performance Q&A:
"Updating only the modified columns. Hibernate knows exactly which columns need updating and, if you choose, will update only those columns."

I've look through the documentation and I am trying to figure out how to do this.

Perhaps I am mistaken, but this statement leads me to believe that if I have Object X with columns 1 through 250, then an update to column 1 (and only column 1) within a transaction will produce SQL like so "update TABLE_X set COLUMN_1 = ? where PK=?" and, an update to columns 1 and 2 would produce "update TABLE_X set COLUMN_1 = ?, COLUMN_2=? where PK=?", etc. However, in my application I see a SQL statement like this "update TABLE_X set COLUMN_1 = ?, COLUMN_2=?, ... COLUMN_250=? where PK=?"

While this all works fine, I would love to get a (minor, I know) performance boost if possible. So, does anybody know how to enable Hibernate to do this? Or did I just misinterprite the documentation?

Thanks,
--Mike

The dynamic-update and dynamic-insert attributes of the <class> mapping element would be a good start. :)

See section 4.1.3 of the manual, and note the last sentence...

Thanks for the help.

dynamic-insert worked for me on INSERTs, but dynamic-update didn't work for me on UPDATEs. I probed into the source code though and figured out why. Turns out, in order to use dynamic-update on INSERT, hibernate must have already loaded the object into the session for a reason other than update. I base this on these lines of code in SessionImpl:




In our application, we have our own cache that maintains the state of our objects. So, the current hibernate Session has not loaded the object. Thus the cannotDirtyCheck variable is always true and thus dirtyProperties are always null.

So, I guess we can't use this optimization. Oh well. It was worth a try.
Thanks again for your help,
--Mike

Of course, Hibernate has to have a current snapshot of the object. In the latest release of Hibernate, you can have Hibernate fetch a snapshot from the database using select-before-update="true". But this is _highly_ unlikely to perform more efficiently!

I agree.
Much better to issue a bigger SQL update statement than reload the object.

Thanks,
--Mike

While I'm on the subject, I might as well ask another question.

Since dynamic-insert is applicable to our application, I may start using it in places other than a test environment. The documentation warns that this can actually result in poorer performance depending on the situation.

Here's my scenario:
We have some normal sized tables (10-15 columns) and some large tables (60-100) columns. The large tables are hit the most frequently. Updates and Inserts are frequent. The large tables have about 40 columns that are null 85% of the time. So, dynamic-insert seems like a good fit for the larger tables.

What I am wondering is why using the dynamic-insert could result in poorer performance? My assumption is that hibernate (and the database, too) does some sort of caching of the "static" insert statement that can't be performed on the dynamic insert statement. So, there is extra string manipulation at the hibernate level. Is that true?

I ran some real quick informal tests, but can't discern a performance difference (which means its less that 10% either positive or negative) in a low thread environment.

Thanks,
--Mike

the reasons why it might be slower are:

(a) Hibernate has to generate the SQL strings at _runtime_
(b) you might not be able to utilize prepared statements and prepared statement caching as efficiently

ok.
Thanks for answering.

FWIW: I did some better performance testing (low thread count) and determined the dynamic-insert improved performance for certain INSERT-intensive operations by around 4% in our application.

--Mike

>>I agree.
>>Much better to issue a bigger SQL update statement than reload the >>object.

I don't agree on this ,Hibernate should Update only the columns which is changed ,not all the Updateable columns in the object.The will result in audting conflicts.

Hope my point makes sense in the buisness prespective.

How will this cause auditing conflicts? Even performing auditing in the database through triggers can account for this, unless its some crazy kind of DB which does not give you the before and after snapshot of the record to be modified. In Oracle for example, this is as simple as comparing the :new and :old row records for whats has changed (and really you'd have to do this anyway in order to ascertain what has changed to be audited).

But my point why hibernate is sending an Update when the column is not changed.We dont want to check old.value = new.value in all the cases.(i.e In audting)

And that is certainly doable using dynamic-update.

But as for auditing in triggers, how else are you going to know what changed other than checking old values against new values? I don't know what database you use, but in all that I know of you would have to do that by manually checking the column value in the old record versus those in the new. If your DB in some way passes you a list of the columns which changed or were referenced in the update statement, then obviously this would not be neccessary. If it does not, the only way would be to perform that manual checking of the two records, in which case it does not really matter whether Hibernate performs an update with all the columns or just those that changed.

Ok.I understand that.
In my humble opinion the Hibernate should update only the modified columns by default instead of setting it through dynamic-update=true.

My $.02 ->
I'll take the extra network noise as opposed to the potentially significant increase in application processing time.

Steve,
Is it possible that we can tell hibernate to include modified columns in the where clause when it sends the update in addition to the key columns.

for e.g
(Class columns->ID(KEY COLUMN),NAME,SALARY)
If user changes the salary then the Update where clause should be

Update table set salary=$$$ where id=1 and salary=oldsalary(i.e value retrieved from the database)

if this feature is already available then reject this question.

Thx
Ashok