Which Hibernate Join operation is more efficient?

Question

Which of the following HQL queries is faster to compute, i.e., more efficient?

from Cat as cat
left join cat.kittens as kitten
with kitten.bodyWeight > 10.0

or

from Cat as cat
left join cat.kittens as kitten
where 
  kitten.bodyWeight > 10.0

It produces different results. The first query will return all cats. — Stefan Steinegger, Jul 09 '13 at 14:13
The first will return all cats, because `with` in an outer join doesn't reduce the result set. The second query returns all cats with at least one kitten that is heavier than 10. — Stefan Steinegger, Jul 10 '13 at 05:55
I don't think that performance would be any difference. Take a look at the execution plan. As already explained in my answer: it depends on the underlaying DBMS and has nothing to do with hibernate. — Stefan Steinegger, Jul 10 '13 at 11:25

score 0 · Answer 1 · answered Jul 09 '13 at 14:16

0

Performance of HQL queries depend on the underlying database. HQL is translated to SQL by hibernate. SQL is translated to an execution plan and optimized by the DBMS. Optimization and performance depends also on the data in your database.

Take a look at the generated SQL and analyze it. You may take a look at the execution plan generated by the DBMS. This is all independent from hibernate.

Your queries can't be compared, they do not produce the same results. Generally, left outer joins are slower than inner joins.

answered Jul 09 '13 at 14:16

Stefan Steinegger

63,782
15
129
193

What is the difference in the results? How can I see the sql which is generated? – Michael Jul 09 '13 at 16:40
@confile Use `logSql = true` in `DataSource.groovy` to see the generated SQL. – dmahapatro Jul 10 '13 at 03:54
See http://stackoverflow.com/questions/1710476/print-query-string-in-hibernate-with-parameter-values – Stefan Steinegger Jul 10 '13 at 05:57

Which Hibernate Join operation is more efficient?

1 Answers1