Hi.

I have a simple relationship in my database. A table Title (mapped to the class TitleType) contains definitions for a number of official titles. The table Language contains a number of supported languages by the system. The table Title_Text (Mapped to the class TitleTypeLocalization) contains the actual name of the title for any supported language, i.e. the Title_Text table contains a TitleID, a LanguageID and a string.

The code that executes wants to get a list of all titles for a specific language. The resulting number of sql requests sent to the SqlServer is very high - one for each record in the Title table. I extracted a trace from Sql server profiler.

This should be a simple join. Why is it doing this. Will a never version of NHibernate solve this problem? (An update is not exactly easy right now because of inter-component dependancies and we are very close to deadline -> new tests, etc.)

Hibernate version:
1.0.1

Mapping documents:
Language:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">

<class name="Midas.Model.Language, Midas.Model" table="[Language]">
<id name="myDatabaseId" type="Int64" column="LanguageID" unsaved-value="0" access="field">
<generator class="identity"/>
</id>
<property name="myIsoTwoLetterName" type="String" column="ISOCodeA2" access="field"/>
</class>

</hibernate-mapping>

Title:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">

<class name="Midas.Model.TitleType, Midas.Model" table="Title">
<id name="myDatabaseId" type="Int64" column="TitleID" unsaved-value="0" access="field">
<generator class="identity"/>
</id>
</class>

</hibernate-mapping>

Title_Text:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">

<class name="Midas.Model.TitleTypeLocalization, Midas.Model" table="Title_Text">
<id name="myDatabaseId" type="Int64" column="Title_TextID" unsaved-value="0" access="field">
<generator class="identity"/>
</id>
<property name="myName" type="String" column="Title" access="field"/>
<many-to-one name="myLanguage" class="Midas.Model.Language, Midas.Model" column="LanguageID" access="field"/>
<many-to-one name="myTitleType" class="Midas.Model.TitleType, Midas.Model" column="TitleID" access="field"/>
</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
IQuery query = session.CreateQuery("FROM Midas.Model.TitleTypeLocalization loc WHERE loc.myLanguage.myDatabaseId = :languageId");
query.SetInt64("languageId", language.DatabaseId);
resultAsList = query.List();

Name and version of the database you are using:
SqlServer 2005

The generated SQL (show_sql=true):
First it selects from the Title_Text table, then the Title table, then the Language table, and then it does a select from the title table for each remaining row.

exec sp_executesql N'select titletypel0_.Title_TextID as Title_Te1_, titletypel0_.TitleID as TitleID, titletypel0_.Title as Title, titletypel0_.LanguageID as
LanguageID from Title_Text titletypel0_ where (titletypel0_.LanguageID=@p0)',N'@p0 bigint',@p0=208

exec sp_executesql N'SELECT titletype0_.TitleID as TitleID0_ FROM Title titletype0_ WHERE titletype0_.TitleID=@p0',N'@p0 bigint',@p0=-1

exec sp_executesql N'SELECT language0_.LanguageID as LanguageID0_, language0_.ISOCodeA2 as ISOCodeA20_ FROM [Language] language0_ WHERE
language0_.LanguageID=@p0',N'@p0 bigint',@p0=208

exec sp_executesql N'SELECT titletype0_.TitleID as TitleID0_ FROM Title titletype0_ WHERE titletype0_.TitleID=@p0',N'@p0 bigint',@p0=90

... one select for each record in the Title table

exec sp_executesql N'SELECT titletype0_.TitleID as TitleID0_ FROM Title titletype0_ WHERE titletype0_.TitleID=@p0',N'@p0 bigint',@p0=999

Something is missing from your mapping files, you forgot the TitleType mapping which is probably where the problem is.

You most likely need to just investigate options fetch="join" or fetch="subselect" or option batch-size="..." on your collection mapping

Or try to use lazy initialization for classes that are defined in many to one relations.