do you mean if there are some NH updates after 1.2.0GA ? On Thu, Mar 31, 2011 at 1:32 AM, srinivas rao <[email protected]>wrote:
> 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. > > -- Fabio Maulo -- 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.
