-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 26 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Updating only the modified columns
PostPosted: Tue Sep 09, 2003 5:40 pm 
Newbie

Joined: Wed Aug 27, 2003 11:37 am
Posts: 6
Location: Sterling, VA
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


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 09, 2003 6:19 pm 
Pro
Pro

Joined: Tue Aug 26, 2003 1:24 pm
Posts: 213
Location: Richardson, TX
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...


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 10, 2003 12:46 pm 
Newbie

Joined: Wed Aug 27, 2003 11:37 am
Posts: 6
Location: Sterling, VA
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:

Code:
if (dirtyProperties==null) {
     // Interceptor returned null, so do the dirtycheck ourself, if possible
    interceptorHandledDirtyCheck = false;
    cannotDirtyCheck = entry.loadedState==null; // object loaded by update()
    if (!cannotDirtyCheck) {
   dirtyProperties = persister.findDirty(values, entry.loadedState, object, this);
    }
}



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


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 10, 2003 12:57 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
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!


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 10, 2003 1:15 pm 
Newbie

Joined: Wed Aug 27, 2003 11:37 am
Posts: 6
Location: Sterling, VA
I agree.
Much better to issue a bigger SQL update statement than reload the object.

Thanks,
--Mike


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 10, 2003 1:31 pm 
Newbie

Joined: Wed Aug 27, 2003 11:37 am
Posts: 6
Location: Sterling, VA
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


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 10, 2003 2:28 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
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


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 10, 2003 4:52 pm 
Newbie

Joined: Wed Aug 27, 2003 11:37 am
Posts: 6
Location: Sterling, VA
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


Top
 Profile  
 
 Post subject: Dynamic Update
PostPosted: Mon Sep 15, 2003 5:58 pm 
Beginner
Beginner

Joined: Wed Aug 27, 2003 6:44 pm
Posts: 20
>>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.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 15, 2003 9:29 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
Quote:
Hibernate should Update only the columns which is changed ... result in audting conflicts


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).


Top
 Profile  
 
 Post subject: Update Modified columns
PostPosted: Tue Sep 16, 2003 4:25 pm 
Beginner
Beginner

Joined: Wed Aug 27, 2003 6:44 pm
Posts: 20
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)


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 16, 2003 6:07 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
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.


Top
 Profile  
 
 Post subject: Cascade Delete
PostPosted: Tue Sep 16, 2003 6:38 pm 
Beginner
Beginner

Joined: Wed Aug 27, 2003 6:44 pm
Posts: 20
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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 16, 2003 6:52 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
My $.02 ->
I'll take the extra network noise as opposed to the potentially significant increase in application processing time.


Top
 Profile  
 
 Post subject: Dynamic Update
PostPosted: Wed Sep 17, 2003 10:51 am 
Beginner
Beginner

Joined: Wed Aug 27, 2003 6:44 pm
Posts: 20
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 26 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.