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

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

     [ http://jira.nhibernate.org/browse/NH-774?page=comments#action_14196 ]
     
Ayende Rahien commented on NH-774:
----------------------------------

Sergey,
That being the case, can it be something with regard to prepared statements + variables?
IIRC, there is a big performance difference with this in Oracle, isn't there?

Jorge,
NH doesn't lock the table.
My guess would be that it is something that is caused by the parameter. Since that seems to be the main difference.

Looking at the logs, the only other difference seems to be that the query first flush and then query, although I can't see how this would make a difference.

> 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