Hi all,
i'm working on optimizing an application using NHibernate 1.0.4 and SQL-Server 2000.
I have problems with an ICriteria query on an object which contains one-to-one mappings. Regardless what FetchMode i configure NHibernate uses the 'fetch="select"' mode which leads to the well known n+1 select problem.
Here is my code (mappings are shortened because of the number of fields in the tables):
Code:
Mappings:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" assembly="MyAssembly" namespace="MyNamespace">
<class name="Material" table="MATERIAL">
<id name="mId" column="MATERIAL_ID" access="field" type="Guid">
<generator class="guid"/>
</id>
<many-to-one name="Autopull" column="AUTOPULL_ID" class="myNamespace.Autopull" unique="true" fetch="join" cascade="all-delete-orphan" />
...
field mapping for MATERIAL
...
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" assembly="MyAssembly" namespace="MyNamespace">
<class name="Autopull" table="AUTOPULL">
<id name="mId" column="AUTOPULL_ID" access="field" type="Guid">
<generator class="guid"/>
</id>
<many-to-one name="OptionalAutopull" column="OPTIONAL_AUTOPULL_ID" class="Mynamespace.OptionalAutopull" unique="true" cascade="none" />
<one-to-one name="Material" class="Mynamespace.Material" property-ref="Autopull" fetch="join" />
...
field mapping for AUTOPULL
...
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" assembly="MyAssembly" namespace="MyNamespace">
<class name="OptionalAutopull" table="OPTIONAL_AUTOPULL">
<id name="mId" column="OPTIONAL_AUTOPULL_ID" access="field" type="Guid">
<generator class="guid"/>
</id>
...
field mapping for AUTOPULL
...
</class>
</hibernate-mapping>
Code:
IList materials = session.CreateCriteria(typeof(Material))
.Add(Expression.In(Material.CUSTOMER_ID, pCustomerIds))
.Add(Expression.In(Material.MATERIAL_CLASSIFICATION_CODE, getConfigMaterialClassificationCodes()))
.Add(Expression.Eq(Material.CONFIGURED_MATERIAL_FLAG, new NullableBoolean(false)))
.SetFetchMode("Autopull", FetchMode.Join)
.SetFetchMode("Autopull.Material", FetchMode.Join)
.List();
The generated select looks like this:
Code:
first select:
SELECT
<field_list>
FROM
MATERIAL this
left outer join AUTOPULL autopul1_ on this.AUTOPULL_ID=autopul1_.AUTOPULL_ID
left outer join MATERIAL gdcmateria2_ on autopul1_.AUTOPULL_ID=materia2_.AUTOPULL_ID
left outer join OPTIONAL_AUTOPULL optiona3_ on autopul1_.OPTIONAL_AUTOPULL_ID=optiona3_.OPTIONAL_AUTOPULL_ID
WHERE
<selection_criteria>
subsequent selects:
SELECT
<field_list>
FROM
MATERIAL materia0_
left outer join AUTOPULL gdcautopul1_ on materia0_.AUTOPULL_ID=autopul1_.AUTOPULL_ID
left outer join MATERIAL materia2_ on autopul1_.AUTOPULL_ID=materia2_.AUTOPULL_ID
left outer join OPTIONAL_AUTOPULL optiona3_ on autopul1_.OPTIONAL_AUTOPULL_ID=optiona3_.OPTIONAL_AUTOPULL_ID
WHERE
materia0_.AUTOPULL_ID=@p0
The first select returns all data nHibernate needs to initialize the objects. The second select NHibernate generates for each Autopull found to fill the Material property. If i look at the internal structures i will find all objects filled except the fields which build up the one-to-one mapping.
I have debugged NHibernate and didn't find where the .SetFetchMode() of the ICriteria or the "fetch=join" was evaluated. No matter what i configure, NHibernate does the n+1 select.
If i remove the <one-to-one> from the Autopull mapping, NHibernate executes only one select against the database. But i need the bidirectional reference in my business logic.
Did anyone have a hint how i can solve this problem?
The only thing which springs to my mind is to map the AUTOPULL table as component instead of one-to-one, as Autopulls are just an enhancement to Materials. But if i do so this will lead to a lot of code changes.
Many thanks in advance
Klaus
Login
FAQ
Search
