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

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

     [ http://jira.nhibernate.org/browse/NH-774?page=all ]

Jorge updated NH-774:
---------------------

    Attachment: CreateSQLQuery.txt

DEBUG using CreateSQLQuery

> 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: CreateQuery.txt, CreateSQLQuery.txt, Load.txt, Load.txt, 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