Hi There,

I am facing some issue related to performance. What I found was NHibernate fires so many queries for retrieving data. I have enabled second level cache also but for fetching records at first time it is taking long time. My hbm files are as follow:

1) Word.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="BusinessEntities" assembly="BusinessEntities">
<class name="Word" table="WORD" >
<cache usage="read-write"/>
<id name="Id" column="WORD_ID" >
<generator class="increment" />
</id>
<property name="WordExport" column="WORD_EXPORT_ID"></property>
<bag name="WordValues" inverse="true" cascade="all">
<cache usage="read-write"/>
<key column="WORD_ID" />
<one-to-many class="WordValue" />
</bag>
</class>
</hibernate-mapping>

2) WordValue.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="BusinessEntities" assembly="BusinessEntities">
<class name="WordValue" table="WORD_VALUE" >

<id name="Id" column="WORD_VALUE_ID" >
<generator class="increment" />
</id>
<property name="Value" column="WORD_VALUE" />
<many-to-one name="Word" class="Word" column="WORD_ID" />
<many-to-one name="SemanticWordValue" property-ref="Word" class="WordValue">
<column name="SEMANTIC_WORD_ID" />
</many-to-one>
</class>
</hibernate-mapping>

Now When I fire the query

Context.Session.Find("from Word word left join fetch word.WordValues");

Instead of firing one join query to fetch all the data i.e.

select word0_.WORD_ID as WORD1_43_0_, wordvalues1_.WORD_VALUE_ID as WORD1_40_1_, word0_.WORD_EXPORT_ID as WORD2_43_0_, wordvalues1_.WORD_VALUE as WORD2_40_1_, wordvalues1_.WORD_ID as WORD3_40_1_, wordvalues1_.SEMANTIC_WORD_ID as SEMANTIC5_40_1_, wordvalues1_.WORD_ID as WORD3_0__, wordvalues1_.WORD_VALUE_ID as WORD1_0__ from WORD word0_ left outer join WORD_VALUE wordvalues1_ on word0_.WORD_ID=wordvalues1_.WORD_ID


It fires several queries such as

first is

select word0_.WORD_ID as WORD1_43_0_, wordvalues1_.WORD_VALUE_ID as WORD1_40_1_, word0_.WORD_EXPORT_ID as WORD2_43_0_, wordvalues1_.WORD_VALUE as WORD2_40_1_, wordvalues1_.WORD_ID as WORD3_40_1_, wordvalues1_.SEMANTIC_WORD_ID as SEMANTIC5_40_1_, wordvalues1_.WORD_ID as WORD3_0__, wordvalues1_.WORD_VALUE_ID as WORD1_0__ from WORD word0_ left outer join WORD_VALUE wordvalues1_ on word0_.WORD_ID=wordvalues1_.WORD_ID

then

exec sp_executesql N'SELECT wordvalue0_.WORD_VALUE_ID as WORD1_40_0_, wordvalue0_.WORD_VALUE as WORD2_40_0_, wordvalue0_.WORD_ID as WORD3_40_0_, wordvalue0_.SEMANTIC_WORD_ID as SEMANTIC5_40_0_ FROM WORD_VALUE wordvalue0_ WHERE wordvalue0_.WORD_ID=@p0',N'@p0 int',@p0=1

exec sp_executesql N'SELECT wordvalue0_.WORD_VALUE_ID as WORD1_40_0_, wordvalue0_.WORD_VALUE as WORD2_40_0_, wordvalue0_.WORD_ID as WORD3_40_0_, wordvalue0_.SEMANTIC_WORD_ID as SEMANTIC5_40_0_ FROM WORD_VALUE wordvalue0_ WHERE wordvalue0_.WORD_ID=@p0',N'@p0 int',@p0=3
and so on...

Can anyone please help me to solve this issue?

Thanks in advance :)

Regards,
Niranjan

Hi,

I have found the problem which fires the multiple queries.
It is because the relation between Word and WordValue is made on WORD_ID field and also there is self reference in the WordValue table between Semantic_Word_Id and Word_Id.

Here the self reference is with the non-primary key i.e. Word_Id so it is firing multiple queries.

If the self reference is with primary key i.e. Word_Value_Id then it will fire only one query.

But my problem is I have self reference with non-primary key... :(

Please can anyone provide me help with this situation?

Regards,
Niranjan

Have you found a solution, because i have now the same problem!