NHibernate list saving and loading optimization advice

I have 3 classes that are mapped via NHibernate: Intersection, Vehicle and Zone.

My Intersection class contains a list of Zones that belong to the Intersection. My Vehicle class contains a list of Zones that the Vehicle contains. Finally my Zone class contains a list of Vehicles that contain the Zone.

My test performance set consists of 10,000 Vehicles and 500 Zones and 250 Intersections. My load time with the Zones and Vehicles both having the lists mapped is right around 27 minutes for all these objects.

I am not sure what is going on but these two lists are not optimized in the least. There is a 10 minute difference in saving the objects if I take the Vehicle list out of the Zone class mapping. This seems a bit off seeing as how the 2 lists are directly related to each other.

It appears NHibernate is recursively saving the items within both the lists and adding a bunch of overhead to the saving procedure. Is there any way to optimize these lists for faster time saving the objects?

Here are my mappings for Device which both my Intersection and Vehicle inherit from:

<hibernate-mapping xmlns="urn:nhibernate-mapping开发者_如何学Python-2.2">
  <class xmlns="urn:nhibernate-mapping-2.2" name="EMTRAC.Devices.Device, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`Device`">
    <id name="PK" type="System.Int64, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="PK" />
      <generator class="identity" />
    </id>
    <version name="LastModifiedOn" column="LastModifiedOn" type="timestamp" access="field.pascalcase-underscore" />
    <joined-subclass name="EMTRAC.Intersections.Intersection, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
      <key>
        <column name="Device_id" />
      </key>      
      <component name="Zones" access="property">
        <bag name="_list" cascade="all-delete-orphan" access="field" fetch="join" inverse="false">
          <key>
            <column name="Zone_PK" />
          </key> 
          <many-to-many class="EMTRAC.Zones.Zone, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>
        </bag>
      </component>
      <property name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
        <column name="ID" />
      </property>
    </joined-subclass>
    <joined-subclass name="EMTRAC.Vehicles.Vehicle, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
      <key>
        <column name="Device_id" />
      </key>      
      <component name="Zones" access="property">
        <bag name="_list" cascade="save-update" access="field" table="VehicleZones" inverse="true">
          <key>
            <column name="veh_id" not-null="true"/>
          </key>
          <many-to-many class="EMTRAC.Zones.Zone, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>
        </bag>
      </component>
      <property name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
        <column name="ID" />
      </property>           
    </joined-subclass>
  </class>
</hibernate-mapping>    

And finally here is my mapping for the Zone class:

    <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class xmlns="urn:nhibernate-mapping-2.2" name="EMTRAC.Zones.Zone, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`Zone`">
    <id name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="PK"/>
      <generator class="identity" />
    </id>
    <version name="LastModifiedOn" column="LastModifiedOn" type="timestamp" access="field.pascalcase-underscore" />
    <property name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="ID" />
    </property>    
    <component name="Vehicles" access="property">
      <bag name="_list" cascade="save-update" access="field" table="VehicleZones">
        <key>
          <column name="veh_id" not-null="true"/>
        </key>
        <many-to-many class="EMTRAC.Vehicles.Vehicle, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>
      </bag>
    </component>
  </class>
</hibernate-mapping>

Any ideas how to improve the efficiency of these lists? Right now each table it is saving them in contains 500,000 records and of course loading them and saving that many records is taking some time.

Edit

I removed all the lazy="false" parts I had forgotten to remove and it is substantially faster. I also made a few adjustments to the saving and loading of the objects, breaking the parts into separate threads to increase performance and implementing the use of a transaction in NHibernate appeared to help.

However I have ran into one snag. I am now unable to save both lists. I have pre-saved all my intersections, vehicles, and zones before adding the lists to the Vehicles and the Zones. However, if I include the list of Vehicles in the Zone mapping I obtain an error trying to update the Vehicles and the lists. Here is the code:

using (var tx = session.BeginTransaction())
{
    foreach (Vehicle vehicle in Program.data.Vehicles.list)
    {
        session.Update(vehicle);
    }
    tx.Commit();
}

I receive a Stack Overflow exception. Any ideas?

Any idea how to get this to go through?

Edit Is there no way to map the list of Vehicles in the Zone class and the list of Zones in the Vehicle class to the same table so it is not iterating through both lists recursively? The lists are related to each other. One is a list of Zones that a Vehicle belongs to and the other is a list of Vehicles that a Zone belongs to. I don't think I have that mapped correctly after digging more into this.

EDIT I have been making progress with the correct mappings I believe. I posted a more simplified version of the mappings for Device which maps the Intersection and Vehicle classes along with a simplified version of Zone which of course maps the Zone class.

This appears to save relatively well with one exception. Saving large numbers of items is tossing out a Stack Overflow exception for some reason. Here is the approach I am using:

//  Create a Transaction for batch updating
using (var tx = session.BeginTransaction())
{
    foreach (Vehicle veh in Program.data.Vehicles.list)
    {
        session.Save(veh);
    }
    //  Commit transactions
    tx.Commit();
}

This works fine for say 250 Intersections, 500 zones, and 1000 Vehicles. However, I was attempting to create a test set of data to stress this in a more extreme environment and bumped up to 2400 Intersections, 9600 Zones, and 5000 Vehicles and I run across a Stack Overflow exception when trying to save the test data into the database via this manner.

Any ideas?

---

Although not an answer, I'd suggest NHibernate isn't the best solution for saving 500K records, although I accept that it's sometimes difficult to replace just that piece of code with (e.g.) stored procedures.

You could consider a stateless session, as this is optimised for speed but doesn't track object changes. Another thing would be to find out what SQL is being executed. Are you seeing updates that you don't expect? In this case there is probably mistakes in your mapping files.

In the end though, if you've made a lot of data changes then NH needs to flush them to the DB, and it needs to keep track of loaded objects to track those changes. At 500K records, that's a lot of data to keep in memory.

---

It appears NHibernate is recursively saving the items within both the lists

you don't have to guess; nhibernate has great logging mechanisms that show you exactly what sql is generated. It might be a good idea to see what exactly it does and post it back here for analysis.

secondly, nHib is not really intended for these kinds of volumes of data.
see the suggestions I received here.
the best advice I can give you is the same one I received- don't load 10,000 objects into memory at once! It's just not good practice, not matter what data access method you're using.

---

It was actually recursively saving them b/c I had the mapping incorrect. I needed an inverse relation on one side and then I needed to modify the mapping to point to the foreign key as well. Finally I needed a Modified field there to tell NHibernate that the item had been modified and that it needed updating.

Here is the correct mapping:

Vehicle:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class xmlns="urn:nhibernate-mapping-2.2" name="EMTRAC.Devices.Device, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`Device`">
    <id name="PK" type="System.Int64, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="PK" />
      <generator class="identity" />
    </id>
    <version name="LastModifiedOn" column="LastModifiedOn" type="timestamp" access="field.pascalcase-underscore" />
    <joined-subclass name="EMTRAC.Vehicles.Vehicle, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
      <key>
        <column name="Device_id" />
      </key>      
      <component name="Zones" access="property">
        <bag name="_list" cascade="save-update" access="field" table="VehicleZones" inverse="true">
          <key>
            <column name="veh_id" not-null="true"/>
          </key>
          <many-to-many class="EMTRAC.Zones.Zone, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>
        </bag>
      </component>
      <property name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
        <column name="ID" />
      </property>     
  </class>
</hibernate-mapping>

Zone:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class xmlns="urn:nhibernate-mapping-2.2" name="EMTRAC.Zones.Zone, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`Zone`">
    <id name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="PK"/>
      <generator class="identity" />
    </id>
    <version name="LastModifiedOn" column="LastModifiedOn" type="timestamp" access="field.pascalcase-underscore" />
    <property name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="ID" />
    </property>    
    <component name="Vehicles" access="property">
      <bag name="_list" cascade="save-update" access="field" table="VehicleZones">
        <key>
          <column name="veh_id" not-null="true"/>
        </key>
        <many-to-many class="EMTRAC.Vehicles.Vehicle, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>
      </bag>
    </component>
  </class>
</hibernate-mapping>