2X more joins that are needed

dhatomic · **Joined:** Thu Nov 03, 2005 1:31 pm **Posts:** 2

A many-to-many uses a join table in the middle that just contains pairs of IDs. When I reach across the many-to-many just to get IDs I expect hibernate to get them from the join-table but, wastefully, it doesn't. Example SQL below.

Hibernate version:

hibernate-3.0.3.tar.gz

Name and version of the database you are using:

mysql Ver 12.22 Distrib 4.0.23, for pc-linux-gnu (i386)

The generated SQL (show_sql=true):

something like this

Code:

select count(*) from Left l inner join JoinTable j on l.id=j.left_id inner join Right r on j.right_id=r.id where r.id=11;

when SQL like this (one join less) would have done just as well:

Code:

select count(*) from Left l inner join JoinTable j on l.id=j.left_id where j.right_id=11;

(you see there is no need to go further than the JoinTable as that knows all there is to know about the IDs on its right)

Is this a known issue?

ernst_pluess · **Posted:** Fri Nov 04, 2005 2:53 am

Depending on your configuration hibernate may do some eager (pre) fetching. Check

your mapping files. Did you set Right class to lazy=false
your cfg.xml file. Set hibernate.max_fetch_depth=0 if you don't want to have any eager fetching.

HTH
Ernst

dhatomic · **Joined:** Thu Nov 03, 2005 1:31 pm **Posts:** 2

setting lazy=false seems unlikely to give me more laziness

also the current inefficent query is not a kind of eagerness because Hibernate gains no additional information through doing the extra table join

it's only a waste of CPU so I'm suprised Hibernate does it under any circumstances

if I have time I'll try and boil down a self-contained test case

towe · **Posted:** Fri Nov 04, 2005 8:52 am

whats your hql?

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

dhatomic: he asked if you *had* set lazy=false; he didn't ask you *to* set lazy=false

I have many-to-many mappings in my current app, and I want entire objects, not just the id values, so hibernate is doing the right thing for me. I can't see the value in retrieving only id values, unless you're going to use them to load the objects themselves....