Hi,

I have the following structure: message (message table) may have multiple fields (fields table), and each field may have multiple subfields (stored in the same "fields" table with the only difference that fields dont have values in the "ParentField" column). Subfields may have subsubfields, etc., but this is not important.

When I retrieve 10 messages that have 10 field each, and each field has 20 subfields, I can see from the log file that NHibernate generates 2000 SQL calls.
Is there a way to optimize that?

Thanks!

Here is one of 2000 SQL statements generated by NHibernate:

SELECT fieldresul0_.MessageResults_ID as MessageR6___2_, fieldresul0_.ID as ID2_, fieldresul0_.ID as ID5_1_, fieldresul0_.Field_ID as Field2_5_1_, fieldresul0_.Name as Name5_1_, fieldresul0_.Value as Value5_1_, fieldresul0_.MessagePosition as MessageP5_5_1_, fieldresul0_.MessageResults_ID as MessageR6_5_1_, fieldresul0_.ParentField_ID as ParentFi7_5_1_, fieldresul1_.ID as ID5_0_, fieldresul1_.Field_ID as Field2_5_0_, fieldresul1_.Name as Name5_0_, fieldresul1_.Value as Value5_0_, fieldresul1_.MessagePosition as MessageP5_5_0_, fieldresul1_.MessageResults_ID as MessageR6_5_0_, fieldresul1_.ParentField_ID as ParentFi7_5_0_ FROM FieldResults fieldresul0_ left outer join FieldResults fieldresul1_ on fieldresul0_.ParentField_ID=fieldresul1_.ID WHERE fieldresul0_.MessageResults_ID=@p0 ORDER BY fieldresul0_.MessagePosition

Here is mapping file that ActiveRecord generates:

Have a look at chapter 15 of the reference doc. There are various ways explained.

E.g.

<bag name="Children" access="property" table="FieldResults" lazy="false" cascade="all" order-by="Field_ID" fetch="join">

or better specifiying it in the query:

"from FieldResult fr join fetch fr.Children"

or criteria

criteria.SetFetchMode("fr.Children", FetchMode.Join)