-->
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.  [ 1 post ] 
Author Message
 Post subject: Random inserts/deletes on a select
PostPosted: Fri Sep 15, 2006 11:36 am 
Beginner
Beginner

Joined: Tue Apr 19, 2005 9:39 am
Posts: 45
I have 3 tables {Group, FeatureAccess, Feature}. The Group table and the Feature table have a many-to-many relationship, and per Database Design, I created an associative table that makes the relationships one-to-manies.

Okay without going into too much detail, here is the concept. An User can be part of MANY Groups and a Group has Many Features (this relationship is
Group (1) -----> (1..*) FeatureAccess (1..*) <----- (1) Feature

FeatureAccess has attributes Group, Feature, and access (int)

I want to be able to do group.getFeatureAccess() and feature.getFeatureAccess()

Here are my mapping files:

Code:
   Group.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

   <class name="com.saic.ide.group.Group" table="idegroup">
      <id name="id" column="groupID" type="long">
         <generator class="native"/>
      </id>   
      <property name="groupName" column="groupName"    type="string"/>
      <property name="description" column="groupDescription" type="string"/>
      
      <set name="features" table="featureaccess" lazy="false">
          <key column="groupID"/>
          <composite-element class="com.saic.ide.menu.feature.FeatureAccess">
              <property name="access" type="int" not-null="true"/>
             <many-to-one name="feature" class="com.saic.ide.menu.feature.Feature" lazy="false" column="featureID"/>
          </composite-element>
      </set>
   </class>
   
</hibernate-mapping>



Code:
Feature.hbm.xml


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

   <class name="com.saic.ide.menu.feature.Feature" table="feature">
      <id name="id" column="featureID" type="long">
         <generator class="native"/>
      </id>   
      <property name="visible" column="visible"    type="boolean"/>
      <property name="iconPath" column="iconPath"    type="string"/>
      <property name="feature" column="name"   type="string"/>
      <property name="forward" column="forward"   type="string"/>   
      <many-to-one name="menu" column="menuID" class="com.saic.ide.menu.Menu" not-null="false" lazy="false"/>   
      <!--<set name="groups" table="featureaccess" lazy="false">
         <key column="featureID"/>
         <many-to-many class="com.saic.ide.group.Group" column="groupID"/>
      </set>
      -->
      <set name="accessGroups" table="featureaccess" lazy="false">
          <key column="featureID"/>
          <composite-element class="com.saic.ide.menu.feature.FeatureAccess">
              <property name="access" type="int" not-null="true"/>
             <many-to-one name="group" class="com.saic.ide.group.Group" column="groupID" lazy="false"/>
          </composite-element>
      </set>
      
   </class>



Code:

User.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

   <class name="com.saic.ide.user.User" table="user">
      <id name="id" column="userID" type="long">
            <generator class="increment"/>
      </id>
      <property name="firstName" column="firstname"   type="string"/>
      <property name="lastName"  column="lastname"   type="string" />
      <property name="middleName"  column="middle"   type="string" />
      <property name="email"  column="email"   type="string" />
      <property name="title"  column="title"   type="string" />

      <set name="groups" lazy="false" table="usergroup">
         <key column="userID"/>
         <many-to-many class="com.saic.ide.group.Group" column="groupID"/>
      </set>
   
   </class>
   
</hibernate-mapping>



When hibernate retrieves a User there are several random inserts and deletes they look like:

Code:
Hibernate: select this_.groupID as groupID5_0_, this_.groupName as groupName5_0_, this_.groupDescription as groupDes3_5_0_ from idegroup this_
Hibernate: select features0_.groupID as groupID0_, features0_.access as access0_, features0_.featureID as featureID0_ from featureaccess features0_ where features0_.groupID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: delete from featureaccess where groupID=? and access=?
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)


I did some research and it says Hibernate sometimes does brute force type retrieves but the blog was on bags and idbags. All these random inserts and deletes are killing performance. And as more elements are added to the FeatureAccess table these random inserts/deletes increase proportionally.

Is there something wrong with my mapping files? Should I use a different collection type?

Do you have any advice. I will be more than happy to give you more detail if you need it.


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

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.