Hibernate performance issues with join queries

Question

We are using dynamic HQL query generator which generates query like below query:

select sum(en1.amount) from SampleEntity1 as en1 where en1.child.name = ?

Hibernate will convert above query to sql as follows:

select sum(tbl1.c_amount) from sampletable1 as tbl1, samplechild as child1 where tbl1.c_child = child1.c_id and child1.c_name = ?

Tables sampletable1 and samplechild both have more than 1 million records and because the generated sql has cross join, the performance of the query is very low and it suffers lots of database I/O. But if we run the sql using inner join the query will be run in much better performance.

Is there any way to force hibernate to convert such HQL queries to inner join instead of cross join?

would you like to give Batoo JPA a chance. Batoo JPA is the new JPA implementation with +15x faster performance. batoo.jp — Hasan Ceylan
– Hasan Ceylan, Commented Oct 6, 2012 at 12:32

JB Nizet · Accepted Answer · 2012-01-28 09:38:22Z

1

This is an inner join, but it's just not using the inner join syntax. If you want it with an inner join syntax, use an inner join, as explained in the Hibernate reference manual:

select sum(en1.amount) from SampleEntity1 as en1 inner join en1.child child where child.name = ?

I'm surprise that both queries don't execute in the same time. They're equivalent, and should use the same indices.

answered Jan 28, 2012 at 9:38

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arash Zareh Over a year ago

Thanks for your answer, but I want to force hibernate to convert en1.child to sql query like sampletable1 as tbl1 inner join samplechild as child on child.tbl1_fk = tbls1.id

JB Nizet Over a year ago

Yes. That's precisely what the query in my answer does.

Arash Zareh Over a year ago

I changed my HibernateDialect and everything goes right now and all queries are converting to inner join.

Mohammed Nosirat Over a year ago

@JBNizet the both queries executed in the same time on database, but hibernate uses reflection after getting results (in case of cross join).

Collectives™ on Stack Overflow

Hibernate performance issues with join queries

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related