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
}
}