I've tried a lot of things to tune this query and I'm sure someone can point me to an answer. What I would like to do is get the same number of selects (for performance) as I do in standard SQL.

Here's the standard SQL query:


When I do a find query in HQL using this:


It does the above SQL query and then does many selects on table child for each ID.

I've got a parent class with the the following:


I've got a child class with the following:


Thanks for your help.

Can I provide any additional information? I'd really like to get this solved.

Try 'left join fetch'. Other than that, make sure that you're not calling setCacheable(true) if the underlying class is not configured for caching.

To be precise, the equivelant would be 'inner join fetch':

In this case, a left join would be useful only if you had children with no parents (an unlikely situation if I read your post correctly).

That's a great idea. In my tests, the results turn out the same with left join fetch or inner join fetch. Would upgrading to 3.0 help this situation?

The results should be the same in this case. left join in HQL has the same meaning as in SQL -- include all data in the class (table) on the left, regardless whether there is a matching object on the right. Inner join means return rows only if there is a match on both sides. In your case,

should have equivelent results to inner join because there should never be a child with no parent. If the query was:

Your query would return the Parent object and its children, if any. However,

would retrieve the parent object (and its children) only if it has children.

Back to your query, though. Since you want the child and it's parent, and you know the parent is there, the difference is in the generated SQL. An HQL join becomes the same type in the generated SQL. So,

becomes something like

while

becomes something like

Since most databases perform an INNER JOIN faster than a LEFT JOIN, you can expect the INNER JOIN to perform slightly better.

Hibernate guys, please check my assumptions and let me know if I'm telling fydoken something wrong.

All looks correct to me.

I really appreciate your response but I don't believe I explained myself well. I meant to say that the results were the same regardless of the query I used. It always does one select like I would expect joining the parent and child and then does multiple selects on the child using any of the following queries.



or



or




I wondered how best to troubleshoot this further or if upgrading from 2.1 would help this behavior.

Thanks for your assistance.

Query

should do the trick if in the mapping table fetch is set to "join".
like this



At least it is what h3-cvs does. Althought it does two selects - one to get the child and second to fetch all the childs of the child parent.

Postgres log looks like this:

[code]
LOG: statement: begin;
LOG: statement: select c021x0_.id as id1_, c021x0_.name as name1_, c021x0_.u_id as u3_1_ from c021 c021x0_ where c021x0_.id=1
LOG: statement: select p021x0_.id as id0_1_, p021x0_.name as name0_1_, childholde1_.u_id as u3_3_, childholde1_.id as id3_, childholde1_.id as id1_0_, childholde1_.name as name1_0_, childholde1_.u_id as u3_1_0_ from p021 p021x0_ left outer join c021 childholde1_ on p021x0_.id=childholde1_.u_id where p021x0_.id=1
LOG: statement: commit;begin;
[/code/
Seems better than many selects for every child anyway.