Improving fetching performance

silveraxe · **Joined:** Mon Aug 29, 2005 5:21 am **Posts:** 9

Hi,
I'm using NHibernate in an ASP.NET application. Everything works ok but not the performance :-) Now there is the task to improve the performance especially regarding the SQL queries. However, there is one part of the application where I'm unsure how to improve fetching the objects including children.

The main object has only 2 attributes but 10 associated objects (via bags). On one specific page I need 8 of the associated objects. Currently I use lazy loading but that produces a lot of sql queries:

- I search for objects with some properties (1 query)
- Then I retrieve all those objects (30 objects found - 30 queries)
- Every object is displayed in a Web User Control including the data of the 8 associated objects I use here (30 objects * 8 associated obj -> 240 queries)
- Some associated objects have other objects in bags they need data from

In total I have about 500 queries for a single page...

I tried to set eager fetching for the objects but that created the cartesian product with >200.000 lines as response and NHibernate threw an error, that the sql query couldn't be executed.

What strategy makes sense at this point? Is there a way to tell NHibernate to first load the 30 main objects and then all instances of the first associated object that belong to the 30 main objects, then all instances of the second associated object, ...?

Thanks,
Hilmar

cremor · **Joined:** Tue Jul 29, 2008 3:30 am **Posts:** 74

How are you loading you main objects (first query)?
Because if you use something like the following, NHibernate will only execute one query to get all 30 objects.

Code:

session.CreateQuery(query).List<Type>();

And how are you mapping your bags?
I've never used bags myself, but for my sets and dictionaries NHibernate always executes only one query to get all objects in the list.

silveraxe · **Joined:** Mon Aug 29, 2005 5:21 am **Posts:** 9

cremor wrote:

How are you loading you main objects (first query)?
Because if you use something like the following, NHibernate will only execute one query to get all 30 objects.

Code:

session.CreateQuery(query).List<Type>();

Let's say I have object A with the following hibernate mapping:
A
- many-to-one B
- bag one-to-many C (inverse=true)
- bag one-to-many D (inverse=true)
- one-to-one E
- bag many-to-many F
- bag many-to-many G

Then I create the following HQL query

Code:

string queryString =
                    "select a " +
                    "from A as a " +
                    "join a.C as c " +
                    "join a.F as f " +
                    "where :value1 between c.Col1 and c.Col2 " +
                    "and c.Type = :value2 " +
                    "and c.Property >= :value3 " +
                    "and f.Property1 like :value4 " +
                    "and f.Property2 like :value5";
IQuery query = factory.Session.CreateQuery(queryString);
query.SetParameter("value1", value1);
query.SetParameter("value2", value2);
query.SetParameter("value3", value3);
query.SetParameter("value4", value4);
query.SetParameter("value5", value5);
IList<A> list = query.List<A>();

Hibernate sends the following SQL statements:

Code:

// first fetching all AIDs
select 
   a0_.[AID] as A1_29_, 
   a0_.[Property1] as Property12_29_, 
   a0_.[Property2] as Property23_29_ 
from 
   [dbo].[A] a0_ 
   inner join [dbo].[C] c1_ 
      on a0_.[AID]=c1_.[AID] 
   inner join F f2_ 
      on a0_.[AID]=f2_.[AID] 
   inner join [dbo].[F] f3_ 
      on f2_.[FID]=f3_.[FID] 
where 
   (@p0 between c1_.[Col1] and c1_.[Col2] )
   and(c1_.[Type]=@p1 )
   and(c1_.[Property]>=@p2 )
   and(f3_.[Property1] like @p3 )
   and(f3_.[Property2] like @p4 )

// then fetching all "A"s again with the one-to-one relationship to E:
SELECT 
   e0_.[AID] as AID1_31_2_, 
   e0_.[Prop] as Prop2_31_2_, 
   a1_.[AID] as Restaura1_29_0_, 
   a1_.[Property1] as Property12_29_0_, 
   a1_.[Property2] as Property23_29_0_, 
   e2_.[AID] as AID1_31_1_, 
   e2_.[Prop] as Prop2_31_1_
FROM 
   [dbo].[E] e0_ 
   left outer join [dbo].[A] a1_ 
      on e0_.[AID]=a1_.[AID] 
   left outer join [dbo].[E] e2_ 
      on a1_.[AID]=e2_.[AID] 
WHERE 
   e0_.[AID]=@p0

cremor wrote:

And how are you mapping your bags?
I've never used bags myself, but for my sets and dictionaries NHibernate always executes only one query to get all objects in the list.

That's an example of a bag in my hibernate config:

Code:

<bag name="NameXYZ"
 table="TableXYZ"
 cascade="all-delete-orphan"
 inverse="false" >
  <key>
   <column name= "`AID`" />
  </key>
  <many-to-many class="F" 
   fetch="join" >
   <column name= "`FID`" />
  </many-to-many>
</bag>

In the C# code I use an IList<F> with the Name NameXYZ for the property. When I access the property all elements in that list are loaded as you said.

However the problem is that every object A has the property NameXYZ. That means each time I access NameXYZ of one object A the elements are loaded. In my case 30 times on one page.

Instead of doing "SELECT ... FROM TableXYZ WHERE AID=@p0" it should do a "SELECT ... FROM TableXYZ WHERE AID=@p0 OR AID=@p1..." or "SELECT ... FROM TableXYZ WHERE AID IN (...)". Do you know what I mean?

Thanks,
Hilmar

eiben · **Joined:** Tue Dec 04, 2007 4:48 pm **Posts:** 21

There would be two things that pop up in my mind: use a different query or use the batching feature of NHibernate.

I haven't used the latter myselft - all I know is that you can send multiple SQL-statement at once to the server to reduce the number of roundtrips from app<->db.

The other thing would be to change the query. What crosses my mind would be something like this:

Code:

IList<E> = session.CreateCriteria(typeof(E))
  .CreateCriteria(A)
  .Add(Expression.Eq("A.AID", "[someVal]")
  .SetFectMode("B", FetchMode.Eager)
  .SetFectMode("C", FetchMode.Eager)
  .SetFectMode("D", FetchMode.Eager)
  .SetFectMode("F", FetchMode.Eager)
  .SetFectMode("G", FetchMode.Eager)
  .List<E>();

I haven't tested this at all ... I just wrote the out of the top of my head, but this should give you all "E" with the specified AID and fully initilized dependent objects. This should result in one huge queries with a ton of columns, but for your use case suitable.

silveraxe · **Joined:** Mon Aug 29, 2005 5:21 am **Posts:** 9

eiben wrote:

There would be two things that pop up in my mind: use a different query or use the batching feature of NHibernate.

I haven't used the latter myselft - all I know is that you can send multiple SQL-statement at once to the server to reduce the number of roundtrips from app<->db.

I think I'll give that a try. As far as I understood this function looks which objects in the session can be loaded if one of that objects is requested and loads more than one. That could help.

eiben wrote:

The other thing would be to change the query. What crosses my mind would be something like this:

Code:

IList<E> = session.CreateCriteria(typeof(E))
  .CreateCriteria(A)
  .Add(Expression.Eq("A.AID", "[someVal]")
  .SetFectMode("B", FetchMode.Eager)
  .SetFectMode("C", FetchMode.Eager)
  .SetFectMode("D", FetchMode.Eager)
  .SetFectMode("F", FetchMode.Eager)
  .SetFectMode("G", FetchMode.Eager)
  .List<E>();

I haven't tested this at all ... I just wrote the out of the top of my head, but this should give you all "E" with the specified AID and fully initilized dependent objects. This should result in one huge queries with a ton of columns, but for your use case suitable.

I tried that but that resulted in a cartesian product with >200.000 result rows in my test data. In the production it will cause >1.000.000 rows. with about 95% redundant data.

As far as I've seen NHibernate 2 solves some of these problems but I have to use 1.2 at the current time.

So, I'll try two things now:
- Use batch query to load more objects in one query
- Preload objects in the cache

Thanks,
Hilmar

eiben · **Joined:** Tue Dec 04, 2007 4:48 pm **Posts:** 21

Hmm, interesting about the cartesian product ...

maybe you have to specify the related elements explicitly in the query:

Code:

IList<E> = session.CreateCriteria(typeof(E))
  .CreateCriteria("A")
  .Add(Expression.Eq("A.AID", "[someVal]")
  .CreateCriteria("B")
  .SetFectMode("B", FetchMode.Eager)
  .CreateCriteria("C")
  .SetFectMode("C", FetchMode.Eager)
  .CreateCriteria("D")
  .SetFectMode("D", FetchMode.Eager)
  .CreateCriteria("F")
  .SetFectMode("F", FetchMode.Eager)
  .CreateCriteria("G")
  .SetFectMode("G", FetchMode.Eager)
  .List<E>(); 

The CreateCriteria should actually create a join ... but your right, that if you only have a bunch of "D" which is used in a lot of "E" that this might not be optimal :)

Well, you could also load all elements of B,C,D,F and G (that would result in 5 select statements). When you access a certain B that is associated with an E this object should be "hydrated" from the session without hitting the database. In conjunction with

Code:

IList<E> = session.CreateCriteria(typeof(E))
  .CreateCriteria("A")
  .Add(Expression.Eq("A.AID", "[someVal]")
  .List<E>();

this should create a total amount of 6 queries.