Lazy loading ends up fetching 3900 rows?!

aarnott · **Joined:** Fri May 13, 2005 4:08 pm **Posts:** 64

I have a 22 table database (that I am only able to wrap my mind around because of NHibernate. Thanks guys) that uses lazy loading collections and entities pretty much everywhere. This works pretty well...

But sometimes a couple of HQL queries seem to make NHibernate go on crazy on downloading thousands of unneeded rows. Consider this code:

Code:

System.Collections.IList list =
   session.CreateQuery("from Evaluation e where e.Participant = ? and e.IsDeleted = 0 and e.EndDate is not null and e.Questionnaire is not null") 
   .SetParameter(0, p)
   .List();

Instead of just retrieving the 0 to very few rows I'm asking for, NHibernate gets row after related row until about 3900 rows are retrieved, and I usually end up getting timeout errors :(. Can anyone tell me why this might be happening? I realize you may need to see some mapping files, log output, or SQL Profile traces. Just let me know what is most helpful. I'd make a test case, but this problem only shows up on my 22 table project.

Thanks in advance!

KPixel · **Posted:** Sun Oct 30, 2005 11:49 am

The query "from Evaluation e [...]" will obviousily return Evaluation entities (with non-lazy relationships).

In the 3900 retrieved rows, is there some that NHibernate should not load? (because they should be lazy)
If you find this kind of rows, then give more details about them and their relationships with Evaluation...

aarnott · **Joined:** Fri May 13, 2005 4:08 pm **Posts:** 64

In a (late) response to KPixel, I am still facing this problem, and I have simplified the scenario (somewhat), and I present more info here:

A very simple call to ISession.Get(typeof(Evaluation), 69315) causes 7 tables to be queried instead of just the one table whose entity I am requesting. All relationships and entities have lazy="true", so I don't know why these entities are loading. I captured a log4net file for just the specific query, and it's 252KB, so I'll send it to you if you ask for it.

Meanwhile, here are relevant snippets of the mapping files that include relationships that are being retrieved. It may seem overwhelming, but I hope it provides you with whatever little info you need from them to help me determine what's wrong. Thanks in advance.

class Evaluation:

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" default-cascade="all-delete-orphan">
   <class name="Byu.Fhss.Sfl.RelateInstitute.Logic.Surveys.Evaluation, RelateInstituteLogic" table="evaluation" lazy="true">
      <cache usage="read-write"/>
      <id name="Id" column="evaluationId" unsaved-value="0">
         <generator class="native"/>
      </id>
      <many-to-one name="Questionnaire" column="questionnaireId" class="Byu.Fhss.Sfl.RelateInstitute.Logic.Surveys.Questionnaire, RelateInstituteLogic" cascade="none" />
      <one-to-one name="FollowUpEvent" property-ref="EventEvaluation"/>
      <many-to-one name="Participant" column="participantId"/>

      <many-to-one name="Partner" column="partnerId" access="field.camelcase" class="Byu.Fhss.Sfl.RelateInstitute.Logic.Accounts.Participant, RelateInstituteLogic"/>
      <many-to-one name="PartnerEvaluation" column="partnerEvaluationId" access="field.camelcase" class="Byu.Fhss.Sfl.RelateInstitute.Logic.Surveys.Evaluation, RelateInstituteLogic"/>
      
      <many-to-one name="OrderDetail" column="orderDetailId"/>

      <map name="ResponsesSet" table="response" lazy="true">
         <cache usage="read-write"/>
         <key>
            <column name="evaluationId" unique-key="response_idx"/>
         </key>
<!-- this relationship was not queried, so I don't include detail here -->
      </map>

      <set name="FollowUpsSet" inverse="true" lazy="true">
         <cache usage="read-write"/>
         <key column="evaluationId"/>
         <one-to-many class="Emar.FollowUp.EvaluationTracker, Emar3"/>
      </set>

   </class>
</hibernate-mapping>

class EventTracker

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" default-cascade="all-delete-orphan">
   <class name="Emar.FollowUp.EventTracker, Emar3" table="eventtracker" lazy="true">
      <cache usage="read-write"/>
      <id name="Id" column="eventTrackerId" unsaved-value="0">
         <generator class="native"/>
      </id>
      <many-to-one name="EvaluationTracker" column="evaluationTrackerId"/>
      <many-to-one name="EventEvaluation" column="eventEvaluationId" class="Byu.Fhss.Sfl.RelateInstitute.Logic.Surveys.Evaluation, RelateInstituteLogic"/>
      <many-to-one name="StudyEvent" column="studyEventId"/>
      <set name="IncentivesSet" lazy="true" inverse="true">
         <cache usage="read-write"/>
         <key column="eventTrackerId"/>
         <one-to-many class="Emar.FollowUp.Incentive, Emar3"/>
      </set>
   </class>
</hibernate-mapping>

class OrderDetail:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" default-cascade="all-delete-orphan">
   <class name="Byu.Fhss.Sfl.RelateInstitute.Logic.Purchasing.OrderDetail, RelateInstituteLogic" table="orderdetail" lazy="true">
      <cache usage="read-write"/>
      <id name="Id" column="orderDetailId" unsaved-value="0">
         <generator class="native"/>
      </id>
      <set name="EvaluationsSet" lazy="true" inverse="true">
         <cache usage="read-write"/>
         <key column="orderDetailId"/>
         <one-to-many class="Byu.Fhss.Sfl.RelateInstitute.Logic.Surveys.Evaluation, RelateInstituteLogic" />
      </set>
      <set name="TokensSet" lazy="true" inverse="true">
         <cache usage="read-write"/>
         <key column="orderDetailId"/>
         <one-to-many class="Byu.Fhss.Sfl.RelateInstitute.Logic.Purchasing.Token, RelateInstituteLogic" />
      </set>
      <many-to-one name="Order" column="orderId" not-null="true" class="Byu.Fhss.Sfl.RelateInstitute.Logic.Purchasing.Order, RelateInstituteLogic"/>
      <many-to-one name="Product" access="field.camelcase" column="productId" not-null="true" class="Byu.Fhss.Sfl.Purchasing.Product, Purchasing"/>
   </class>
</hibernate-mapping>

class Order

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" default-cascade="all-delete-orphan">
   <class name="Byu.Fhss.Sfl.RelateInstitute.Logic.Purchasing.Order, RelateInstituteLogic" table="`order`" lazy="true">
      <cache usage="read-write"/>
      <id name="Id" column="orderId" unsaved-value="0">
         <generator class="native"/>
      </id>
      <many-to-one name="Customer" column="userId" class="Byu.Fhss.Sfl.RelateInstitute.Logic.Accounts.User, RelateInstituteLogic"/>

      <set name="PaymentsSet" inverse="true" order-by="ChargedOn">
         <cache usage="read-write"/>
         <key column="orderId"/>
         <one-to-many class="Byu.Fhss.Sfl.Purchasing.IPayment, Purchasing"/>
      </set>

      <set name="DetailsSet" inverse="true">
         <cache usage="read-write"/>
         <key column="orderId"/>
         <one-to-many class="Byu.Fhss.Sfl.RelateInstitute.Logic.Purchasing.OrderDetail, RelateInstituteLogic"/>
      </set>
   </class>
</hibernate-mapping>

class User

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" default-cascade="all-delete-orphan">
   <class name="Byu.Fhss.Sfl.RelateInstitute.Logic.Accounts.User, RelateInstituteLogic" table="`user`" lazy="true" discriminator-value="user" >
      <cache usage="read-write"/>
      <id name="Id" column="userId" unsaved-value="0">
         <generator class="native"/>
      </id>
      <discriminator column="userType" type="string" length="20"/>

      <set name="OrdersSet" lazy="true" inverse="true" order-by="Date">
         <cache usage="read-write"/>
         <key column="userId"/>
         <one-to-many class="Byu.Fhss.Sfl.RelateInstitute.Logic.Purchasing.Order, RelateInstituteLogic"/>
      </set>

      <subclass name="Byu.Fhss.Sfl.RelateInstitute.Logic.Accounts.Participant, RelateInstituteLogic" discriminator-value="participant" lazy="true">

         <set name="EvaluationsSet" lazy="true" inverse="true" order-by="StartDate DESC">
            <key column="participantId"/>
            <one-to-many class="Byu.Fhss.Sfl.RelateInstitute.Logic.Surveys.Evaluation, RelateInstituteLogic"/>
         </set>
      </subclass>

   </class>
</hibernate-mapping>

class Payment

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" default-cascade="all-delete-orphan">
   <class name="Byu.Fhss.Sfl.Purchasing.IPayment, Purchasing" table="payment">
      <cache usage="read-write"/>
      <id name="Id" column="paymentId" unsaved-value="0">
         <generator class="native"/>
      </id>
      <discriminator column="paymentType" type="string" length="10"/>
      <many-to-one name="Order" column="orderId" class="Byu.Fhss.Sfl.RelateInstitute.Logic.Purchasing.Order, RelateInstituteLogic"/>

      <subclass name="Byu.Fhss.Sfl.Purchasing.CreditCardPayment, Purchasing" discriminator-value="credit">
      </subclass>

      <subclass name="Byu.Fhss.Sfl.RelateInstitute.Logic.Purchasing.TokenPayment, RelateInstituteLogic" discriminator-value="token">
         <many-to-one name="Token" column="tokenId" class="Byu.Fhss.Sfl.RelateInstitute.Logic.Purchasing.Token, RelateInstituteLogic"/>
      </subclass>
   </class>
</hibernate-mapping>

aarnott · **Joined:** Fri May 13, 2005 4:08 pm **Posts:** 64

Ok, to sum up the problem, as I understand it: <many-to-one> relationships are always loading immediately from the entity that I'm actually calling for. <many-to-one>'s don't take a lazy=true, but their related <class>'s do, and they are set to lazy=true, yet all the entities related in this way are still getting loaded in a non-lazy fashion.

Any ideas as to why?

sergey · **Posted:** Wed Dec 07, 2005 6:36 pm

Please send the logs to my e-mail.

Nels_P_Olsen · **Posted:** Wed Dec 07, 2005 6:37 pm

By "loaded", do you mean that objects are created for the many-to-one properties, or that they are actually loaded with values from the related entity's table?

My understanding is that if the related entities are marked "lazy=true", then proxies of them are supposed to get returned. These proxies don't actually have any values from the database loaded into them (except maybe the ID property, which can be gotten from the foreign key column of the referencing table; in any case, the SQL NHibernate generates and exectues shouldn't need to select from referenced entity's table if you don't explicitly select properties the related entity or specify eager fetching in your HQL).

For example, every one of our entities has audit properties like "CreatedByUser". This property is a User object, not just some user login name. While every entity we load will have a proxy object attached to its CreatedByUser property, this proxy is empty until its properties are accessed. Since properties on this User object are rarely accessed, it shouldn't cause anything extra to get loaded from the database.

Let me know if this is not the case. The only way I'd imagine you could tell is to log the SQL, or close the session and then try and access a many-to-one property that should be proxied (in which case you should get a lazy loading exception?) Trying to examine properties on the proxy while the session is still open would cause them to lazy load ...

BTW, Hibernate 3.0 supposedly supports lazy loading of many-to-one and one-to-one properties ... how does this differ from what I described above?

aarnott · **Joined:** Fri May 13, 2005 4:08 pm **Posts:** 64

Nels_P_Olsen wrote:

By "loaded", do you mean that objects are created for the many-to-one properties, or that they are actually loaded with values from the related entity's table?

They are actually being loaded from the database. I'm certain of this because I have run SQL Profiler and caught the queries, and the NHibernate log4net logs show it as well.

So, if Hibernate 3.0 adds lazy loading of many-to-one relationships, then that would suggest that NHibernate (from Hibernate 2.x) doesn't yet support this, and what I'm observing is by design in NHibernate for the time being, and not actually a bug or a misconfiguration after all. I hope this is not the case, as I would really like lazy-loading of these many-to-one related entities right away.

And sergey, I'll email you the logs right away. Thank you.

ryzamm · **Posted:** Tue Dec 13, 2005 4:07 am

NHibernate currently have suppport many to one lazy loading, all you need is to make all the properties to virtual

aarnott · **Joined:** Fri May 13, 2005 4:08 pm **Posts:** 64

Ok, thanks for all your analysis, folks. I found that all my "child" entities, upon initialization, were referencing their parent entity's child collection, so an Apple, upon initialization, would call up Tree.Apples to save it in a variable. So, that would in turn call in the Tree, which led to my non-lazy-loading behavior problem.

So it's my mistake, and NHibernate rocks!

Thanks everybody.