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

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
radic
 Post subject: Enormous amount of select queries
PostPosted: Fri Jul 13, 2007 8:57 am 
Beginner
Beginner

Joined: Tue May 08, 2007 8:26 am
Posts: 21
Hello

I am using Hibernate and I noticed alot of select queries showed up where it is not necassary.

Code:
007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=46
2007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=59
2007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=45
2007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=51
2007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=57
2007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=40
2007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=35
2007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=39
2007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=36
2007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=38
2007-07-13 14:32:13 LOG:  duration: 0.000 ms  statement: select accessrigh0_.accessright_id as accessri1_6_0_, accessrigh0_.accessright_name as accessri2_6_0_ from accessright accessrigh0_ where accessrigh0_.accessright_id=63


This comes from a many-to-many mapping table, with one select on one side of the many-to-many table.
It does these selects for every many to many record in the table.
I can not enter a fetch="join" in my key-many-to-one files so how to solve this?

Mapping files:

Profile:

Code:
<hibernate-mapping>
    <class name="com.rdt.orm.Profile" table="profile">
   <id name="profileId" column="profile_id" type="int">
       <generator class="sequence">
      <param name="sequence">profile_profile_id_seq</param>
       </generator>
   </id>
      
   <set name="profileAccessRights" cascade="save-update, delete" inverse="true" lazy="true"  >
       <key column="profile_id" />
       <one-to-many class="ProfileAccessRight" />
   </set>

        <property name="profileName" column="profile_name" type="string" />
   <property name="profileLevel" column="profile_level" type="integer" />
    </class>
</hibernate-mapping>


ProfileAccessRight

Code:
<hibernate-mapping>
    <class name="ProfileAccessRight" table="profileaccessright">
   <composite-id>
       <key-many-to-one name="profile" class="Profile" column="profile_id" lazy="proxy" />
       <key-many-to-one name="accessRight" class="AccessRight" column="accessright_id" lazy="false" />
   </composite-id>
   
       <property name="profileAccessRightValue" column="profileaccessright_value" type="string" />
    </class>
</hibernate-mapping>


AccessRight

Code:
<hibernate-mapping>
    <class name="AccessRight" table="accessright">
   <id name="accessRightId" column="accessright_id" type="integer">
       <generator class="sequence">
      <param name="sequence">accessright_accessright_id_seq</param>
       </generator>
   </id>
   
        <property name="accessRightName" column="accessright_name" type="string" />
    </class>
</hibernate-mapping>


These are simplified mapping files but they contain all that is needed.

For example when a user logs in, i do this query:

Code:
...
   User tempUser = (User) session.createQuery( "from User u where u.userName = '"+ user.getUserName() +"'").uniqueResult();
   
   if( tempUser != null)
   {
       Hibernate.initialize( tempUser.getProfile().getProfileAccessRights());
   }
...


This works fine but it does around 30 select queries for access rights, do i have to make a seperate query for selecting the accessrights or is there something else?

thanks
Top
 Profile  
 
thatmikewilliams
 Post subject:
PostPosted: Fri Jul 13, 2007 11:45 am 
Expert
Expert

Joined: Fri Jul 13, 2007 8:18 am
Posts: 370
Location: london
Hi radic

Try this: Introduce a surrogate primary key into ProfileAccessRight instead of using a composite key and re-map AccessRight and Profile as ordinary properties. This allows you to set fetch="join" on the many-to-one relationship to AccessRight so it gets loaded in the same query as the ProfileAccessRight.

If you're worried about the uniqueness of the profile/accessRight combination you should add a unique constraint covering this pair of values.

Oh and don't forget to create a new sequence for the new primary key.

Changes detailed below.

Mike


ProfileAccessRight.hbm.xml

Code:
<hibernate-mapping>
    <class name="ProfileAccessRight" table="profileaccessright">
<!-- Don't use a composite id for this.
   <composite-id>
       <key-many-to-one name="profile" class="Profile" column="profile_id" lazy="proxy" />
       <key-many-to-one name="accessRight" class="AccessRight" column="accessright_id" lazy="false" />
   </composite-id>
-->

<!-- Create a surrogate primary key -->
   <id name="profileAccessRightId" column="profileaccessright_id" type="integer">
       <generator class="sequence">
      <param name="sequence">profileaccessright_profileaccessright_id_seq</param>
       </generator>
   </id>

<!-- Map profile as a many-to-one property -->
    <many-to-one
            name="profile"
            class="Profile"
            column="profile_id"
            lazy="proxy" />

<!-- Map accessRight as a many-to-one property -->
<!-- Set fetch="join" to load AccessRight objects when ProfileAccessRight objects are loaded -->
       <many-to-one
            name="accessRight"
            class="AccessRight"
            column="accessright_id"
            lazy="false"
            fetch="join" />

       <property name="profileAccessRightValue" column="profileaccessright_value" type="string" />
    </class>
</hibernate-mapping>



In ProfileAccessRight.java add a private property to hold the surrogate key:

Code:
public class ProfileAccessRight
    private int id;
    @SuppressWarnings("unused")
    private int getId() {
        return id;
    }
    @SuppressWarnings("unused")
    private void setId(int id) {
    this.id = id;
    }
....
Top
 Profile  
 
rajneeshg
 Post subject: Enormous amount of select queries
PostPosted: Fri Jul 13, 2007 11:56 am 
Newbie

Joined: Fri Jul 13, 2007 11:50 am
Posts: 1
I did not see that you have already tried join as fetch strategy. Taking the post back
Top
 Profile  
 
piet.t
 Post subject:
PostPosted: Sun Jul 15, 2007 1:50 pm 
Regular
Regular

Joined: Fri May 12, 2006 4:05 am
Posts: 106
You can reduce the number of sql-statements issued by setting a batch-size on the AccessRight class-mapping. E.g. batch-size="20" will fetch up to 20 AccesRights with a single select.
Top
 Profile  
 
radic
 Post subject:
PostPosted: Mon Jul 16, 2007 5:09 am 
Beginner
Beginner

Joined: Tue May 08, 2007 8:26 am
Posts: 21
Thank you for your input.

I have tried the batch-size but for some reason it would not work, i read the docs about it but for some reason it just kept doing alot of select statement.

I now specify fetch joining inside the hql query and it works, but when I specify it in the mapping files it does not work.
It is a bit strange but the docs did say only 1 fetch="join" is allowed for a select query inside a mapping file.
So maybe that's it.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.