-->
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.  [ 4 posts ] 
Author Message
 Post subject: Eager fetching produces 3 queries instead of 1
PostPosted: Sun Jan 31, 2010 9:50 am 
Newbie

Joined: Sat Jan 30, 2010 5:09 am
Posts: 2
Hello I was experimenting with NHibernate and i got little confused with Eager fetching. I created 6 tables with 1-many relationships and I am trying to eager fetch them in 1 database query. In my example I have Products that consists of Parts that consists of Materials that consists of Materials that consists of Materials that consist of Materials (4 tables for materials because I was out of imagination).

Everything works fine if I use HQL:
Code:
IList<Product> products = session.CreateQuery("from Product p left join fetch p.Parts r left join fetch r.Materials m0 left join fetch m0.Materials m2 left join fetch m2.Materials m3 left join fetch m3.Materials").List<Product>();

2010-01-31 14:45:11,455 [9] DEBUG NHibernate.SQL [(null)] -
    select
        product0_.OID as OID0_0_,
        parts1_.OID as OID3_1_,
        materials2_.OID as OID1_2_,
        materials3_.OID as OID2_3_,
        materials4_.OID as OID2_4_,
        materials5_.OID as OID2_5_,
        product0_.Name as Name0_0_,
        product0_.Added as Added0_0_,
        parts1_.Name as Name3_1_,
        parts1_.Quantity as Quantity3_1_,
        parts1_.Expiration as Expiration3_1_,
        parts1_.ProductOID as ProductOID3_1_,
        parts1_.ProductOID as ProductOID0__,
        parts1_.OID as OID0__,
        materials2_.Name as Name1_2_,
        materials2_.Price as Price1_2_,
        materials2_.PartOID as PartOID1_2_,
        materials2_.PartOID as PartOID1__,
        materials2_.OID as OID1__,
        materials3_.Name as Name2_3_,
        materials3_.Price as Price2_3_,
        materials3_.MaterialOID as Material4_2__,
        materials3_.OID as OID2__,
        materials4_.Name as Name2_4_,
        materials4_.Price as Price2_4_,
        materials4_.MaterialOID as Material4_3__,
        materials4_.OID as OID3__,
        materials5_.Name as Name2_5_,
        materials5_.Price as Price2_5_,
        materials5_.MaterialOID as Material4_4__,
        materials5_.OID as OID4__
    from
        Products product0_
    left outer join
        Parts parts1_
            on product0_.OID=parts1_.ProductOID
    left outer join
        Materials materials2_
            on parts1_.OID=materials2_.PartOID
    left outer join
        Materials2 materials3_
            on materials2_.OID=materials3_.MaterialOID
    left outer join
        Materials2 materials4_
            on materials3_.OID=materials4_.MaterialOID
    left outer join
        Materials2 materials5_
            on materials4_.OID=materials5_.MaterialOID


But when I try setting eager fetching in mapping files and call:
Code:
  ISession session = sessionFactory.OpenSession();
   Product product = session.Get<Product>(1);



Then i get 3 selects :(
Code:
2010-01-31 14:47:04,698 [9] DEBUG NHibernate.SQL [(null)] -
    SELECT
        product0_.OID as OID0_3_,
        product0_.Name as Name0_3_,
        product0_.Added as Added0_3_,
        parts1_.ProductOID as ProductOID5_,
        parts1_.OID as OID5_,
        parts1_.OID as OID3_0_,
        parts1_.Name as Name3_0_,
        parts1_.Quantity as Quantity3_0_,
        parts1_.Expiration as Expiration3_0_,
        parts1_.ProductOID as ProductOID3_0_,
        materials2_.PartOID as PartOID6_,
        materials2_.OID as OID6_,
        materials2_.OID as OID1_1_,
        materials2_.Name as Name1_1_,
        materials2_.Price as Price1_1_,
        materials2_.PartOID as PartOID1_1_,
        materials3_.MaterialOID as Material4_7_,
        materials3_.OID as OID7_,
        materials3_.OID as OID2_2_,
        materials3_.Name as Name2_2_,
        materials3_.Price as Price2_2_
    FROM
        Products product0_
    left outer join
        Parts parts1_
            on product0_.OID=parts1_.ProductOID
    left outer join
        Materials materials2_
            on parts1_.OID=materials2_.PartOID
    left outer join
        Materials2 materials3_
            on materials2_.OID=materials3_.MaterialOID
    WHERE
        product0_.OID=@p0;
    @p0 = 1
2010-01-31 14:47:05,199 [9] DEBUG NHibernate.SQL [(null)] -
    SELECT
        materials0_.MaterialOID as Material4_1_,
        materials0_.OID as OID1_,
        materials0_.OID as OID2_0_,
        materials0_.Name as Name2_0_,
        materials0_.Price as Price2_0_
    FROM
        Materials2 materials0_
    WHERE
        materials0_.MaterialOID=@p0;
    @p0 = 1
2010-01-31 14:47:05,238 [9] DEBUG NHibernate.SQL [(null)] -
    SELECT
        materials0_.MaterialOID as Material4_1_,
        materials0_.OID as OID1_,
        materials0_.OID as OID2_0_,
        materials0_.Name as Name2_0_,
        materials0_.Price as Price2_0_
    FROM
        Materials2 materials0_
    WHERE
        materials0_.MaterialOID=@p0;
    @p0 = 1


I have tried to set <property name="max_fetch_depth">10</property> and checked again my mapping files but everything looks fine! Any suggestions?

My mapping files:
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Eg.DO" assembly="Eg">

  <class name="Eg.DO.Product, Eg" table="Products">

    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Added" />
   
    <set name="Parts" lazy="false" fetch="join">
      <key column="ProductOID"/>
      <one-to-many class="Eg.DO.Part, Eg"/>
    </set>

  </class>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Eg.DO" assembly="Eg">

  <class name="Eg.DO.Part, Eg" table="Parts">

    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Quantity" />
    <property name="Expiration" />
    <many-to-one name="Product"  fetch="join" class="Eg.DO.Product, Eg" column="ProductOID"/>
    <set name="Materials" lazy="false" fetch="join">
      <key column="PartOID"/>
      <one-to-many class="Eg.DO.Material, Eg"/>
    </set>
  </class>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Eg.DO" assembly="Eg">

  <class name="Eg.DO.Material, Eg" table="Materials">

    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Price" />
    <many-to-one name="Part" fetch="join" class="Eg.DO.Part, Eg" column="PartOID"/>
    <set name="Materials" lazy="false" fetch="join">
      <key column="MaterialOID"/>
      <one-to-many class="Eg.DO.Material2, Eg"/>
    </set>
  </class>

  <class name="Eg.DO.Material2, Eg" table="Materials2">

    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Price" />
    <set name="Materials" lazy="false" fetch="join">
      <key column="MaterialOID"/>
      <one-to-many class="Eg.DO.Material3, Eg"/>
    </set>
  </class>
<!---->
  <class name="Eg.DO.Material3, Eg" table="Materials2">
    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Price" />
    <set name="Materials" fetch="join" lazy="false" >
      <key column="MaterialOID"/>
      <one-to-many class="Eg.DO.Material4, Eg"/>
    </set>
  </class>

  <class name="Eg.DO.Material4, Eg" table="Materials2">
    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Price" />
  </class>
</hibernate-mapping>
</hibernate-mapping>
</hibernate-mapping>


Top
 Profile  
 
 Post subject: Re: Eager fetching produces 3 queries instead of 1
PostPosted: Fri Feb 05, 2010 1:50 pm 
Newbie

Joined: Sat Dec 26, 2009 7:05 am
Posts: 8
it happend to me once, where the nhibernate debug showed me 2 inserts where only one was needed, and when i checked the DB i've noticed that only one of those inserts was actually executed...

so first of all, check the DB (if you're using sql server, its very simple using the profiler tool, for oracle i guess its possible too, but i don't know how because i useally use the DBAs to do this)
to see if all those selects are really running

i didn't look into all of you're mappings, but it seems strange to me to use one to many mapping and set it as lazy with fetch="join"

using fetch = select\sub select is the right way to go with lazy="true" and its hard for me to see any reason why use fetch=join with lazy loading (why would you want to re-load you're root entity everytime you access a proxy collection?)


Top
 Profile  
 
 Post subject: Re: Eager fetching produces 3 queries instead of 1
PostPosted: Sat Feb 06, 2010 4:17 am 
Newbie

Joined: Sat Jan 30, 2010 5:09 am
Posts: 2
nadavsof wrote:
i didn't look into all of you're mappings, but it seems strange to me to use one to many mapping and set it as lazy with fetch="join"



lazy is false!


Top
 Profile  
 
 Post subject: Re: Eager fetching produces 3 queries instead of 1
PostPosted: Mon Feb 08, 2010 2:25 am 
Expert
Expert

Joined: Thu Dec 14, 2006 5:57 am
Posts: 1185
Location: Zurich, Switzerland
When you use bi-directional associations, you should mark the one-to-many end with inverse="true". I'm not sure if that's the problem here, but it's at least a try. Have you tried using less tables ? Does it work then ?

_________________
--Wolfgang


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 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:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.