-->
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.  [ 8 posts ] 
Author Message
 Post subject: One to many set performance problem
PostPosted: Mon Jun 07, 2004 4:23 pm 
Senior
Senior

Joined: Sun Oct 26, 2003 5:05 am
Posts: 139
Hi, I'm currently using spring+hibernate+postgres and I have a pretty severe performance problem when store/updating many-to-many relationships (but I'm just editing one side of the relation).

Since I'm using spring, I did a execution time test and see that the costs for updating these relationships is very high. Even for updating 8 or 9 relations during a single call to getHibernateTemplate().store(), it takes 500-600 milliseconds. When I did 15-20, it went to 1.2-1.6 seconds. This is absolutely unexceptable for my application, especially since this is a pentium 4 3ghz with over a gig of ram. Obviously I'm doing something very, very wrong :/

Here is the sql being generated during the update. It's surrounded by two log statements that my webforms framework generates. Note that my framework, while a lot more sophisticated than spring mvc, is not carrying that much of the overhead. In fact, I tested the execution time outside of the web app and it's exactly the same (or basically less than 1 milliseconds of difference between the two).

Code:
16:10:43,815  INFO WebFormControllerDelegate:146 - Processing Form Bean for com.upfactor.cms.web.security.UserForm
16:10:43,846 DEBUG SQL:237 - update user_account set firstName=?, lastName=?, username=?, password=?, sessionId=?, userRoleId=?, defaultLanguageId=?, createdOn=?, isActive=? where id=?
16:10:43,877 DEBUG SQL:237 - update session set sessionId=?, createdOn=?, isActive=? where id=?
16:10:43,908 DEBUG SQL:237 - update user_role set name=?, level=?, createdOn=?, isActive=? where id=?
16:10:43,924 DEBUG SQL:237 - update user_privilege set name=?, description=?, createdOn=?, isActive=? where id=?
16:10:44,112 DEBUG SQL:237 - delete from user_privilege_relation where userId=? and userPrivilegeId=?
16:10:44,127 DEBUG SQL:237 - insert into user_privilege_relation (userId, userPrivilegeId) values (?, ?)
16:10:44,377  INFO WebFormControllerDelegate:154 - Finished Processing Form Bean for com.upfactor.cms.web.security.UserForm in 562 milliseconds.


It's fairly easy to see where the problem is. It's deleting all the many-to-many relations and then it's inserting them again. No matter what I do to set the collection in the domain object, it always wants to delete/insert them all rather than the ones it wants to. Even if it does want to delete/insert them all, I can't see 10 of each operation take this much time considering the session/connection is still open during all of this.

That leads me to believe one of three things:
- some optimization in my configure or mapping is wrong
- spring is the bottleneck
- hibernate doesn't do many-to-many mappings very well.

I'm more inclined to think that it's the first one, so I want to ask the experts. In that spirit, I'm going to show you the mapping documents for these domain objects (User, UserGroup and UserPrivilege) - the rest is really of no importance I would think.

Code:
   <class name="com.upfactor.cms.domain.security.User" table="user_account">
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">user_account_id_seq</param>
         </generator>
      </id>
      <property name="firstName" column="firstName"/>
      <property name="lastName" column="lastName"/>
      <property name="username" column="username"/>
      <property name="password" column="password"/>
      <many-to-one name="session" column="sessionId" class="com.upfactor.cms.domain.security.Session"/>
      <many-to-one name="role" column="userRoleId" class="com.upfactor.cms.domain.security.UserRole"/>
      <many-to-one name="defaultLanguage" column="defaultLanguageId" class="com.upfactor.cms.domain.i18n.Language" cascade="none"/>
      <set name="groups" table="user_group_relation" lazy="true">
         <key column="userId"/>
         <many-to-many column="userGroupId" class="com.upfactor.cms.domain.security.UserGroup"/>
      </set>
      <set name="privileges" table="user_privilege_relation" lazy="true">
         <key column="userId"/>
         <many-to-many column="userPrivilegeId" class="com.upfactor.cms.domain.security.UserPrivilege"/>
      </set>

      <property name="createdOn" column="createdOn"/>
      <property name="isActive" column="isActive"/>
   </class>

   <class name="com.upfactor.cms.domain.security.UserGroup" table="user_group">
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">user_group_id_seq</param>
         </generator>
      </id>
      <property name="name" column="name"/>
      <property name="level" column="level"/>

      <property name="createdOn" column="createdOn"/>
      <property name="isActive" column="isActive"/>
   </class>

   <class name="com.upfactor.cms.domain.security.UserPrivilege" table="user_privilege">
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">user_privilege_id_seq</param>
         </generator>
      </id>
      <property name="name" column="name"/>
      <property name="description" column="description"/>

      <property name="createdOn" column="createdOn"/>
      <property name="isActive" column="isActive"/>
   </class>


Thanks so much for helping me out with this. I'm not an expert at databases, just good at higher-level architectural things like frameworks and so on. Details like this drive me nuts =) Thanks so much.

Best Regards,
Ken Egervari


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 07, 2004 4:27 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
You are throwing away the collection and replacing it with a new one, aren't you?

Don't do this.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 07, 2004 4:33 pm 
Senior
Senior

Joined: Sun Oct 26, 2003 5:05 am
Posts: 139
Actually, I just put cascade="none" on the sets and the role, which I did on many of the other mappings but I just forgot them here. Anyway, that wasn't causing the bottleneck so I'm still in trouble. Here was the latest run of the update with 8 user privileges being set on the user.

16:30:21,908 DEBUG SQL:237 - update user_account set firstName=?, lastName=?, username=?, password=?, sessionId=?, userRoleId=?, defaultLanguageId=?, createdOn=?, isActive=? where id=?
16:30:21,955 DEBUG SQL:237 - delete from user_privilege_relation where userId=? and userPrivilegeId=?
16:30:22,221 DEBUG SQL:237 - insert into user_privilege_relation (userId, userPrivilegeId) values (?, ?)
16:30:22,455 INFO WebFormControllerDelegate:154 - Finished Processing Form Bean for com.upfactor.cms.web.security.UserForm in 547 milliseconds.

As you can see, it's still fairly hefty :/


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 07, 2004 4:59 pm 
Senior
Senior

Joined: Sun Oct 26, 2003 5:05 am
Posts: 139
Actually, it is the same object, which strikes me as weird. I'm actually doing this mapping generically since like Hibernate does mapping between program domain objects and the database, my mvc maps all controls to program domain objects without knowledge of the database (just the reference data in spring). Since most of the reference data is coming from hibernate anyway, I would imagine that all elements in memory would be considered identical. After looking at my strategy for mapping reference data to collections, I see that the domain collection object is also the same instance too.

Anyway, I've tried many methods without changing the reference of the collection instance. Could you give a template of code that does it properly?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 07, 2004 6:05 pm 
Senior
Senior

Joined: Sun Oct 26, 2003 5:05 am
Posts: 139
Well, this is what I ended up going with:

Code:
      // Delete entries that the user de-selected.
      Iterator i = domainCollection.iterator();
      while( i.hasNext() ) {
          IdentityObject domainObject =
              castObjectToIdentityObject( i.next() );

         // If this domain object is no longer selected, remove it.  We have to
         // use i.remove() instead of domainCollection.remove() because it'll
         // throw a concurrency exception, which happens when you delete elements
         // out of a collection without the iterator knowing.
         if( !idWasSelected( domainObject.getId(), selectedIds ) ) {
            i.remove();
         }
      }

      // Add the new entries that were not already present, skipping the ones that
      // were not changed at all.
      i = referenceCollection.iterator();
      while( i.hasNext() ) {
         IdentityObject referenceObject =
             castObjectToIdentityObject( i.next() );
         if( idWasSelected( referenceObject.getId(), selectedIds ) ) {
            if( !objectExistsInDomainCollection( referenceObject, domainCollection ) ) {
               domainCollection.add( referenceObject );
            }
         }
      }



Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 08, 2004 1:38 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
What on earth are you doing? This is just waay to complex!

Sounds like maybe you got equals()/hashCode() problems. But impossible to tell w/o more information.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 08, 2004 8:44 am 
Senior
Senior

Joined: Sun Oct 26, 2003 5:05 am
Posts: 139
I know that's what it is, but since this is generic code, I can't assume they will work all the time and I'm not even sure I want to. The good thing is that in my framework, you just specify the expression to the object and it's control type (selectone, selectmany, etc. in these mapping cases) and the appropriate mapping strategy will be used (domain object, collection or map). It basically does the mapping between user interfac controls and domain objects for me and is part of my mvc. If you saw how expressive and easy it is to do very hard things, you'd actually like it as I wanted hard mappings to be as easy to do as simple string or long mappings. Anyway, that code was just a snippet from the collection mapping strategy. It improved my speed by 2000% thanks to your suggestions.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 08, 2004 9:21 am 
Senior
Senior

Joined: Sun Oct 26, 2003 5:05 am
Posts: 139
I'm not sure you can use equals either because it's a mapping between domain -> long array -> reference data too


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

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.