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