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.
