-->
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: fetch="join" and N+1 queries with association crit
PostPosted: Mon Feb 25, 2008 8:34 am 
Newbie

Joined: Mon Feb 25, 2008 7:04 am
Posts: 1
Location: India
Hibernate ignores the fetch="join" for associations when there is a restiction on the associated criteria !!!,

In the example code below, when I have created Employee criteria and specified restrictions on Language associations which results in N+1 query pattern even if fetch="join" is specified.

It fires only one query to fetch employees and thier associated languages if I only specify the restriction on Employee criteria e.g. surname.

Hibernate version:
3.2.3
languges Mapping
<set name="languages" table="EMPLOYEE_SPEAKS_LANGUAGE" lazy="false" fetch="join">
<key column="emp_id"/>
<many-to-many column="lan_id" class="Language"/>
</set>

Code between sessionFactory.openSession() and session.close():
criteria.add(Restrictions.like("surname", "FOR",MatchMode.START));
Criteria lanCriteria = criteria.createCriteria("languages");
lanCriteria.add(Restrictions.eq("code",language));
criteria.setFetchMode("languages", FetchMode.JOIN);
criteria.list();

database used
Oracle 10g

The generated SQL

1 query to fetch all matching employees and their associated languages
select this_.emp_id as emp1_1_1_,
this_.emp_surname as emp2_1_1_,
this_.emp_firstname as emp3_1_1_,
this_.cn_id as cn4_1_1_,
languages3_.emp_id as emp1_,
language1_.lan_id as lan2_,
language1_.lan_id as lan1_3_0_,
language1_.lan_code as lan2_3_0_,
language1_.lan_name as lan3_3_0_
from EMPLOYEE this_ inner join EMPLOYEE_SPEAKS_LANGUAGE languages3_ on this_.emp_id = languages3_.emp_id inner join SPOKEN_LANGUAGE language1_ on languages3_.lan_id = language1_.lan_id
where this_.emp_surname like ? and language1_.lan_code = ?

N queries for languages (unnecessary)
select languages0_.emp_id as emp1_1_,
languages0_.lan_id as lan2_1_,
language1_.lan_id as lan1_3_0_,
language1_.lan_code as lan2_3_0_,
language1_.lan_name as lan3_3_0_
from EMPLOYEE_SPEAKS_LANGUAGE languages0_ left outer join SPOKEN_LANGUAGE language1_ on languages0_.lan_id = language1_.lan_id
where languages0_.emp_id = ?

This works as expected (single join fetch) if I only apply the restrictions on Employee attributes


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.