I am facing very strange issue re NHibernate and now it is become headache.
NHibernate is taking longer time (2-3 minute) than expected time (few milliseconds) to execute such a simple query. The database is Oracle and I am using ODP driver. I have checked all necessary configuration re NHibernate and Spring which looks ok to me. When I execute the same query in sqldeveloper, it is giving result in milliseconds.
FYI - When I execute another query which has three inner join with complex model with the same NHibernate configuration, I am getting the result as expected.
In the debug log, I could see below lines where it is wasting time:
2012-08-17 09:53:20,754 [TestRunnerThread] DEBUG - NHibernate.Connection.DriverConnectionProvider - Obtaining IDbConnection from Driver
2012-08-17 09:55:09,369 [TestRunnerThread] DEBUG - NHibernate.AdoNet.AbstractBatcher - ExecuteReader took 108720 ms
NHibernate property settings:
<nhibernatePropertiesSettings>
<setting name="nhibernate.connection.provider" serializeAs="String">
<value>NHibernate.Connection.DriverConnectionProvider</value>
</setting>
<setting name="nhibernate.connection.driver.class" serializeAs="String">
<value>NHibernate.Driver.OracleDataClientDriver</value>
</setting>
<setting name="nhibernate.dialect" serializeAs="String">
<value>NHibernate.Dialect.Oracle10gDialect</value>
</setting>
<setting name="nhibernate.show.sql" serializeAs="String">
<value>true</value>
</setting>
<setting name="nhibernate.query.substitutions" serializeAs="String">
<value>true 1, false 0, yes 'Y', no 'N'</value>
</setting>
<setting name="nhibernate.use.proxy.validator" serializeAs="String">
<value>false</value>
</setting>
<setting name="nhibernate.template.flush.mode" serializeAs="String">
<value>Never</value>
</setting>
</nhibernatePropertiesSettings>
Spring property setting:
<springPropertiesSettings>
<setting name="spring.db.provider" serializeAs="String">
<value>OracleODP-11-2.0</value>
</setting>
</springPropertiesSettings>
Mapping:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="XYZ.PaymentInvestigation.Service.Model.PaymentMessageHistory, PaymentInvestigation.Service.Model" table="MESSAGE_HIST_T" lazy="false">
<composite-id name="PaymentMessageHistoryId" class="XYZ.PaymentInvestigation.Service.Model.PaymentMessageHistoryId, PaymentInvestigation.Service.Model" unsaved-value="undefined">
<key-property name="TransactionDate" column="TRN_DATE" />
<key-property name="TransactionReferenceNumber" column="TRN_NUMBER" />
<key-property name="TransactionTimeStamp" column="TRN_TIMESTAMP" />
<key-property name="HistoryNumber" type="AnsiString" column="HIST_NO" />
<key-property name="SubHistoryNumber" type="AnsiString" column="SUB_HIST_NO" />
</composite-id>
<property name="EntryType" column="ENTRY_TYPE" update="false" insert="false" />
<property name="Location" column="LOC" update="false" insert="false" />
<property name="QueLineId" column="QUE_LINE_ID" update="false" insert="false" />
<property name="DateTime" column="DATE_TIME" update="false" insert="false" />
<property name="SequenceNo" column="SEQUENCE_NO" update="false" insert="false" />
<property name="OperatorInitials" column="OPR_INITIALS" update="false" insert="false" />
<property name="Amount" column="AMOUNT" update="false" insert="false" />
<property name="MsgInfo" column="MSG_INFO" update="false" insert="false" />
<property name="RecordExpired" column="RECORD_EXPIRED" update="false" insert="false" />
<property name="RecordUpdated" column="RECORD_UPDATED" update="false" insert="false" />
<property name="Details" column="DETAILS" update="false" insert="false" />
</class>
Query:
IList<PaymentMessageHistory> paymentMessageHistories =
HibernateTemplate.ExecuteFind(session => session
.QueryOver<PaymentMessageHistory>()
.Where(x =>
x.PaymentMessageHistoryId.TransactionDate == paymentMessageId.TransactionDate &&
x.PaymentMessageHistoryId.TransactionReferenceNumber == paymentMessageId.TransactionReferenceNumber)
.List());
PaymentMessageHistoryId Model:
public class PaymentMessageHistoryId : IEquatable<PaymentMessageHistoryId>
{
public virtual DateTime TransactionDate { get; set; }
public virtual int TransactionReferenceNumber { get; set; }
public virtual double TransactionTimeStamp { get; set; }
public virtual string HistoryNumber { get; set; }
public virtual string SubHistoryNumber { get; set; }
public bool Equals(PaymentMessageHistoryId other)
{
if (ReferenceEquals(null, other)) return false;
if (ReferenceEquals(this, other)) return true;
return other.TransactionDate.Equals(TransactionDate) && other.TransactionReferenceNumber == TransactionReferenceNumber && other.TransactionTimeStamp.Equals(TransactionTimeStamp) && Equals(other.HistoryNumber, HistoryNumber) && Equals(other.SubHistoryNumber, SubHistoryNumber);
}
public override bool Equals(object obj)
{
if (ReferenceEquals(null, obj)) return false;
if (ReferenceEquals(this, obj)) return true;
if (obj.GetType() != typeof (PaymentMessageHistoryId)) return false;
return Equals((PaymentMessageHistoryId) obj);
}
public override int GetHashCode()
{
unchecked
{
int result = TransactionDate.GetHashCode();
result = (result*397) ^ TransactionReferenceNumber;
result = (result*397) ^ TransactionTimeStamp.GetHashCode();
result = (result*397) ^ (HistoryNumber != null ? HistoryNumber.GetHashCode() : 0);
result = (result*397) ^ (SubHistoryNumber != null ? SubHistoryNumber.GetHashCode() : 0);
return result;
}
}
public static bool operator ==(PaymentMessageHistoryId left, PaymentMessageHistoryId right)
{
return Equals(left, right);
}
public static bool operator !=(PaymentMessageHistoryId left, PaymentMessageHistoryId right)
{
return !Equals(left, right);
}
public override string ToString()
{
return string.Format("TransactionDate: {0}, TransactionReferenceNumber: {1}, TransactionTimeStamp: {2}, HistoryNumber: {3}, SubHistoryNumber: {4}", TransactionDate, TransactionReferenceNumber, TransactionTimeStamp, HistoryNumber, SubHistoryNumber);
}
PaymentMessageHistory Model:
public class PaymentMessageHistory : IEquatable<PaymentMessageHistory>
{
public virtual PaymentMessageHistoryId PaymentMessageHistoryId { get; set; }
public virtual string EntryType { get; set; }
public virtual string Location { get; set; }
public virtual string QueLineId { get; set; }
public virtual string DateTime { get; set; }
public virtual string SequenceNo { get; set; }
public virtual string OperatorInitials { get; set; }
public virtual decimal Amount { get; set; }
public virtual string MsgInfo { get; set; }
public virtual double RecordExpired { get; set; }
public virtual string RecordUpdated { get; set; }
public virtual string Details { get; set; }
public bool Equals(PaymentMessageHistory other)
{
if (ReferenceEquals(null, other)) return false;
if (ReferenceEquals(this, other)) return true;
return Equals(other.PaymentMessageHistoryId, PaymentMessageHistoryId) && Equals(other.EntryType, EntryType) && Equals(other.Location, Location) && Equals(other.QueLineId, QueLineId) && Equals(other.DateTime, DateTime) && Equals(other.SequenceNo, SequenceNo) && Equals(other.OperatorInitials, OperatorInitials) && other.Amount == Amount && Equals(other.MsgInfo, MsgInfo) && other.RecordExpired.Equals(RecordExpired) && Equals(other.RecordUpdated, RecordUpdated) && Equals(other.Details, Details);
}
public override bool Equals(object obj)
{
if (ReferenceEquals(null, obj)) return false;
if (ReferenceEquals(this, obj)) return true;
if (obj.GetType() != typeof (PaymentMessageHistory)) return false;
return Equals((PaymentMessageHistory) obj);
}
public override int GetHashCode()
{
unchecked
{
int result = (PaymentMessageHistoryId != null ? PaymentMessageHistoryId.GetHashCode() : 0);
result = (result*397) ^ (EntryType != null ? EntryType.GetHashCode() : 0);
result = (result*397) ^ (Location != null ? Location.GetHashCode() : 0);
result = (result*397) ^ (QueLineId != null ? QueLineId.GetHashCode() : 0);
result = (result*397) ^ (DateTime != null ? DateTime.GetHashCode() : 0);
result = (result*397) ^ (SequenceNo != null ? SequenceNo.GetHashCode() : 0);
result = (result*397) ^ (OperatorInitials != null ? OperatorInitials.GetHashCode() : 0);
result = (result*397) ^ Amount.GetHashCode();
result = (result*397) ^ (MsgInfo != null ? MsgInfo.GetHashCode() : 0);
result = (result*397) ^ RecordExpired.GetHashCode();
result = (result*397) ^ (RecordUpdated != null ? RecordUpdated.GetHashCode() : 0);
result = (result*397) ^ (Details != null ? Details.GetHashCode() : 0);
return result;
}
}
public static bool operator ==(PaymentMessageHistory left, PaymentMessageHistory right)
{
return Equals(left, right);
}
public static bool operator !=(PaymentMessageHistory left, PaymentMessageHistory right)
{
return !Equals(left, right);
}
public override string ToString()
{
return string.Format("PaymentMessageHistoryId: {0}, EntryType: {1}, Location: {2}, QueLineId: {3}, DateTime: {4}, SequenceNo: {5}, OperatorInitials: {6}, Amount: {7}, MsgInfo: {8}, RecordExpired: {9}, RecordUpdated: {10}, Details: {11}", PaymentMessageHistoryId, EntryType, Location, QueLineId, DateTime, SequenceNo, OperatorInitials, Amount, MsgInfo, RecordExpired, RecordUpdated, Details);
}
I went through with this NHibernate taking a long time to run query but didn't work for me.
Please help!
Regards, Milind
One more simple solution than inheriting oracle driver class is to just add type attribute in your property and value will be "Date". this is NHibernate type.
<key-property name="TransactionDate" type="Date" column="TRN_DATE" />