-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Performance problem with eager joins in native SQL select
PostPosted: Mon Dec 31, 2007 12:45 am 
Beginner
Beginner

Joined: Sun Apr 09, 2006 9:07 pm
Posts: 24
NHibernate version: 1.2.0 GA

I have two mapped classes, let's call them Parent and Child, where Parent has a simple many-to-one mapping to Child. I want to load those using a native SQL query, so I use the following code:

ISQLQuery query = Session.CreateSQLQuery(@"
SELECT p.*, c.*
FROM Parent p
INNER JOIN Child c ON p.childId = c.[id]
(more joins and criteria)
";

query.SetFlushMode(FlushMode.Never);
query.AddEntity("p", typeof(Parent));
query.AddJoin("c", "p.Child");

This works, but using SQL Profiler I can see that it's doing a separate query to load each Child instance, even though they were all already loaded in the original query. After some debugging, it turns out that when NHibernate goes to look for p.Child in the session it doesn't find it, so it loads it again! Simply swapping around the selects fixes the problem:

ISQLQuery query = Session.CreateSQLQuery(@"
SELECT c.*, p.*
FROM Parent p
INNER JOIN Child c ON p.childId = c.[id]
(more joins and criteria)
";

Could this be fixed so that NHibernate is smart enough to realise it has the child it needs in the query results, regardless of the order of the select list?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.