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

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

     [ http://jira.nhibernate.org/browse/NH-774?page=comments#action_14197 ]
     
Sergey Koshcheyev commented on NH-774:
--------------------------------------

Ayende, I'm not familiar with Oracle limitations, but maybe the optimizer fails to use the index when using parameters, or something like that...

Jorge,
can you run the same test using plain ADO.NET? I.e. first, execute SQL "select * from yourtable where id = @p0" and create a parameter and set it to the value you are using, and second, execute SQL "select * from yourtable where id = somevalue". If it shows the same difference, then you should probably talk to your DBA about this, since it would indicate that it's not a problem in NHibernate.

> 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