NHibernate performance question

egodoy · **Joined:** Tue Jun 12, 2007 3:21 pm **Posts:** 3

Hello all!
In our current project we have a table called "Insurance" which has 73,000 records. This table has 4 foreign keys as follows

Users (User ID)
Person (Person ID)
ProgramType (Program Type ID)
Organization (Organization ID)
All of this fields are <many-to-one blah,blah>

When the user tries to get the Insurance records for a given ProgramType for example, the Criteria takes a very, very long time (last time I checked I stopped it after 20 minutes...) The one program type that is most selected, has 5000 records in the Insurance table.
Can anyone tell me what can we do to optimize this task?
Or maybe NHibernate is not the way to go with this kind of requirement.

Thank you very much,

Ernesto L. Godoy

Mapping File:

Code:

[color=red]<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
   <class name="COINS.ModelAccessLayer.NHibernateCore.TableObj.InsuranceCard, COINS.ModelAccessLayer" table="coins.insrnc_card" lazy="true"> 
      <id name="InsuranceCardID" column="insrnc_card_id" type="String" length="38">
         <generator class="uuid.hex" />
      </id>
      <property name="InsuranceStartDate" column="insrnc_card_insrnc_start_dt" type="DateTime" />
      <property name="InsuranceEndDate" column="insrnc_card_insrnc_end_dt" type="DateTime" />
      <property name="SerialNumber" column="insrnc_card_srl_num" type="Nullables.NHibernate.EmptyStringType, Nullables.NHibernate" length="9" />
      <property name="MedicalNotes" column="insrnc_card_med_notes_txt" type="String" length="1000" />
      <property name="PersonChangeCode" column="insrnc_card_prsn_changes_cd" type="String" length="1"  />
      <property name="PrintedIndicator" column="insrnc_card_printd_ind" type="String" length="1" />
      <property name="TransferDate" column="insrnc_card_trnsfr_dt" type="Nullables.NHibernate.NullableDateTimeType, Nullables.NHibernate" />
      <property name="CreatedByID" column="insrnc_card_crtd_id" type="String" length="38" />
      <property name="CreatedDate" column="insrnc_card_crtd_dt" type="DateTime" />
      <property name="UpdatedByID" column="insrnc_card_updt_id" type="String" length="38" />
      <property name="UpdatedDate" column="insrnc_card_updt_dt" type="DateTime" />
      <many-to-one  name="User" class="COINS.ModelAccessLayer.NHibernateCore.TableObj.User, COINS.ModelAccessLayer" column="insrnc_card_usr_id" unique="true" />
      <many-to-one name="Person"  class="COINS.ModelAccessLayer.NHibernateCore.TableObj.Person, COINS.ModelAccessLayer"  column="insrnc_card_prsn_id" unique="true" />
      <many-to-one name="ProgramType" class="COINS.ModelAccessLayer.NHibernateCore.TableObj.ProgramType, COINS.ModelAccessLayer"  column="insrnc_card_pgm_type_id" unique="true" />
      <many-to-one name="Organization" lazy="false" class="COINS.ModelAccessLayer.NHibernateCore.TableObj.Organization, COINS.ModelAccessLayer" column="insrnc_card_org_id" unique="true" />
   </class>
   <sql-query name="serial_number" >
    <return-scalar column="seq" type="Int64"/>
    <![CDATA[
      SELECT COINS.SEQ_SRL_NUM.NEXTVAL as seq FROM DUAL
      ]]>
   </sql-query>
</hibernate-mapping>[/color]

Class File

Code:

using System;
using Nullables;
using System.Collections;


namespace COINS.ModelAccessLayer.NHibernateCore.TableObj
{
   /// <summary>
   /// Summary description for InsuranceCard.
   /// </summary>
   public class InsuranceCard
   {
      #region Internal Variables
      private string insrnc_card_id;
      private DateTime insrnc_card_insrnc_start_dt;
      private DateTime insrnc_card_insrnc_end_dt;
      private string insrnc_card_srl_num;
      private string insrnc_card_med_notes_txt;
      private string insrnc_card_prsn_changes_cd;
      private string insrnc_card_printd_ind;
      private NullableDateTime insrnc_card_trnsfr_dt;
      private User insrnc_card_usr_id;
      private Person insrnc_card_prsn_id;
      private ProgramType insrnc_card_pgm_type_id;
      private string insrnc_card_crtd_id;
      private DateTime insrnc_card_crtd_dt;
      private string insrnc_card_updt_id;
      private DateTime insrnc_card_updt_dt;
      private Organization insrnc_card_org_id;


      #endregion

      #region Properties
      public virtual string InsuranceCardID
      {
         get { return(insrnc_card_id); }
         set { insrnc_card_id = value; }
      }
      public virtual DateTime InsuranceStartDate
      {
         get { return(insrnc_card_insrnc_start_dt); }
         set { insrnc_card_insrnc_start_dt = value; }
      }
      public virtual DateTime InsuranceEndDate
      {
         get { return(insrnc_card_insrnc_end_dt); }
         set { insrnc_card_insrnc_end_dt = value; }
      }
      public virtual string SerialNumber
      {
         get { return(insrnc_card_srl_num); }
         set { insrnc_card_srl_num = value; }
      }
      public virtual string MedicalNotes
      {
         get { return(insrnc_card_med_notes_txt); }
         set { insrnc_card_med_notes_txt = value; }
      }
      public virtual string PersonChangeCode
      {
         get { return(insrnc_card_prsn_changes_cd); }
         set { insrnc_card_prsn_changes_cd = value; }
      }
      public virtual string PrintedIndicator
      {
         get { return(insrnc_card_printd_ind); }
         set { insrnc_card_printd_ind = value; }
      }
      public virtual NullableDateTime TransferDate
      {
         get { return(insrnc_card_trnsfr_dt); }
         set { insrnc_card_trnsfr_dt = value; }
      }
      public virtual User User
      {
         get { return(insrnc_card_usr_id); }
         set { insrnc_card_usr_id = value; }
      }
      public virtual Person Person
      {
         get { return(insrnc_card_prsn_id); }
         set { insrnc_card_prsn_id = value; }
      }
      public virtual ProgramType ProgramType
      {
         get { return(insrnc_card_pgm_type_id); }
         set { insrnc_card_pgm_type_id = value; }
      }
      public virtual string CreatedByID
      {
         get { return(insrnc_card_crtd_id); }
         set { insrnc_card_crtd_id = value; }
      }
      public virtual DateTime CreatedDate
      {
         get { return(insrnc_card_crtd_dt); }
         set { insrnc_card_crtd_dt = value; }
      }
      public virtual string UpdatedByID
      {
         get { return(insrnc_card_updt_id); }
         set { insrnc_card_updt_id = value; }
      }
      public virtual DateTime UpdatedDate
      {
         get { return(insrnc_card_updt_dt); }
         set { insrnc_card_updt_dt = value; }
      }

      public virtual Organization Organization
      {
         get { return(insrnc_card_org_id); }
         set { insrnc_card_org_id = value; }
      }

         #endregion

      #region Constructor
      public InsuranceCard()
      {
      }
      #endregion
   }
}

PandaWood · **Joined:** Wed Oct 25, 2006 10:51 pm **Posts:** 71

Where's the 'Criteria'?

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Most probably the problem is not with NHibernate but with your database. Have you got indexes on the fields you need to query?

Symon.

egodoy · **Joined:** Tue Jun 12, 2007 3:21 pm **Posts:** 3

Sorry!
Bellow is the two different methods that I have tried yielding the same results.
And yes... the tables have indexes. I have tried this by wrapping it with OracleClient ADO.NET and it flies!

Method 1

Code:

public IList GetInusranceList(ArrayList pgmTypeList)
{

IList insrList = session.CreateCriteria(typeof(InsuranceCard)).Add
(NHibernate.Expression.Expression.In("ProgramType",pgmTypeList)).List();

return insrList ;
}

Method 2

Code:

public IList GetInusranceList(ArrayList pgmTypeList)
{

StringBuilder strSql = new StringBuilder();

// Build the SQL string
strSql.Append("SELECT insrnc FROM InsuranceCard AS insrnc WHERE ");
strSql.Append("insrnc.ProgramType.ProgramTypeID IN (:pgmType)");

// Set the Criteria
IQuery qry = NHibernatesession.CreateQuery(strSql.ToString());
qry.SetParameterList("pgmType",pgmTypeList);


insrList = qry.List();

// return list
return insrList ;
}

karlchu · **Posted:** Wed Aug 08, 2007 11:22 am

Your "Organization" many-to-one is declared lazy="false". You may be having the n+1 select problem. Try changing it to lazy="true".

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

Do you really need <one-to-many> mappings for all associations or are there <one-to-one> mappings? Also, if you need "lazy=false" for your organization mapping you should check which associations are defined there, because they are read too.

In general i prefer to use Views for querying lists of data. My user interfaces arenormally built in two steps: First the user gets an overview screen where he can select the data he want's to edit, second there will be a detail view where the user can make modifications to the data. The overview is filled via a view. For the detail view i read just the record the user has selected and it's associations as far as i need them. Doing it this way i can avoid the "n+1 select" problem mostly.

Regards
Klaus