Hello,

I am trying to pick up the pieces from what apears to be a very poorly designed web application in that utilizes NHibernate. Most pages in the application are taking 10+ seconds to render and many are taking 5+ minutes. I have been developing software professionally for about 10 years and I had heard about NHibernate but never ran across it untill now. I am in the process of trying to figure out if I can tune this application acceptable performance levels or if I am going to have to rip NHibernate out of the application and write all of the queries from scratch. I have Log4Net hooked up and am viewing the created sql. The most simple pages in this application are producing almost 4000 lines of code in the log files generated by log4net when I am just looking at the sql output. I have been studing up on Nhibernate for the past several days and have tried to optimize all of the data fetching with almost no peformance gains. This applicaiton always using CreateCriteria to fetch data like this:

session.CreateCriteria( typeof( Company ) )
.AddOrder( new NHibernate.Expression.Order( "Name", true ) ).List<Company>();

A sniped from the mapping document looks like this:

<class name="Job" table="JOB" lazy="true" batch-size="50">
<id name="ID" column="JOB_ID" type="Int64">
<generator class="sequence">
<param name="sequence">JOB_SEQ</param>
</generator>
</id>
<many-to-one name="Study" column="STUDY_ID" class="Study" cascade="delete" not-found="ignore"/>
<many-to-one name="Company" column="COMPANY_ID" class="Company" cascade="delete" />
<many-to-one name="Contact" column="CONTACT_ID" class="Contact" />
<many-to-one name="PurchaseOrder" column="PO_ID" class="PurchaseOrder" cascade="delete" />
<property name="ScopeOfWork" column="SCOPE_OF_WORK" type="String" />
<property name="ContractNumber" column="CONTRACT_NUMBER" type="String" />
<property name="CreatedDate" column="CREATED_DATE" type="DateTime" />
<property name="StartDate" column="START_DATE" type="DateTime" />
<property name="ExcelFileDueDate" column="EXCEL_DUE_DATE" type="DateTime" />
<property name="DraftReportDueDate" column="DRAFT_DUE_DATE" type="DateTime" />
<property name="FinalReportDueDate" column="FINAL_DUE_DATE" type="DateTime" />
<property name="LastShipmentArrivalDate" column="LAST_SHIPMENT_ARRIVAL" type="DateTime" />
<property name="DeliverableItem" column="DELIVERABLE_ITEM" type="String" />
<property name="ResultsTAT" column="TAT_RESULTS" type="Byte" />
<property name="ReportTAT" column="TAT_REPORT" type="Byte" />
<property name="Amount" column="AMOUNT" type="Decimal" />
<property name="Status" column="WORKFLOW_STATUS" type="Enthalpy.BioPharma.WorkflowStatusType, Enthalpy.BioPharma" not-null="true" />
<property name="QAUserName" column="QA_USER_NAME" type="String" not-null="false" />
<property name="IsNew" column="IS_NEW" type="YesNo" not-null="true" />
<property name="StudyName" column="STUDY_NAME" type="String" />
<property name="StudyTitle" column="STUDY_TITLE" type="String" />
<property name="ScientistLastName" column="SCIENTIST_LASTNAME" type="String" />
<property name="ScientistFirstName" column="SCIENTIST_FIRSTNAME" type="String" />
<property name="SamplesArrived" column="SAMPLES_ARRIVED" type="Int64" not-null="false"/>
<property name="SamplesReceived" column="SAMPLES_RECEIVED" type="Int64" not-null="false"/>
<set name="DeliverablesTo" table="DELIVERABLES_TO" generic="true">
<key column="JOB_ID"/>
<element column="NAME" type="String"/>
</set>
<bag name="Samples" table="SAMPLEGROUP_WORKFLOW" generic="true" order-by="RUNID ASC" inverse="true">
<key column="JOB_ID"/>
<one-to-many class="SampleGroup" />
</bag>
</class>

<class name="Study" table="STUDY" schema="WATSON" mutable="false" lazy="true" batch-size="50">
<id name="ID" column="STUDYID" type="Int64">
<generator class="assigned" />
</id>
<property name="Name" column="STUDYNAME" type="String" />
<many-to-one name="Project" column="PROJECTID" class="Project" />
<property name="Title" column="STUDYTITLE" type="String" />
<property name="Description" column="STUDYDESCRIPTION" type="String" />
<property name="Director" column="STUDYDIRECTOR" type="String" />
<property name="IsGLP" column="GLP" type="YesNo"/>
<many-to-one name="Scientist" column="USERID" class="WatsonUser" />
<set name="Analytes" table="ASSAYANALYTES" schema="WATSON" generic="true">
<key column="STUDYID" />
<many-to-many class="Analyte" column="ANALYTEID" />
</set>
<set name="Shipments" table="SHIPMENTTRANSFER" schema="WATSON" generic="true">
<key column="STUDYID" />
<many-to-many class="Shipment" column="SHIPMENTID" />
</set>
<many-to-one name="Status" column="STUDYSTATUS" class="ConfigStudyStatus" />
<many-to-one name="Species" column="SPECIESID" class="ConfigSpecies" />
<many-to-one name="StudyType" column="STUDYTYPEID" class="ConfigStudyType" />
<many-to-one name="StudyUserStatus" column="STUDYUSERSTATUSID" class="ConfigStudyUserStatus" />
<property name="PKDirector" column="PKDIRECTOR" type="String" />
<property name="AnalyticalCoordinator" column="ANALYTICALCOORDINATOR" type="String" />
<property name="DepDirector" column="DEPUTYSTUDYDIRECTOR" type="String" />
<property name="Department" column="DEPARTMENT" type="String" />
<property name="StartDate" column="STARTDATE" type="DateTime" />
<property name="StudyCompleteDate" column="STUDYCOMPLETIONDATE" type="DateTime" />
<property name="PKCompleteDate" column="PKCOMPLETIONDATE" type="DateTime" />
<property name="ESigInd" column="ESIGIND" type="YesNo"/>
<property name="HandlingRequired" column="HANDLINGREQUIRED" type="YesNo"/>
<property name="Notebook" column="NOTEBOOK" type="String" />
<property name="PageNumber" column="PAGENUMBER" type="String" />
<property name="FilePrefix" column="FILEPREFIX" type="String" />
<property name="Amendment" column="AMENDMENT" type="String" />
<bag name="Samples" table="ANALYTICALRUN" generic="true" inverse="true" order-by="RUNID ASC">
<key>
<column name="STUDYID"/>
</key>
<one-to-many class="WatsonSampleGroup" not-found="exception" />
</bag>
</class>

<class name="Company" table="COMPANY" lazy="true" batch-size="50">
<id name="ID" column="COMPANY_ID" type="Int64">
<generator class="sequence">
<param name="sequence">COMPANY_SEQ</param>
</generator>
</id>
<property name="Name" column="NAME" type="String" not-null="true" />
<property name="Email" column="EMAIL" type="String" />
<property name="Address1" column="ADDRESS1" type="String" />
<property name="Address2" column="ADDRESS2" type="String" />
<property name="City" column="CITY" type="String" />
<many-to-one name="StateProvince" column="STATE_PROVINCE_ID" class="StateProvince" not-null="true" />
<property name="PostalCode" column="POSTAL_CODE" type="String" />
<property name="Active" column="ACTIVE" type="TrueFalse" not-null="true" />
<bag name="Contacts" table="CONTACT" generic="true" inverse="true" order-by="LAST_NAME ASC, FIRST_NAME ASC">
<key column="COMPANY_ID" />
<one-to-many class="Contact" />
</bag>
</class>

<class name="Contact" table="CONTACT" lazy="true" batch-size="50">
<id name="ID" column="CONTACT_ID" type="Int64">
<generator class="sequence">
<param name="sequence">CONTACT_SEQ</param>
</generator>
</id>
<property name="FirstName" column="FIRST_NAME" type="String" />
<property name="Salutation" column="SALUTATION" type="String" />
<property name="LastName" column="LAST_NAME" type="String" />
<property name="Phone1" column="PHONE1" type="String" />
<property name="Phone2" column="PHONE2" type="String" />
<property name="Email" column="EMAIL" type="String" />
<many-to-one name="Employer" column="COMPANY_ID" class="Company" />
</class>

<class name="PurchaseOrder" table="PO" lazy="true" batch-size="50">
<id name="ID" column="PO_ID" type="Int64">
<generator class="sequence">
<param name="sequence">PO_SEQ</param>
</generator>
</id>
<many-to-one name="Company" column="COMPANY_ID" class="Company" cascade="delete" not-null="true" />
<many-to-one name="Quote" column="QUOTE_ID" class="Quote" not-null="true"/>
<property name="StartAmount" column="START_AMOUNT" type="Decimal" not-null="true" />
<property name="CreateDate" column="CREATED_DATE" type="DateTime" not-null="true" />
<property name="PONumber" column="PO_NUMBER" type="String" not-null="true" />
<property name="Description" column="PO_DESCRIPTION" type="String" not-null="false" />
<bag name="Transactions" table="PO_TRANSACTION" generic="true" inverse="true" order-by="TRANSACTION_DATE asc">
<key column="PO_ID" />
<one-to-many class="PurchaseOrderTransaction" />
</bag>
</class>

It seems like the application is being forced to execute a gigantic n + n amount of select statements to pull almost any data out of the database. Am I on the right track? I would like to use NHibernate in this applicaiton if possible. How can I speed up the queries (by atleast 10 fold)?

Let me know if I need to give any more informations.

Thanks for your time

Hibernate version: 1.2.1.4000

Mapping documents:

Oracle 8i Lims Database

You can try fetch mode join which will reduce the amount of single sql statemente. For example:



The fetch mode is the crucial thing here. You'll probably have to check al criterias/queries in your application. You could specify the fetch mode in the mappings, but that's not the recommended way.


Have a look at this How-To to learn about the "ResultTransformer". It has nothing to do with the performance, but with join fetching you will get duplicate entries in the return company list.

http://www.nhforge.org/wikis/howtonh/get-unique-results-from-joined-queries.aspx

it seems to me that lazy loading is always only declared in the class entity. The documentation says that this is only an optional way to deactivate lazy loading for the whole class.



I don't know if it can also activate lazy loading for all sets, lists and bags within the class. If not, this would mean that lazy loading is not enabled for the mapping, which would explain the extremely bad performance.

You could enable lazy loading for the collection mappings within the nhibernate-mapping, but this requires extensive testing. An application that never had lazy loading enabled may not be prepared to cope with uninitialized collections.

an example to enable lazy loading:

Thanks for the suggestions. I have read about doing the joins with the Criteria method. I wanted to post on here before trying it out to see you guys had to say about my options. I will also try to make the many to one relationships lazy fetching. Thanks for your suggestions.

It seems that specifying the joins in the CreateCriteria method is helping for the very simple fetch statement that I originally posted. My next problem is that I have CreateCriteria statements that call a class/table that has multiple
many to one and one to many relationships. Then the children many to one tables have their own many to one relationships. This goes on for several levels of joined tables.

The code is calling these table by dynamiclly creating a query object like this.

ICriteria query = session.CreateCriteria( typeof( Job ) );

query = query.Add( NHibernate.Expression.Expression.Eq( "Company.ID", Convert.ToInt64( ddlFilterCompany.SelectedValue ) ) );

query.CreateCriteria( "Study", NHibernate.SqlCommand.JoinType.InnerJoin)
.Add( NHibernate.Expression.Expression.IsNotNull( "Name" ) );

I am assuming this is forcing the relationship between Job and Study to be queried using an inner join. I forced all of the other relationships on the Job table to be constrained this same way and everything is fine. How do I force the relationships of the Study table to be joins rather than multiple selects? I can't add a criteria for the Study table relationships because this criteria is for the Job class.

Hmmm ... I suppose you can't force these joins to be inner joins

<many-to-one name="Study" column="STUDY_ID" class="Study" cascade="delete" not-found="ignore"/>

This means, that a job does not necessarily has a study. Using an inner join would return nothing all.

And there is a configuration setting where you can specifiy the maximum depth for outer join fetching:

hibernate.max_fetch_depth

I don't know what the default is, but probably to low for your scenario.

But too many joins aren't helpful either. A combination of join and single-select is normally the best for such large "trees".

How do I specify the joins for the 2nd, 3rd, 4th, .... tables?

You have to add each one with crit.CreateCriteria or crit.CreateAlias:

query.CreateCriteria( "Study", "s", JoinType.InnerJoin)
.CreateCriteria( "s.WhatEver", JoinType.InnerJoin)
...

But joining more than three tables is probably a bad idea.

Thanks I didn't realize I could create table alias names. I am sure I will back for more questions after testing. Thanks for the help.

bluisana:

First I would try to figure out how long the query is taking itself, trace the application or use logs to try to figure this out. If the query is indeed taking a long time you can run this in Toad directly and often see the problem isn't NHibernate.

Often times I have seen people have an inefficient way of managing their sessions within ASP.Net. I'll admin it's tricky to do it yourself. Many people use Spring.Net or Castle to help manage the sessions within ASP.Net for them.

I was thrown on an application once with terrible performance and I found this was the case, in fact each request was generating a new session, regardless of whether it was an ASPX page or not.

Spring.Net manages it with an HTTP handler for IIS:

<httpHandlers>
<!-- Spring -->
<add verb="*" path="*.aspx" type="Spring.Web.Support.PageHandlerFactory, Spring.Web"/>
<add verb="*" path="*.asmx" type="Spring.Web.Services.WebServiceHandlerFactory, Spring.Web"/>
<add verb="*" path="ContextMonitor.ashx" type="Spring.Web.Support.ContextMonitor, Spring.Web"/>
</httpHandlers>

How about specifying joins with tables in another schema :)?

Kibbled_bits I have been looking at the queries using Log4Net and I am seeing over 100,000 queries to refresh some of these pages. I did originally look at the session problems and I have used some pretty complex singleton classes to make sure I am always instatiating only one session per page. I could have still missed something but I think the main problem here is the data fetching.

The initial capacity was set too low at: 8 for the SelectSqlBuilder that needed a capacity of: 13 for the table

I get this for almost ever sql statement that is executed. What does it mean?

Is it possible to explicitly join a table from another schema like this.

//Whatever is in schema whereever

query.CreateCriteria( "Study", "s", JoinType.InnerJoin)
.CreateCriteria( "s.whereever.WhatEver", JoinType.InnerJoin)

You can forget about that. That only indicates that the default size for the parameter array isn't large enough (or something like that). This happens during initialization of the queries during session factory built.



I can't follow. Do you mean another table that is not part of the mapping ? Hibernates criteria and HQL queries operate on classes (mappings) not on tables. So if you do not have an association between these tables defined in the mapping, you can't join them. You can only use (correlated) subqueries.

Can you post your complete critieria and the statements that are created (one sample per table) ?