How to remove redundant records when using left-join fetch?

lili_met · **Joined:** Tue May 23, 2006 10:53 am **Posts:** 24

Hi, all:

I want to get rid of the N+1 select problem, all I can found in documentation is to use "left join fetch". But in my case, it will return redundant parent records. How do I resolve this? Your answer is highly appreciated! Thanks!

Hibernate version:
3.1.3

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping>

<class name="ihub.hibernate.Person"
table="person"> <id name="id"
type="integer"
column="PERSON_ID">
<generator class="native"/>
</id>
<property name="name"
column="NAME"
unique="false"
/>
<set name="emails" table="email">
<key column="PERSON_ID"/>
<one-to-many class="ihub.hibernate.Email"/>
</set>
</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping>

<class name="ihub.hibernate.Email"
table="email"> <id name="id"
type="integer"
column="EMAIL_ID">
<generator class="native"/>
</id>
<property name="personID"
column="PERSON_ID"
unique="false"
/>
<property name="email"
column="EMAIL_ADDR"
unique="true"
/>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
List<Person> persons = session.createQuery(
"from Person p left join fetch p.emails where name like 'Lili%'")
.list();
for (Person p : persons) {
out.println("--- found person: " + p.getName());
for (Email e : p.getEmails()) {
out.println("----- personal email: " + e.getEmail());
}
}

Full stack trace of any exception that occurs:
--- found person: Lili
----- personal email: lili3@xxx.com
----- personal email: lili2@xxx.com
----- personal email: lili1@xxx.com
--- found person: Lili
----- personal email: lili3@xxx.com
----- personal email: lili2@xxx.com
----- personal email: lili1@xxx.com
--- found person: Lili
----- personal email: lili3@xxx.com
----- personal email: lili2@xxx.com
----- personal email: lili1@xxx.com
--- found person: Lili2
----- personal email: lili4@xxx.com
----- personal email: lili5@xxx.com
--- found person: Lili2
----- personal email: lili4@xxx.com
----- personal email: lili5@xxx.com

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

The generated SQL (show_sql=true):
select person0_.PERSON_ID as PERSON1_1_0_, emails1_.EMAIL_ID as EMAIL1_0_1_, person0_.NAME as NAME1_0_, emails1_.PERSON_ID as PERSON2_0_1_, emails1_.EMAIL_ADDR as EMAIL3_0_1_, emails1_.PERSON_ID as PERSON2_0__, emails1_.EMAIL_ID as EMAIL1_0__ from person person0_ left outer join email emails1_ on person0_.PERSON_ID=emails1_.PERSON_ID where name like 'Lili%'

Debug level Hibernate log excerpt:

mjstein6300 · **Joined:** Tue Jun 28, 2005 4:33 pm **Posts:** 21

pass the list to a set... set.addAll(list) That should do the trick.

lili_met · **Joined:** Tue May 23, 2006 10:53 am **Posts:** 24

mjstein6300 wrote:

pass the list to a set... set.addAll(list) That should do the trick.

It does the trick! Thanks a lot!

lili_met · **Joined:** Tue May 23, 2006 10:53 am **Posts:** 24

Although the Set.addAll(list) helps to get clean result set, it doesnot change the underlying query behavior of Hibernate. Since Hibernate is doing left-join fetch like SQL, a lot of extra objects will be generated from the query unnecessarily. For large querys (for example, setMaxResult(100)), the memory usage and the response is really bad. So is there any better way to solve my problem? Thanks!

pepelnm · **Posted:** Wed May 24, 2006 5:42 pm

Code:

String hql = "select distinct p from Person p left join fetch p.emails where name like 'Lili%'";
List<Person> persons = session.createQuery(hql).list(); 

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

For HQL, peplnm's suggestion (select distinct...) is correct. The equivlaent for Criteria (which has the same problem) is to use the DISTINCT_ROOT_ALIAS result transformer.

jbchurn · **Posted:** Fri May 26, 2006 4:19 am

I have 3 classes with relationship as below.

Staff (one-to-many) StaffSkill (many-to-one) Skill.

My HQL:

Code:

select distinct s from Staff s inner join s.staffSkills sk inner join fetch sk.Skill;

I do try to use "distinct" but it still give me duplicated object resultset for "Staff". Any possible reason of why it does not work?

Please advise.
Thank you.

ekupcik · **Joined:** Sun Oct 09, 2005 6:36 am **Posts:** 19

While adding the results to a Set solves the duplicate entries it also changes the order of your elements. This is not the "offcial" way to solve this problem. So maybe you want have a look at

Criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

instead.

jbchurn · **Posted:** Fri May 26, 2006 4:59 am

But what I need is how to get it work for HQL, not criteria query.

Thank you.

jbchurn · **Posted:** Fri May 26, 2006 5:10 am

For your information, I do try using criteria and it can work.

Code:

        Criteria c = _session.createCriteria(Staff.class)
            .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
            .createCriteria("staffSkills")
            .createCriteria("skill");

But I want to know how to get it work in HQL.

Really appreciate your advice.

ekupcik · **Joined:** Sun Oct 09, 2005 6:36 am **Posts:** 19

jbchurn wrote:

But what I need is how to get it work for HQL, not criteria query.

Thank you.

Ahh, sorry. I didn't pay attention to this. Well, in this case you might really have to use a set. In your example you don't seem to care about the order so adding the objects directly to a set shouldbe ok. If you care about the order i would use something like that.

Code:

List loResultList = new ArrayList();
Set loResultObjects = new HashSet();
ScrollableResults loResult = loQuery.scroll();
while(loResult.next())
{
    Object loObject = loResult.next();
    if(loResultObjects.contains(loObject) == false)
    {
        loResultList.add(loObject);
    }
}

Or you could ask somebody who knows more about SQL than i do ;-)

HQL is just a more convenient way to write plain SQL i don't think that there is a way to solve your problem in plain SQL.

But have you tried using Criteria and setting lazy="false"

<set name="emails" table="email" lazy="false">

This way Hibernate should create the Join for you even if you use the Crieria API

jbchurn · **Posted:** Fri May 26, 2006 6:01 am

Hi ekupcik,

I found somewhere else that uses a little bit shorter code than yours, can I use this to save some codes? or what's the advantage of your code (which little bit more) over the following? reason asking because i can get non-duplicated resultset using it too.

Code:

  List uniqueResults = new ArrayList();
  for (int i=0; i<lstStaff.size(); i++) {
    Object o = lstStaff.get(i);    
    if (!uniqueResults.contains(o)) {
      uniqueResults.add(o);
    }
  }   

Hi tenwit,

If you seeing this posting, would like to seek confirmation from you whether distinct really can get rid of duplicate resultset in HQL or not.

Thanks all.

ekupcik · **Joined:** Sun Oct 09, 2005 6:36 am **Posts:** 19

jbchurn wrote:

Code:

  List uniqueResults = new ArrayList();
  for (int i=0; i<lstStaff.size(); i++) {
    Object o = lstStaff.get(i);    
    if (!uniqueResults.contains(o)) {
      uniqueResults.add(o);
    }
  }   

Using a List can kill your performance if you have a large result as you will usually have iterate though the whole list for each entry in the result set. So your code gets slower and slower the more elemetns you have already read. A HashSet needs more or less a constant time to check whether it contains an object (unless you are using a really poor implementation of hashCode()). This doesn't matter if you have just few elements, an ArrayList might be even faster when you have just a few elements (but you won't notice it anyway). But for large results sets with thousands and more elements your code won't work well.

jbchurn · **Posted:** Fri May 26, 2006 6:26 am

Ok.

By the way, what is the "loQuery" in your code?

Where should I substitute the List obtained from my HQL in your code?

lili_met · **Joined:** Tue May 23, 2006 10:53 am **Posts:** 24

Somehow using left-join fetch with Result transformer, the returned result is not correct sometimes(missing some records I don't know why), also the setMaxResultSize() will not work correctly, because it only defines the records to be retrieved before transformation. So here's what I do now:

- Don't map child collection in the parent xml, but preserve the separate mapping between object and db table for the child object.

- query parent elements only, and get the foreign key set.

- Use the foreign key set to query child collection (using IN restriction)

- Redistribute the child collection to parents based on foreign keys in a for loop (Both parent and children need to be sorted with the foreign key during the query.)

In this way, I only do 2 queries instead of (N+1) and do some post-processing in Java, and get what I want. Not so smart, anyone has better ideas?