Any updates ??? On Mar 30, 5:43 pm, srinivas rao <[email protected]> wrote: > Hi All, > > 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 ; > } > > } > > } > > Cirteria 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.
-- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
