[nhibernate-issues] [JIRA] Created: (NH-774) Oracle Performance using Load

[NHibernate J. <mik...@us...>]

Oracle Performance using Load
-----------------------------

         Key: NH-774
         URL: http://jira.nhibernate.org/browse/NH-774
     Project: NHibernate
        Type: Patch
  Components: Data Providers  
    Versions: 1.0, 1.0.2    
    Reporter: Jorge
 Attachments: PerformanceNHibernate.rar

I have developed a application using NHibernate version 1.0.2.0 (in the version 1.0.0.0 the same behaviour) over ORACLE version 10.2.0.1.0 in the Server and the same version for the Oracle Client.
I am using C#, .NET 2.0 and Visual Studio 2005.

I have a table with more than 1 million records, and the object to build has one property for each field in the table. It means nothing special.

When i am trying to load one object using the primary key with session.Load() spends about 3 seconds, while if i use session.CreateSQLQuery() to get the same object using the primary key as weel it spends about 0,05 seconds. the query using CreateSQLQuery is:

SELECT {a.*} FROM REGDI a WHERE REGDIID='" + myREGDIID + "'";

and query in the Load is :

SELECT regdi0_.REGDIID as REGDIID0_, regdi0_.REGID as REGID0_, regdi0_.DITAHUN as DITAHUN0_, regdi0_.DIKODE as DIKODE0_, regdi0_.DINOMOR as DINOMOR0_, regdi0_.DITANGGAL as DITANGGAL0_ FROM REGDI regdi0_ WHERE regdi0_.REGDIID=:p0



If do the same test with a few data in the table (about 10,000) is working both methods quickly. But if the table has 1 million records the load take a long time.

I have debud the application and the problem is in the method DoQuery() in the class NHibernate.Loader.Loader. And in the line 326:
for( count = 0; count < maxRows && rs.Read(); count++ )

To do the rs.Read() takes about 3 seconds.
the object rs is a System.Data.OracleClient.OracleDataReader
and the query to do is :
SELECT regdi0_.REGDIID as REGDIID0_, regdi0_.REGID as REGID0_,
regdi0_.DITAHUN as DITAHUN0_, regdi0_.DIKODE as DIKODE0_,
regdi0_.DINOMOR as DINOMOR0_, regdi0_.DITANGGAL as DITANGGAL0_
FROM REGDI regdi0_ WHERE regdi0_.REGDIID=:p0

I think there is something strange building the OracleCommand inside the NHibernate. Someone else has the same problem?? Anyone has some clues?

I attach a small project in VS2005 showing the problem.

Thank you in advance.
Jorge.



********* XML Mapping *****
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
 <class name="PerformanceNHibernate.RegDI, PerformanceNHibernate"
        table="REGDI"   >
   <id name="RDI_RegDIId" column="REGDIID" type="String">
     <generator class="uuid.hex"/>
   </id>
   <property name ="RDI_RegisterLink" column ="REGID" type ="String"/>
   <property column="DIKODE" type="String" name="RDI_DICode" not-null="true" length="6" />
   <property column="DINOMOR" type="String" name="RDI_DINomor" length="30" />
   <property column="DITAHUN" type="Int32" name="RDI_DIYear" />
   <property column="DITANGGAL" type="DateTime" name="RDI_DIDate" />
 </class>
</hibernate-mapping>


******* Class Definition ********

using System;

namespace PerformanceNHibernate
{
   [Serializable]
   public class RegDI
   {
       private string _regdiid;//identifier
       private string _regid;
       private string _dicode;
       private string _dinomor;
       private int? _diyear;
       private DateTime? _didate;

       public RegDI()
       {
       }
       public string RDI_RegDIId
       {
           get { return _regdiid; }
           set { _regdiid = value; }
       }
       public string RDI_RegisterLink
       {
           get { return _regid; }
           set { _regid = value; }
       }
       /// <summary>
       ///
       /// </summary>              public string RDI_DICode
       {
           get { return _dicode; }
           set { _dicode = value; }
       }
       /// <summary>
       ///
       /// </summary>              public string RDI_DINomor
       {
           get { return _dinomor; }
           set { _dinomor = value; }
       }
       /// <summary>
       ///
       /// </summary>              public int? RDI_DIYear
       {
           get { return _diyear; }
           set { _diyear = value; }
       }
       /// <summary>
       ///
       /// </summary>              public DateTime? RDI_DIDate
       {
           get { return _didate; }
           set { _didate = value; }
       }

       public override bool Equals(object obj)
       {
           bool response = false;
           if (this.RDI_RegDIId.Equals(((RegDI)obj).RDI_RegDIId))
           {
               response = true;
           }
           return response;
       }

       public override int GetHashCode()
       {
           return ((String)this.RDI_RegDIId).GetHashCode();
       }


   }//end of public class
}//end of namespace


******* Table in ORACLE ************

CREATE TABLE REGDI (
   REGDIID    VARCHAR2(32) NOT NULL,
   REGID      VARCHAR2(32) NOT NULL,
   DIKODE     VARCHAR2(6) NOT NULL,
   DINOMOR    VARCHAR2(30),
   DITAHUN    NUMBER(4),
   DITANGGAL  DATE);

ALTER TABLE REGDI ADD (
   CONSTRAINT PK_REGDI PRIMARY KEY (REGDIID));

CREATE INDEX I1TRANSDI ON REGDI (DIKODE, DINOMOR, DITAHUN);

CREATE INDEX I2TRANSDI ON REGDI (DIKODE, DITAHUN, DINOMOR);

CREATE INDEX I2REGDI ON REGDI (REGID, DIKODE);


***************************** 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://jira.nhibernate.org/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira