Castle ActiveRecord / NHibernate optimization question

Question

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:

<?xml version="1.0" encoding="utf-16"?> 
    <hibernate-mapping  auto-import="true" default-lazy="false" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:nhibernate-mapping-2.2"> 
      <class name="FieldResult, Data" table="FieldResults"> 
        <id name="ID" access="property" column="ID" type="Int32" unsaved-value="0"> 
          <generator class="native"> 
            <param name="sequence">FieldResults_ID</param> 
          </generator> 
        </id> 
        <property name="FieldID" access="property" type="String"> 
          <column name="Field_ID"/> 
        </property> 
        <property name="Name" access="property" type="String"> 
          <column name="Name"/> 
        </property> 
        <property name="DisplayValue" access="property" type="String"> 
          <column name="Value"/> 
        </property> 
        <property name="MessagePosition" access="property" type="Int32"> 
          <column name="MessagePosition"/> 
        </property> 
        <many-to-one name="ParentMessage" access="property" class="MessageResult, Data" column="MessageResults_ID" /> 
        <many-to-one name="ParentField" access="property" class="FieldResult, Data" column="ParentField_ID" /> 
        <bag name="Children" access="property" table="FieldResults" lazy="false" cascade="all" order-by="Field_ID"> 
          <key column="ParentField_ID" /> 
          <one-to-many class="FieldResult, Data" /> 
        </bag> 
      </class> 
    </hibernate-mapping>`

Answer 1

I'd guess this was a select n+1 issue. Have you tried eager loading, or a custom HQL query? I'll post some example HQL to get around this if you haven't