Performance issues

mustapha · **Joined:** Wed May 31, 2006 9:24 am **Posts:** 22

Hi there, I have tried searching the forum for a similar but can't find anything specific - apologies if this has been answered before. I have also looked in the help but not sure exactly where to go...

I have developed a site using NHibernate 1.2.0.4 - up til now it has been great to use. However, I am now working on reporting and am experiencing some severe performance problems on the live site.

The object hierarchy is Account (user info), which is many-to-one with Profile, which is many-to-one with ProfileView.

I have included my mapping documents below (slightly truncated - have taken out irrelevant fields). One report I have had problems with is a page where I load all Accounts, bind them to a repeater. The repeater has another repeater as a child control, which takes the bound Account and binds all published Profiles to the child repeater. This seems very simple but takes a long time to run on the production server - this is a shared server so is under more load but I would not expect this kind of delay - it is maybe loading 20 Accounts with an average of 5 profiles each. I do not understand how to make a more efficient query in NHibernate - temporarily I have simply switched to ADO.Net and populated a datatable with the query:

Code:

SELECT a.AccountId, a.CompanyName, p.ProfileId, p.[Filename], p.PrimaryKeywords FROM Profile p INNER JOIN Account a ON p.AccountId=a.AccountId WHERE p.Published = 1 ORDER BY a.CompanyName, p.PrimaryKeywords

which gives me all the data I need in one query with no refetching, unlike the above method.

Likewise, I have another report where I filter the results by the ViewTime property of the ProfileView. So, first I have to query the Accounts

Code:

SELECT DISTINCT a.* FROM Account a LEFT OUTER JOIN Profile p ON p.AccountId = a.AccountId LEFT OUTER JOIN ProfileView pv ON pv.ProfileId = p.ProfileId WHERE pv.ViewTime > '2007-01-01'

I am using an IQuery to do this, with named paramaters where appropriate.

Again, I bind these Accounts to a repeater, and for each Account I load the appropriate profiles:

Code:

SELECT DISTINCT p.* FROM Profile p LEFT OUTER JOIN ProfileView pv ON pv.ProfileId = p.ProfileId WHERE p.AccountId = :accId AND pv.IsPreview = :includePreviews AND pv.ViewTime > :start

Finally for each Profile I must calculate summary stats for the profile views, which involves 2 more queries:

Code:

from ProfileView pv WHERE pv.Profile = :profile AND pv.IsPreview = :includePreviews

Code:

from ProfileView pv WHERE pv.Profile = :profile AND pv.IsPreview = :includePreviews AND pv.ClickedThrough = true

Obviously this all starts to add up to a lot of querying quite quickly. The speed drop on an under-load server is very noticeable - I have run and implemented the recommendations in SQL Server Tuning Advisor.

Can anyone give me any tips on reporting strategies under NHibernate? I know I can drastically reduce the number of queries using TSQL / ADO.Net, but I'd prefer to learn NHibernate better...

AHA, Ben :)

Hibernate version:
1.2.0.4

Mapping documents:
Account:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="proj.Domain.Classes" assembly="proj.Domain" default-access="field.camelcase">
   <class name="Account" table="`Account`">
      <id name="Id" column="AccountId" type="Int32" unsaved-value="0">
         <generator class="identity" />
      </id>   
      <property name="CompanyName" column="CompanyName" type="String" not-null="true" />
      <bag name="Profiles"  inverse="true" lazy="true" order-by="ProfileId" cascade="all-delete-orphan">
         <key column="AccountId" />
         <one-to-many class="Profile" />
      </bag>
      <many-to-one name="Country" class="Country" column="CountryId" not-null="false"/>
      <one-to-one name="User" class="User" property-ref="Account"/>
      
   </class>
</hibernate-mapping>

Profile:

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="proj.Domain.Classes" assembly="proj.Domain" default-access="field.camelcase">
   <class name="Profile" table="`Profile`">
      <id name="Id" column="ProfileId" type="Int32" unsaved-value="0">
         <generator class="identity" />
      </id>
      
      <property name="Name" column="Name" type="String" not-null="true" />
      <property name="Published" column="Published" type="Boolean" not-null="true" />
      <list name="Categories" table="ProfileCategories" lazy="true" cascade="none">
         <key column="ProfileId" />
         <index column="Rank" type="Int32" />
         <many-to-many class="Category" column="CategoryId"/>
      </list>
      <bag name="ProfileViews"  inverse="true" lazy="true" order-by="ProfileViewId" cascade="all-delete-orphan">
         <key column="ProfileId" />
         <one-to-many class="ProfileView" />
      </bag>
      <many-to-one name="Account" column="AccountId" class="Account" not-null="true"/>
      
   </class>
</hibernate-mapping>

ProfileView:

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="proj.Domain.Classes" assembly="proj.Domain" default-access="field.camelcase">
   <class name="ProfileView" table="`ProfileView`">
      <id name="Id" column="ProfileViewId" type="Int32" unsaved-value="0">
         <generator class="identity" />
      </id>
      
      <property name="IPAddress" column="IPAddress" type="String" not-null="true" />
      <property name="Referer" column="Referer" type="String" not-null="true" />
      <property name="ViewTime" column="ViewTime" type="DateTime" not-null="true" />
      <property name="IsPreview" column="IsPreview" type="Boolean" not-null="true" />
      <property name="ClickedThrough" column="ClickedThrough" type="Boolean" not-null="true" />
      <property name="ClickThroughDate" column="ClickThroughDate" type="DateTime" not-null="false" />
      <many-to-one name="Profile" column="ProfileId" class="Profile" not-null="true"/>
      
   </class>
</hibernate-mapping>

Name and version of the database you are using:
SQL Server 2005

Sreenivas Shreenath · **Posted:** Sat Oct 20, 2007 3:54 am

Did you check whether join columns and columns under where conditions are indexed? This might help solve your problem to a certain extent, but not sure of too many recursive queries though.

Do not forget to post a reply if this answers your question.

==============================

Shreenath Sreenivas
Senior Software Engineer (TL)
Ocimum Biosolutions

mustapha · **Joined:** Wed May 31, 2006 9:24 am **Posts:** 22

Hi Sreenivas, yes I ran SQL Profiler on the site and implemented all it's reccomendations, which indexed all the joins and where's.

If you look at the fetch paradigm here, it seems obvious to me that NHibernate will be much slower and involve many more queries - I have to load each account, and then query for all the Profile children of that Account.

Using a stored procedure, I can get all the data I need in 1 query and simply loop through it code-side. Is this simply a weakness of reporting with NHibernate, or am I missing something?

Cheers, Ben

mustapha · **Joined:** Wed May 31, 2006 9:24 am **Posts:** 22

Hmmm, nobody else has any comments?! Basically, it seems as though NHibernate is not very suited to reporting to me - has anyone else come to this conculsion? Do other people use other solutions when it comes to reporting, or am I just missing something about NHibernate?

AHA, Ben

martijnb · **Joined:** Sat May 14, 2005 8:40 am **Posts:** 130

mustapha wrote:

If you look at the fetch paradigm here, it seems obvious to me that NHibernate will be much slower and involve many more queries - I have to load each account, and then query for all the Profile children of that Account.

With NHibernate, you can load 1 one-many collection together with the parent:

Code:

from Account a join fetch a.Profiles p

I believe that future versions allow for more joins.

And of course, every many-one relation can be joined and fetched in one go.

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

There's also MultiQuery in the trunk.

Symon.

mustapha · **Joined:** Wed May 31, 2006 9:24 am **Posts:** 22

@martijnb, the problem with what you suggest is that the criteria for my query are on the ProfileView, which has a one-many relationship with the Profile. I take it from your comment about future versions allowing more joins means that the current version can't.

So, using that I could fetch all Accounts, with their related Profiles, and then loop through them code-side looking for ones that fit my criteria, and then output them. I'm not that keen on this as a solution though, since I'm fetching and looping through redundant data. Using ADO and a stored procedure I can fetch exactly the data I need with no further processing required code-side.

Cheers, Ben