CODEWITHSUNDEEP
nhibernatenhibernate-mappinglinq-to-nhibernate
sqlnewbie
sqlnewbie

Reputation: 867

One-to-One association issue, NHProf showing Select N+1 Alert, Changing Criteria to use FetchMode.Join not resolving N+1 problem Any thoughts?

We are facing One-to-One association issue, NHProf showing Select N+1 Alert, Changing Criteria to use FetchMode.Join is not resolving N+1 problem. Following are the details.

Software Version Details: NH Version: 1.2 .net Version: 3.5 DB: Oracle 11g Second level Cache: Enabled

Hbm files and class entities.

Description: A 'Sample' entity can contain 0 or 1 'Association' entities. An 'Association' entity has a foreign key constraint from 'Sample' entity.

Sample.hbm.xml 

<?xml version="1.0" encoding="utf-8" ?> 
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
assembly="NHSample" namespace="NHSample"> 
<class name="Sample" table="SAMPLE" proxy="Sample" 
polymorphism="explicit" > 
        <id name="Id" type="Decimal" unsaved-value="-1"> 
                        <column name="SA_ID" sql-type="NUMBER" not-null="true" 
unique="true"/> 
                        <generator class="sequence"> 
                                <param name="sequence">SA_ID_SEQ</param> 
                        </generator> 
    </id> 
        <property name="SampleName" type="String"> 
                <column name="SAMPLE_NAME" length="100" sql-type="VARCHAR2" not- 
null="false"/> 
        </property> 
        <one-to-one name="Association" class="Association" property- 
ref="SampleAssociated" cascade="all-delete-orphan"/> 
  </class> 
</hibernate-mapping>

Sample.cs 

namespace NHSample 
{ 
    public class Sample 
    { 
        public virtual decimal Id 
        { 
            get ; 
            set ; 
        } 

        public virtual string SampleName 
        { 
            get ; 
            set ; 
        } 

        public virtual Association Association 
        { 
            get ; 
            set ; 
        }
    } 
}

Association.hbm.xml 

<?xml version="1.0" encoding="utf-8" ?> 
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
assembly="NHSample" namespace="NHSample"> 
  <class name="Association" table="ASSOCIATION"> 
    <id name="Id" type="Decimal" unsaved-value="-1"> 
      <column name="AS_ID" sql-type="NUMBER" not-null="true" 
unique="true"/> 
      <generator class="sequence"> 
        <param name="sequence">AS_ID_SEQ</param> 
      </generator> 
    </id> 
        <property name="AssociationName" type="String"> 
                <column name="ASSOCIATION_NAME" length="100" sql-type="VARCHAR2" not- 
null="false"/> 
        </property> 
    <many-to-one name="SampleAssociated" class="Sample"> 
      <column name="SA_ID" sql-type="NUMBER" not-null="true"/> 
    </many-to-one> 
  </class> 
</hibernate-mapping>

Association.cs 

namespace NHSample 
{ 
    public class Association 
    { 
        public virtual decimal Id 
        { 
            get ; 
            set ; 
        } 

        public virtual string AssociationName 
        { 
            get ; 
            set ; 
        } 

        public virtual Sample SampleAssociated 
        { 
            get ; 
            set ; 
        } 
    } 
}

Criteria WITHOUT FetchMode=Join used to access Sample Entity: 

class Program 
    { 
        static void Main(string[] args) 
        { 
            using (ISession session = SessionFactory.OpenSession()) 
            { 
                var electedIds = new List<decimal>() { 1, 2, 3 }; 

                ICriteria criteria = 
session.CreateCriteria(typeof(Sample)); 
                criteria.Add(Expression.In("Id", electedIds)); 

                var list = criteria.List(); 
            } 
            Console.ReadKey(); 
        } 
    }

NHProf showed N+1 alert when Criteria.List() has been executed. Following were the SQL statments showed by NHProf.

-- statement #1 

SELECT this_.SA_ID                   as SA1_0_1_, 
       this_.SAMPLE_NAME             as SAMPLE2_0_1_, 
       associatio2_.AS_ID            as AS1_1_0_, 
       associatio2_.ASSOCIATION_NAME as ASSOCIAT2_1_0_, 
       associatio2_.SA_ID            as SA3_1_0_ 
FROM   SAMPLE this_ 
       left outer join ASSOCIATION associatio2_ 
         on this_.SA_ID = associatio2_.SA_ID 
WHERE  this_.SA_ID in (1 /* :p0 */,2 /* :p1 */,3 /* :p2 */)

-- statement #2 

SELECT associatio0_.AS_ID            as AS1_1_0_, 
       associatio0_.ASSOCIATION_NAME as ASSOCIAT2_1_0_, 
       associatio0_.SA_ID            as SA3_1_0_ 
FROM   ASSOCIATION associatio0_ 
WHERE  associatio0_.SA_ID = 1 /* :p0 */

-- statement #3 

SELECT associatio0_.AS_ID            as AS1_1_0_, 
       associatio0_.ASSOCIATION_NAME as ASSOCIAT2_1_0_, 
       associatio0_.SA_ID            as SA3_1_0_ 
FROM   ASSOCIATION associatio0_ 
WHERE  associatio0_.SA_ID = 2 /* :p0 */

-- statement #4 

SELECT associatio0_.AS_ID            as AS1_1_0_, 
       associatio0_.ASSOCIATION_NAME as ASSOCIAT2_1_0_, 
       associatio0_.SA_ID            as SA3_1_0_ 
FROM   ASSOCIATION associatio0_ 
WHERE  associatio0_.SA_ID = 3 /* :p0 */

Using NHProf website suggestions modified cirteria using FETCHMODE.Join: 

class Program 
    { 
        static void Main(string[] args) 
        { 
            using (ISession session = SessionFactory.OpenSession()) 
            { 
                var electedIds = new List<decimal>() { 1, 2, 3 }; 

                ICriteria criteria = 
session.CreateCriteria(typeof(Sample)); 
                criteria.SetFetchMode("Association", FetchMode.Join); 
                criteria.Add(Expression.In("Id", electedIds)); 

                var listByIds = criteria.List(); 
            } 
            Console.ReadKey(); 
        } 
    }

NHProf still showing N+1 alert and following are the SQL queries.

-- statement #1 

SELECT this_.SA_ID                   as SA1_0_1_, 
       this_.SAMPLE_NAME             as SAMPLE2_0_1_, 
       associatio2_.AS_ID            as AS1_1_0_, 
       associatio2_.ASSOCIATION_NAME as ASSOCIAT2_1_0_, 
       associatio2_.SA_ID            as SA3_1_0_ 
FROM   SAMPLE this_ 
       left outer join ASSOCIATION associatio2_ 
         on this_.SA_ID = associatio2_.SA_ID 
WHERE  this_.SA_ID in (1 /* :p0 */,2 /* :p1 */,3 /* :p2 */)

-- statement #2 

SELECT associatio0_.AS_ID            as AS1_1_0_, 
       associatio0_.ASSOCIATION_NAME as ASSOCIAT2_1_0_, 
       associatio0_.SA_ID            as SA3_1_0_ 
FROM   ASSOCIATION associatio0_ 
WHERE  associatio0_.SA_ID = 1 /* :p0 */

-- statement #3 

SELECT associatio0_.AS_ID            as AS1_1_0_, 
       associatio0_.ASSOCIATION_NAME as ASSOCIAT2_1_0_, 
       associatio0_.SA_ID            as SA3_1_0_ 
FROM   ASSOCIATION associatio0_ 
WHERE  associatio0_.SA_ID = 2 /* :p0 */

-- statement #4 

SELECT associatio0_.AS_ID            as AS1_1_0_, 
       associatio0_.ASSOCIATION_NAME as ASSOCIAT2_1_0_, 
       associatio0_.SA_ID            as SA3_1_0_ 
FROM   ASSOCIATION associatio0_ 
WHERE  associatio0_.SA_ID = 3 /* :p0 */

Any thoughts on how to overcome this Additional SQL statments and N+1 issue in this scenario.

Thank you.

Upvotes: 1

Views: 622

Answers (1)

KeithS
KeithS

Reputation: 71573

This is a known issue in NHibernate; Join fetching is not preventing the N+1 problem in a variety of situations. Notice that your first query with the join fetching does actually include the join and mapped fields; NHibernate is constructing the proper query as per the mapping, but is not conveying that information into the joined child collection, so it just goes back and runs N queries to pull the children.

You can see the JIRA item here: https://nhibernate.jira.com/browse/NH-2534. The NHibernate team has kicked into gear and is making headway on the backlog of bugs, and this one's marked major (I would call it so; I'm waiting on a new build to solve my own N+1 problem as evidenced by my comment to this exact bug), so I would guess it'll get fixed as soon as they can get to it.

Personally, I wouldn't mind a 2-pass query solution, where NHibernate could be told in the mapping to lazy-load using a foreign key query by default. Currently, you can only specify this behavior by constructing your own IQuery to load children yourself. A lazy proxy produced by NH is pre-initialized with IDs retrieved using a foreign key query, but then it pulls the full data one record at a time by ID. That's insane; you'd never do it that way if you weren't using NH.

Upvotes: 3

Related Questions