nhibernate-issues — Updates to JIRA issues are sent to this list

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

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

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

    Resolution: Not an Issue

(By the way, AnsiString corresponds to CHAR/VARCHAR while String corresponds to NCHAR/NVARCHAR, i.e. String is capable of storing Unicode while AnsiString is not.)

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

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

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

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

Good to hear you got it solved!

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

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

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

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

I have change the mapping file, changing the 
type="String" 
to type ="AnsiString" length ="32" (The length is not mandatory)

And now is working fine with the NHibernate version 1.0.2.0

I thought the type string and AnsiString was more or less the same in NHibernate, but don't.

Thanks to all, specially Ayende and Sergei,
Jorge.-

<?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 ="AnsiString" length ="32">
      <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>

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

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

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

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

I have tried to reproduced the bevaviour using only ADO.Net and i ve got. This is the code:
(Using the Oracle driver from Microsoft System.Data.OracleClient).

  OracleCommand myOraComm = new OracleCommand();
            myOraComm.CommandText = " 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";
            myOraComm.CommandType = CommandType.Text;
            myOraComm.UpdatedRowSource = UpdateRowSource.Both;
            OracleParameter myParam = myOraComm.CreateParameter();
            //OracleParameter myParam = new OracleParameter("p0", this.txtID.Text);
            myParam.DbType = DbType.String;
            myParam.ParameterName = ":p0";
            myOraComm.Parameters.Add(myParam);
            OracleParameter myParam2 = myOraComm.Parameters[0];
            myParam2.Value = this.txtID.Text;
            //myOraComm.Parameters[0].Value = this.txtID.Text;
            OracleConnection myOraCon = new OracleConnection("data source=;user id=nhibtest;password=nhibtest");
            myOraCon.Open();
            myOraComm.Connection = myOraCon; // (OracleConnection)myDataAccess.mySession.Connection;
            OracleDataReader myOraDR = myOraComm.ExecuteReader();
            System.DateTime before = DateTime.Now;
            myOraDR.Read();
            this.txtRegID.Text = Convert.ToString(myOraDR[1]);
            System.DateTime after = DateTime.Now;
            this.txtTime.Text = Convert.ToString(after - before);

If i comment the line :
            myParam.DbType = DbType.String;

The code is working fine and quickly.

Also if use the Oracle Driver from Oracle "Oracle.DataAcces" the code is working fine.

So the problem is in the Driver from Microsoft. Isn't ?

Thanks,
Jorge.

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

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

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

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

I have included the last vs2005 project, using OracleDataReader with and without parameters. The performance is quite well. With parameters takes around 0,02 seconds to read the DataReader. Without parameters about 0,01 seconds.

Thanks,
Jorge.

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

[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: PerformanceNHibernate.rar

The project using DataReader with and without parameters

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

[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

[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

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

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

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

But the version 1.2.0 is still a Beta, and i am going to put in "production" the application. I would like to use the last stable version.

Ayende, i dont get exactly what you mean, but if I run the test with a table with 10.000 records works fine and quicly, but i use a table with 1,5 millions records, take a long time the Load, while the CreateSQLQuery is fast.
So, i am afraid the problem is not in the application trying to read the mapping file. I am afraid the Load() ask to Oracle something else than the query.
Do you mean in the mapping file there is something 
Does NHIbernate lock the table in Oracle to run the Load()?

Regards and thanks,
Jorge.

> 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

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

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

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

Yes but it executes the exact same* query and processes the result set in the exact same way, so I don't understand why there's the time difference. Also, I don't understand why would rs.Read() take 3 seconds in one case but not in the other.

* The only difference is that the CreateSQLQuery variant doesn't use parameters, while Load does.

> 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

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

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

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

One thing that may cause this is that Load() respects the mapping file configuration, while CreateQuery (or CreateSqlQuer) always ignores it.

> 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

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

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

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

Also, have you tried the 1.2.0.Beta1 version? The bug, if any, may have already been fixed.

> 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

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

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

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

%d should produce output like this: 2006-10-25 08:52:17,093. I don't understand why it's different in your case.

> 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

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

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

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

The DEBUB i ve put in this page, are with this pattern:
"%d [%t] %-5p %c [%x] <%P{user}> - %m%n"

Is it the "%d" the time?

Thanks,



> 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

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

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

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

Sorry Sergey, i am trying to set the App.config to show the timespamp and i am stuck. 
I have this lines in my App.config but it seems is not enough to show the time in the Console.
<appender name="console" type="log4net.Appender.ConsoleAppender, log4net">
      <layout type="log4net.Layout.PatternLayout,log4net">
        <param name="ConversionPattern" value="%r - %timestamp - %d [%t] %-5p %c [%x] &amp;lt;%P{user}&amp;gt; - %m%n" />
      </layout>
</appender> 

I have added the "%r - %timestamp -" in the pattern, but it does not work.
Any idea??
I have followed this instruction:
http://wiki.nhibernate.org/display/NH/Configuring+log4net+Logging
Thank you very much,
Jorge.

> 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

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

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

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

Hmm, I must admit I am puzzled by this. Is the left column of the logs the time elapsed? If not, can you run the test again, this time also logging the time of each message (%r or %timestamp pattern)?

> 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

[nhibernate-issues] [JIRA] Updated: (NH-775) IQuery.SetResultTransformer() doesn't work

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

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

Sergey Koshcheyev updated NH-775:
---------------------------------

    Fix Version: 1.2.0.Beta2

> IQuery.SetResultTransformer() doesn't work
> ------------------------------------------
>
>          Key: NH-775
>          URL: http://jira.nhibernate.org/browse/NH-775
>      Project: NHibernate
>         Type: Bug
>   Components: Core
>     Versions: 1.2.0.Beta1
>     Reporter: Dario Cecere
>     Priority: Minor
>      Fix For: 1.2.0.Beta2

>
> (already posted on forum and approved as bug by Sergey)
> NHibernate does not call the IResultTransformer Interface Object within a situation like this:
> IQuery q = oz.CreateQuery("select users.Username, roles from Domain.EUser as users inner join users.Roles as roles where users.Username = 'admin'"); 
> q = q.SetResultTransformer(o); 
> IEnumerable l = q.Enumerable(); 
> The same with List(), and i figure also with generic-methods
> Hope i can help more

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

[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

[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: Load.txt

DEBUG using Load

> 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

[nhibernate-issues] [JIRA] Created: (NH-775) IQuery.SetResultTransformer() doesn't work

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

IQuery.SetResultTransformer() doesn't work
------------------------------------------

         Key: NH-775
         URL: http://jira.nhibernate.org/browse/NH-775
     Project: NHibernate
        Type: Bug
  Components: Core  
    Versions: 1.2.0.Beta1    
    Reporter: Dario Cecere
    Priority: Minor


(already posted on forum and approved as bug by Sergey)
NHibernate does not call the IResultTransformer Interface Object within a situation like this:

IQuery q = oz.CreateQuery("select users.Username, roles from Domain.EUser as users inner join users.Roles as roles where users.Username = 'admin'"); 

q = q.SetResultTransformer(o); 
IEnumerable l = q.Enumerable(); 

The same with List(), and i figure also with generic-methods

Hope i can help more


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

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

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

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

No, that is not what we meant, please see here how to enable _nhibernate_ debug logging:

http://wiki.nhibernate.org/display/NH/Configuring+log4net+Logging


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

[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: CreateQuery.txt

Debub level Output 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, 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

[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: Load.txt

The Debub level Output using session.Load(id)

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

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

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

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

Please attach DEBUG-level log output for the two cases (CreateQuery vs Load).

> 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

[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