Hibernate Community • View topic - Performance Problem with multiple joins

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Performance Problem with multiple joins

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

drees

Post subject: Performance Problem with multiple joins

Posted: Mon Sep 08, 2003 8:15 pm

Newbie

Joined: Mon Sep 08, 2003 4:04 pm
Posts: 8
Location: San Diego, CA

I've got two objects, object A, object B and C.

Object A has two different sets of B. Object B has many-to-one relationship to C.

I am writing a HQL query like this:

Code:

select a from A a left join a.setB1 as setB1 left join a.setB2 as setB2
where setB1.c.attribute=:someValue and setB2.c.attribute=:someValue

The problem is that the SQL being generated looks like this:

Code:

select a.id as id
from tableA a
left outer join setTableB1 tb1 on a.id=tb1.a_id
left outer join tableB tb on tb1.b_id=tb.id
left outer join setTableB2 tb2 on a.id=tb2.a_id
left outer join tableB tb on tb2.b_id=tb.id,
tableC c1,
tableC c2
where
c1.attribute = "SomeValue" and tb1.c_id=c1.id
c2.attribute = "SomeValue" and tb2.c_id=c2.id

The query returns duplicate results. If I add a distinct clause to the query, it works OK, but performance is horrible (MySQL 4.0.14 sucks CPU), and I only have a few hundred rows in each table.

If I only do one join on either setTableB1 or setTableB2, the query returns quickly without any duplicate results.

Any ideas?

Top

drees

Post subject:

Posted: Mon Sep 08, 2003 9:26 pm

Newbie

Joined: Mon Sep 08, 2003 4:04 pm
Posts: 8
Location: San Diego, CA

I fixed the problem by changing the HQL query to the following:

Code:

select a from A a
left join a.setB1 as setB1
left join setB1.c as b1c
left join a.setB2 as setB2
left join setB2c as b2c
where b1c.attribute=:someValue and b2c.attribute=:someValue

which now generates the following SQL

Code:

select a.id as id
from tableA a
left outer join setTableB1 stb1 on a.id=stb1.a_id
left outer join tableB tb1 on tb1.b_id=tb1.id
left outer join tableC tc1 on tb1.id=tc1.id
left outer join setTableB2 stb2 on a.id=stb2.a_id
left outer join tableB tb2 on tb2.b_id=tb2.id,
left outer join tableC tc2 on tb2.id=tc2.id
where
tc1.attribute = "SomeValue"
tc2.attribute = "SomeValue"

It seems that the having the extra contactInfo tables in the query slowed things down a lot without the explicit joins as MySQL didn't know what to do.

BTW, I have use_outer_join enable in my config file.

Top

gavin

Post subject:

Posted: Mon Sep 08, 2003 10:15 pm

Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia

Correct solution. But why on earch are you using "left join" for a table you refer to in the where clause? Change to "inner join".

Top

drees

Post subject:

Posted: Mon Sep 08, 2003 11:49 pm

Newbie

Joined: Mon Sep 08, 2003 4:04 pm
Posts: 8
Location: San Diego, CA

Thanks for the tip. Not sure why I was using left join instead of inner join. I blame the fact that most of my SQL experience being based on Sybase and a lack of coffee. ;)

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

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