need help on strange behaviour with lazy

anthony · **Posted:** Fri Apr 02, 2004 4:11 am

hi all,
here is my main mapping file:

Code:

<hibernate-mapping>
    <class
        name="Article"
        table="P_ART_ARTICLE"
        dynamic-update="false"
        dynamic-insert="false"
        mutable="false"
    >

        <id
            name="id"
            column="NSU_ART"
            type="java.lang.Long"
        >
            <generator class="assigned">
            </generator>
        </id>

        <map name="familles" table="P_L01_ART_NCL" lazy="true" inverse="false" order-by="DRA_ART_L01 DESC"
            where="NSU_VRN='30008' and DAT_FIN_L01 is null" >
              <key column="NSU_ART" />
              <index column="NSU_VRN" type="integer"/>   
              <many-to-many class="ElementNomenclature" column="NSU_ENC" />
        </map>

        <set
            name="produits"
            table="P_L27_RIN_ART"
            lazy="false"
            inverse="false"
            cascade="none"
            sort="unsorted"
            order-by="DRA_RIN_L27 DESC"
            where = "DAT_FIN_L27 is null"
        >

              <key
                  column="NSU_ART"
              />

              <many-to-many
                  class="com.auchan.refUniqueServices.infosProduits.bo.Produit"
                  column="NSU_RIN"
                  outer-join="true"
              />

        </set>
    </class>

</hibernate-mapping>

as you can see i don't want to lazy load the collections, i also want all collections to be loaded in one query.

when i query like this :

Code:

from Article as articles

the main query doesn't contain the "join" but generate 1 query per article to obtain the details, i am in the "n+1" trouble.

Now if i explicitly query using join in hql, the first query returns all i need, but it is followed by n (unecessary) queries....

Code:

"select articles,produits,produitInfoCriteresGestion,produitsFournisseurs,produitFournisseurInfos
from Article as articles 
join articles.produits as produits
join produits.produitInfoCriteresGestion as produitInfoCriteresGestion
join produits.produitsFournisseurs as produitsFournisseurs
join produitsFournisseurs.produitFournisseurInfos as produitFournisseurInfos" +
ElementNomenclature as elem "

What am i doing wrong?

Anthony

gavin · **Posted:** Fri Apr 02, 2004 4:18 am

Quote:

as you can see i don't want to lazy load the collections, i also want all collections to be loaded in one query.

This is incredibly evil. It results in a cartesian product.

You can fetch exactly one collection by setting outer-join="true".

I can't tell the cause of your other problem, because you didn't show the query. But in general this problem is solved by setting lazy="true" for appropriate class and collection mappings.

anthony · **Posted:** Fri Apr 02, 2004 4:33 am

excuse me Gavin, i don't understand...

the goal is to retrieve all Articles for one famille

for this, i make this query:

Code:

Article as articles, ElementNomenclature as elem 
where elem.n01Den.nsuDen = 450008 
and elem.id = 730030008 
and elem in elements(articles.familles) 

i want to retrieve all products in one shot but don't need the familles,
Product is mapped like this:

Code:

<?xml version="1.0"?>
<hibernate-mapping>
    <class
        name="Produit"
        table="P_RIN_REF_INT"
        dynamic-update="false"
        dynamic-insert="false"
        mutable="false"
    >

        <id
            name="id"
            column="NSU_RIN"
            type="java.lang.Long"
        >
            <generator class="assigned">
            </generator>
        </id>
...
        <bag
            name="composantsColisMixte"
            table="P_L13_RIN_RIN"
            lazy="false"
            inverse="false"
            cascade="none"
        >

              <key
                  column="NSU_RIN"
              />

              <many-to-many
                  class="Produit"
                  column="RIN_NSU_RIN"
                  outer-join="true"
              />

        </bag>

        <bag
            name="offres"
            table="P_POF_PRD_OFR"
            lazy="false"
            inverse="false"
            cascade="none"
        >

              <key
                  column="NSU_RIN"
              />

              <many-to-many
                  class="Offre"
                  column="NSU_OFR"
                  outer-join="true"
              />

        </bag>

        <set
            name="produitInfoCriteresGestion"
            table="P_L55_RIN_VCG"
            lazy="false"
            inverse="false"
            cascade="none"
            sort="unsorted"
            order-by="DAP_L55 desc"
            where="NSU_CRG = 20029"
        >

              <key
                  column="NSU_RIN"
              />

              <many-to-many
                  class="ProduitInfoCritereGestion"
                  column="NSU_VCG"
                  outer-join="true"
              />

        </set>

        <bag
            name="produitsFournisseurs"
            table="P_RFR_REF_FRN"
            lazy="false"
            inverse="false"
            cascade="none"
            where="DAR_RFR is null"
        >

              <key
                  column="NSU_RIN"
              />

              <one-to-many
                  class="ProduitFournisseur"
              />
        </bag>
        
        <bag
            name="articles"
            table="P_L27_RIN_ART"
            lazy="false"
            inverse="false"
            cascade="none"
            where = "DAT_FIN_L27 is null"
        >

              <key
                  column="NSU_RIN"
              />

              <many-to-many
                  class="Article"
                  column="NSU_ART"
                  outer-join="false"
              />
        </bag>

    </class>

</hibernate-mapping>

all collections "under" produit are also with lazy=false & outer-join=true

It seems i'm doing a big mistake about lazy loading ;(

anthony · **Posted:** Fri Apr 02, 2004 4:39 am

and here is the generated sql

Code:

select article0_.NSU_ART as NSU_ART0_, elementnom1_.NSU_ENC 
/// more column of the two tables

from P_ART_ARTICLE article0_, P_N05_L19 elementnom1_ 

where elementnom1_.NSU_DEN=450008 and ((elementnom1_.NSU_DEN=450008 )and(elementnom1_.NSU_ENC=730030008 )and(elementnom1_.NSU_ENC 

in(select familles2_.NSU_ENC from P_L01_ART_NCL familles2_ where article0_.NSU_ART=familles2_.NSU_ART and familles2_.NSU_VRN='30008' and familles2_.DAT_FIN_L01 is null)))

gavin · **Posted:** Fri Apr 02, 2004 4:42 am

I don't see any collections marked outer-join="true" here.

anthony · **Posted:** Fri Apr 02, 2004 4:56 am

Bingo,
the two advices solved my problem,

to resume:
- if someone always want to load collections in one shoot + in one sql query : mark the collection with lazy=false + outer-join = true (what i wasn't doing)
- if someone want to load collection in a particular scenario: mark the collection with lazy=true + outer-join = true and query with join in hql

i don't know why, i thought that lazy=false forced outer-join= true, i know this is stupid....

Thanks again Gavin!

Anthony

rollatwork · **Posted:** Wed Sep 22, 2004 9:56 am

anthony wrote:

- if someone want to load collection in a particular scenario: mark the collection with lazy=true + outer-join = true and query with join in hql

Doesn't HQL ignore the mapping document eager fetch (ie. outer-join="true")

I'm encountering similar problems when trying to load an entity which is the root of a fairly deep graph which is required on the client side. (See mapping below)

We have to return this entity, Subject, with the collections children (of type Subject), attributes, and parent ids initialized. Subject is used by other use cases so we default the entity to have all collections marked lazy.

Given the id of the root Subject, could you suggest the most performant query(ies) to load this tree?

Mapping:

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
    <class
        name="com.casebank.spotlight.domain.Subject"
        table="subject"
        dynamic-update="false"
        dynamic-insert="false"
        select-before-update="false"
    >

        <jcs-cache usage="read-write" />

        <id
            name="id"
            column="id"
            type="java.lang.String"
            unsaved-value="0"
        >
            <generator class="uuid.hex">
              <!--  
                  To add non XDoclet generator parameters, create a file named 
                  hibernate-generator-params-Subject.xml 
                  containing the additional parameters and place it in your merge dir. 
              --> 
            </generator>
        </id>

        <map
            name="names"
            table="subject_name"
            lazy="false"
            sort="unsorted"
            inverse="false"
            cascade="all"
        >
            <cache 
                usage="nonstrict-read-write" 
             />

              <key
                  column="subject_id"
              >
              </key>

              <index
                  column="locale_id"
                  type="string"
              />

              <element
                  column="translation"
                  type="string"
                  not-null="false"
                  unique="false"
              />

        </map>

        <map
            name="descriptions"
            table="subject_description"
            lazy="true"
            sort="unsorted"
            inverse="false"
            cascade="all"
        >
            <cache 
                usage="nonstrict-read-write" 
             />

              <key
                  column="subject_id"
              >
              </key>

              <index
                  column="locale_id"
                  type="string"
              />

              <element
                  column="translation"
                  type="string"
                  not-null="false"
                  unique="false"
              />

        </map>

        <list
            name="children"
            table="subject_hierarchy"
            lazy="true"
            inverse="false"
            cascade="none"
        >
            <cache 
                usage="nonstrict-read-write" 
             />

              <key
                  column="parent_id"
              >
              </key>

              <index
                  column="sort_order"
              />

              <many-to-many
                  class="com.casebank.spotlight.domain.Subject"
                  column="child_id"
                  outer-join="true"
               />

        </list>

        <bag
            name="parentsIds"
            table="subject_hierarchy"
            lazy="true"
            inverse="false"
            cascade="none"
        >
            <cache 
                usage="nonstrict-read-write" 
             />

              <key
                  column="child_id"
              >
              </key>

              <element
                  column="parent_id"
                  type="string"
                  not-null="false"
                  unique="false"
              />

        </bag>

        <list
            name="attributes"
            table="subject_attribute"
            lazy="true"
            inverse="false"
            cascade="none"
        >
            <cache 
                usage="nonstrict-read-write" 
             />

              <key
                  column="subject_id"
              >
              </key>

              <index
                  column="sort_order"
              />

              <many-to-many
                  class="com.casebank.spotlight.domain.Attribute"
                  column="attribute_id"
                  outer-join="true"
               />

        </list>

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-Subject.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

anthony · **Posted:** Wed Sep 22, 2004 10:25 am

use 5 queries, one to fetch each collection

rollatwork · **Posted:** Wed Sep 22, 2004 10:31 am

Thanks, Anthony.

This is essentially what I have done and wanted confirmation on. Just looks ugly, but certainly I understand the limitation of one fetch per query.

Regards from Cartesian hell,
Roll

Code:

            Query query = session.createQuery("select subject from Subject as subject " +
                  "join fetch subject.children as children");
            
            Query query2 = session.createQuery("select subject from Subject as subject " +
                  "join fetch subject.parentsIds as parentsIds ");
            
            Query query3 = session.createQuery("select subject from Subject as subject " +
                  "join fetch subject.attributes as attributes ");
            
            List children = query.list();
            List parentIds = query2.list();
            List attributes = query3.list();

            // now load the subject by Id, voila... instant graph

...

rollatwork · **Posted:** Mon Feb 14, 2005 4:09 pm

rollatwork wrote:

Thanks, Anthony.

This is essentially what I have done and wanted confirmation on. Just looks ugly, but certainly I understand the limitation of one fetch per query.

Regards from Cartesian hell,
Roll

Code:

            Query query = session.createQuery("select subject from Subject as subject " +
                  "join fetch subject.children as children");
            
            Query query2 = session.createQuery("select subject from Subject as subject " +
                  "join fetch subject.parentsIds as parentsIds ");
            
            Query query3 = session.createQuery("select subject from Subject as subject " +
                  "join fetch subject.attributes as attributes ");
            
            List children = query.list();
            List parentIds = query2.list();
            List attributes = query3.list();

            // now load the subject by Id, voila... instant graph

...

In my coming iteration I am reviewing our data layer and EIS tier and reviewing for optimizations. 5 months since this last post... I was just curious if this is still considered the optimum best practice approach to loading an entity with multiple lazy collections?

This has been working well for us and I am about to apply the technique to other sub-optimal queries. However, it's pretty ugly to look at.

anthony · **Posted:** Mon Feb 14, 2005 5:51 pm

for me that's always ok

if you have this case A--*B--*C (not the same as C*--A--*B), just use batch fetching

maybe the one collection fetching limitation will go away but for now, it's ok.