Performance problems using NHibernate

sir-archimedes · **Joined:** Mon Mar 20, 2006 7:59 am **Posts:** 30

Hibernate version: 1.20 Alpha

Hello,

I am trying to improve the performance of my ASP.NET application which is built using NHibernate. I have a quite complicated class model and fetching the data takes quite a lot of time:
100 objects: with caching 8 seconds
100 objects: without caching 8,84 seconds

You can see that an improvement is absolutely necessary.

The objects that I was fetching are of a class that is called Task.

A Task constists of several Action-objects. Each Action-Object can have a responsible User and a responsible Department. Further several WorkItem/Costs/File-Objects can be assigned to each Action. At least a CostsCategory object gets assigned to each Costs-Object.

In my test I fetch 100 times a Task object and calculated the total costs for this task. (i.e. iterating through all assigned Action-Objects and then all Costs-Objects) My problem now is that I need to display the Task-Objects in a list together with the number of actions and the total costs and total work. Therefore all the data needs to be fetched.

Perhaps you can find any issues in my mapping file or in the NHibernate configuration file.

First the mapping file:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" assembly="AMS.Core" namespace="AMS.Core.Domain">
  <class name="User" table="Users" lazy="true">
    <id name="Id" column="Id" unsaved-value="0">
      <generator class="native" />
    </id>

    <property name="UserName" column="UserName" length="255" not-null="true" unique="true"/>
    <property name="ForeName" column="ForeName" length="50" />
    <property name="Name" column="Name" length="50" />
    <property name="Address" column="Address" length="50" />
    <property name="PostCode" column="PostCode" length="7" />
    <property name="City" column="City" length="50" />
    <property name="Phone1" column="Phone1" length="50" />
    <property name="Phone2" column="Phone2" length="50" />
    <property name="Fax" column="Fax" length="50" />
    <property name="Mobile" column="Mobile" length="50" />
    <property name="Comment" column="Comment" length="1073741823" />
    <many-to-one name="Department" column="DepartmentId" class="Department" outer-join="true"/>
    
    <property name="CreationDate" column="CreationDate" not-null="true"/>
    <property name="LastUpdateDate" column="LastUpdateDate" />
  </class>

  <class name="Department" table="Departments" lazy="true">
    <id name="Id" column="Id" unsaved-value="0">
      <generator class="native" />
    </id>

    <property name="Name" column="Name" length="50" />

    <many-to-one name="CreatedBy" column="CreatedByUserId" class="User" not-null="true" />
    <many-to-one name="LastUpdatedBy" column="LastUpdatedByUserId" class="User" />
    <property name="CreationDate" column="CreationDate" not-null="true"/>
    <property name="LastUpdateDate" column="LastUpdateDate" />
    <property name="InActive" column="InActive" />
  </class>

  <class name="CostCategory" table="CostCategories">
    <id name="Id" column="Id" unsaved-value="0">
      <generator class="native" />
    </id>

    <property name="Name" column="Name" length="50" />
    <property name="Description" column="Description" length="1073741823" />

    <many-to-one name="CreatedBy" column="CreatedByUserId" class="User" not-null="true"/>
    <many-to-one name="LastUpdatedBy" column="LastUpdatedByUserId" class="User" />
    <property name="CreationDate" column="CreationDate" not-null="true"/>
    <property name="LastUpdateDate" column="LastUpdateDate" />
    <property name="InActive" column="InActive" />
  </class>

  <class name="TaskCategory" table="TaskCategories" lazy="true">
    <id name="Id" column="Id" unsaved-value="0">
      <generator class="native" />
    </id>

    <property name="Name" column="Name" length="50" unique="true"/>
    <property name="Description" column="Description" length="1073741823" />

    <many-to-one name="CreatedBy" column="CreatedByUserId" class="User" not-null="true"/>
    <many-to-one name="LastUpdatedBy" column="LastUpdatedByUserId" class="User" />
    <property name="CreationDate" column="CreationDate" not-null="true"/>
    <property name="LastUpdateDate" column="LastUpdateDate" />
    <property name="InActive" column="InActive" />
  </class>

  <class name="Costs" table="Costs" lazy="true">
    <id name="Id" column="Id" unsaved-value="0">
      <generator class="native" />
    </id>

    <property name="Description" column="Description" length="100" />
    <property name="Amount" column="Amount" />
    <many-to-one name="Category" column="CategoryId" class="CostCategory" />
    <many-to-one name="Action" class="Action" 
        column="ActionId" not-null="true"
        update="false"/>

    <many-to-one name="CreatedBy" column="CreatedByUserId" class="User" not-null="true"/>
  </class>

  <class name="WorkItem" table="WorkItems" lazy="true">
    <id name="Id" column="Id" unsaved-value="0">
      <generator class="native" />
    </id>

    <property name="Description" column="Description" length="50" />
    <property name="Duration" column="Duration" />

    <many-to-one name="Action" class="Action" 
        column="ActionId" not-null="true"
        update="false"/>

    <many-to-one name="CreatedBy" column="CreatedByUserId" class="User" not-null="true"/>
    <property name="CreationDate" column="CreationDate" not-null="true"/>
  </class>

  <class name="Action" table="Actions" lazy="true">
    <id name="Id" column="Id" unsaved-value="0">
      <generator class="native" />
    </id>

    <property name="Status" column="Status" />
    <property name="TimeLimit" column="TimeLimit" />
    <property name="Description" column="Description" length="1073741823" />
    <property name="Comment" column="Comment" length="1073741823" />
    <property name="Title" column="Title" length="50" />

    <bag name="Costs" lazy="true" generic="true" cascade="all-delete-orphan" inverse="true" >
      <cache usage="read-write"/>
      <key column="ActionId" />
      <one-to-many class="Costs"/>
    </bag>

    <bag name="Work" lazy="true" generic="true" cascade="all-delete-orphan" inverse="true" >
      <cache usage="read-write"/>
      <key column="ActionId" />
      <one-to-many class="WorkItem"/>
    </bag>

    <many-to-one name="Department" column="DepartmentId" class="Department" />
    <many-to-one name="PersonInCharge" column="PersonInChargeId" class="User" />

    <many-to-one name="Task" class="Task" 
        column="TaskId" 
        insert="false" cascade="save-update"/>

    <bag name="Files" lazy="true" generic="true" cascade="all-delete-orphan" >
      <cache usage="read-write"/>
      <key column="ActionId" />
      <one-to-many class="File"/>
    </bag>

<!--
    <property name="FileCount" formula="(SELECT COUNT(*) FROM Files a WHERE a.ActionId = Id)" />
    <property name="ActualWork" formula="(SELECT SUM(wi.Duration) FROM WorkItems wi WHERE wi.ActionId = Id)" />
-->
    <property name="StartDate" column="StartDate" />
    <property name="EndDate" column="EndDate" />

    <property name="PlannedWork" column="PlannedWork"/>
    <property name="PlannedCosts" column="PlannedCosts"/>

    <many-to-one name="CreatedBy" column="CreatedByUserId" class="User" not-null="true" />
    <many-to-one name="LastUpdatedBy" column="LastUpdatedByUserId" class="User" />
    <property name="CreationDate" column="CreationDate" not-null="true"/>
    <property name="LastUpdateDate" column="LastUpdateDate" />
  </class>

  <class name="Task" table="Tasks" >
    <id name="Id" column="Id" unsaved-value="0">
      <generator class="native" />
    </id>

    <property name="Title" column="Title" length="50" />
    <property name="Priority" column="Priority" />
    <property name="Status" column="Status" />
    <property name="TimeLimit" column="TimeLimit" />
    <property name="TaskEditPermission" column="TaskEditPermission" />
    <property name="ActionEditPermission" column="ActionEditPermission" />
    <property name="ActionLinkType" column="ActionLinkType" />

    <property name="Description" column="Description" length="1073741823" />

    <list name="Actions" lazy="true" generic="true" cascade="all-delete-orphan" >
      <cache usage="read-write"/>
      <key column="TaskId" />
      <index column="Position" />
      <one-to-many class="Action"/>
    </list>

    <bag name="Files" lazy="true" generic="true" cascade="all-delete-orphan" >
      <cache usage="read-write"/>
      <key column="TaskId" />
      <one-to-many class="File"/>
    </bag>    

    <many-to-one name="Category" column="CategoryId" class="TaskCategory" />
    <many-to-one name="Contact" column="ContactID" class="Contact" />

    <property name="StartDate" column="StartDate" />
    <property name="EndDate" column="EndDate" />


    <!--    <property name="ActionCount" formula="(SELECT COUNT(*) FROM Actions a WHERE a.TaskId = Id)" />
    <property name="FileCount" formula="(SELECT COUNT(*) FROM Files a WHERE a.TaskId = Id)" />
    <property name="PlannedCosts" formula="(SELECT SUM(act.PlannedCosts) FROM Actions act WHERE act.TaskId = Id)" />
    -->

    <many-to-one name="CreatedBy" column="CreatedByUserId" class="User" not-null="true"/>
    <many-to-one name="LastUpdatedBy" column="LastUpdatedByUserId" class="User" />
    <property name="CreationDate" column="CreationDate" not-null="true"/>
    <property name="LastUpdateDate" column="LastUpdateDate" />
    <property name="InActive" column="InActive" />
  </class>

  <class name="FileData" table="FileData" lazy="true">
    <id name="Id" column="Id" unsaved-value="0">
      <generator class="foreign">
        <param name="property">File</param>
      </generator>
    </id>

    <property name="Data" column="Data" type="BinaryBlob"  length="1073741823" />
    <one-to-one class="File" name="File" constrained="true" />
  </class>

  <class name="File" table="Files" lazy="true">
    <id name="Id" column="Id" unsaved-value="0">
      <generator class="native" />
    </id>

    <property name="FileName" column="FileName" length="100" />
    <property name="Size" column="Size" />
    <!--    <one-to-one name="FileData" class="FileData" cascade="all-delete-orphan" fetch="select" /> -->

    <one-to-one name="FileData" class="FileData" cascade="all-delete-orphan" />
    <!--    <one-to-one name="FileData" class="FileData" property-ref="File" cascade="all-delete-orphan" constrained="true" /> -->

    <property name="Description" column="Description" length="100" />

    <many-to-one name="CreatedBy" column="CreatedByUserId" class="User" not-null="true"/>
    <many-to-one name="LastUpdatedBy" column="LastUpdatedByUserId" class="User" />

    <property name="CreationDate" column="CreationDate" not-null="true"/>
    <property name="LastUpdateDate" column="LastUpdateDate" />
    <property name="InActive" column="InActive" />
  </class>

</hibernate-mapping>

Here the NHibernate-Configuration file:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.0" >
  <session-factory name="AMS">
    <!-- CONNECTION SETTINGS -->

    <!-- MSSQL-Settings -->
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="connection.connection_string">Here the connection string</property>
    <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
    <property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
    <property name="connection.isolation">ReadCommitted</property>
    <property name="default_schema">AMSDB_DEVEL.dbo</property>

    <!-- MISCELLANEOUS SETTINGS -->
    <property name="show_sql">true</property>
    <property name="use_outer_join">true</property>

    <!-- PERFORMANCE/CACHING-SETTINGS -->
    <property name="use_reflection_optimizer">true</property>
    <property name="cache.provider_class">NHibernate.Caches.SysCache.SysCacheProvider, NHibernate.Caches.SysCache</property>
    <property name="cache.use_query_cache">true</property> 
    <property name="cache.use_query_cache">false</property>
    <property name="relativeExpiration">600</property>
    <property name="Expiration">600</property>

    <!-- MAPPING FILES -->
    <mapping assembly="AMS.Core"/>

    <!-- CACHE-DEFINITION -->
    <class-cache class="AMS.Core.Domain.User, AMS.Core" usage="nonstrict-read-write"/>
    <class-cache class="AMS.Core.Domain.Department, AMS.Core" usage="nonstrict-read-write"/>
    <class-cache class="AMS.Core.Domain.CostCategory, AMS.Core" usage="nonstrict-read-write"/>
    <class-cache class="AMS.Core.Domain.Contact, AMS.Core" usage="nonstrict-read-write"/>
    <class-cache class="AMS.Core.Domain.TaskCategory, AMS.Core" usage="nonstrict-read-write"/>
    <class-cache class="AMS.Core.Domain.Task, AMS.Core" usage="read-write"/>
    <class-cache class="AMS.Core.Domain.Action, AMS.Core" usage="read-write"/>
    <class-cache class="AMS.Core.Domain.Costs, AMS.Core" usage="read-write"/>
    <class-cache class="AMS.Core.Domain.WorkItem, AMS.Core" usage="read-write"/>
    <class-cache class="AMS.Core.Domain.File, AMS.Core" usage="read-write"/>
  
  </session-factory>
</hibernate-configuration>

I hope anybody sees the problem and is able to show me a solution :-)

Regards and thank you in advance,
sir-archimedes

martijnb · **Joined:** Sat May 14, 2005 8:40 am **Posts:** 130

If you need to display aggregated data it's much faster to create a custom hql query that selects individual properties. This returns a collection with object arrays, representing the properties.

sir-archimedes · **Joined:** Mon Mar 20, 2006 7:59 am **Posts:** 30

I already tried this but in my special case I wouldn't win a lot of performance because I have some other properties that needs to be calculated in my application.

The class Task has a property that is called "CurrentResponsibility". Each Action has a person in charge and/or a department that is responsible. If there is at least one Action for a Task that has the current user as person in charge, then "CurrentReposnibility" of this Task is "ByPersonInCharge". If current user's department is the responsible department for at least one Action, then "CurrentResponsibilty" of the Task is "ByDepartment". If none of these cases take place "CurrentResponsibilty" is "None".

I think this Responsibility-stuff cannot be selected using HQL - therefore I have to fetch all the Actions.

This responsibility property is quite important in my application and I need to display it on the list showing all the tasks a user is responsible for.

luedi · **Joined:** Tue Feb 21, 2006 9:50 am **Posts:** 107

did you think about reading the data via a view? You can map a view like a table. If you need to update the data you have to read the changed records again via your table mapping, transfer the changes and then update the records. This will be a little bit more code as you have to deal with two objects but it should be much faster than your current approach.

Regards
Klaus

sir-archimedes · **Joined:** Mon Mar 20, 2006 7:59 am **Posts:** 30

Hi,

I think using a view is not an option, as a view is nothing else than a saved SQL-Command. (Okay, sometimes a view can be materialized but thats not the usual case) Perhaps I should really think about creating a HQL query that only selects the (aggregated) properties that are important. That is not the kind of solution that I was hoping for, because I really wanted to stick to my domain objects - but that was just slow.

But first I will try to fetch some of the data directly in a property using a "formula". That will help me avoid most of the queries that are fired to the database during fetching and displaying my task-data.

Thank you guys.

dansari · **Joined:** Fri Sep 01, 2006 10:54 pm **Posts:** 3

Have you noticed you have both of these lines in your config file:

<property name="cache.use_query_cache">true</property>
<property name="cache.use_query_cache">false</property>

(I'm a newbie myself, so I don't know what that property controls, exactly.)