|
From: NHibernate J. <mik...@us...> - 2006-10-25 04:29:30
|
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
|