HQL/ JPA-QL Query Tuning: How to enforce the join order

hoeft · **Posted:** Thu May 03, 2007 7:51 am

Hi!

I have read the book "SQL Tuning" by Dan Tow and want to optimise the important HQL/JPA-QL queries of my application, but I not sure how to enforce the optimal execution plan :-(.

Consider the following JPA-QL-Query:

Code:

select o
from Operation o
where o.order.flight.etd = :flight

The resulting SQL-Statement:

Code:

select
  ....
 from
  Operation operation0_,
  OrderTab order1_,
  Flight flight2_ 
 where
  order1_.flight_id=flight2_.id 
  and operation0_.order_id=order1_.id 
  and flight2_.etd=?

Let's say I want to get the following join order: Flight, OrderTab, Operation. How can I get it? Exists there a way to enforce this join order with Jpa-QL or HQL?
If it were possible to enforce the join order with JPA-QL I would only have to change the JPA-QL query, the other application code would remain unchanged:

Code:

Query query = em.createQuery(<JPA-QL Query which enforces the join order>);
Operation op = (Operation)em.getSingleResult(); 
....

But if the only way to enforce the join order is to execute a sql query instead the HQL-Query, I think would have to write the following code:

Code:

Query query = em.createNativeQuery(<SQL Query which enforces the join order>);
Object[] ar = (Object[])em.getSingleResult(); /*the result of the query won't be an object with the type Operation but an array of objects (the attribute values)*/
Operation op = (Operation)new Operation().setAttr1(ar[0]).setAttr2(ar[1]) ...;

The code above is very clumsy and not very convenient. If sql is the only way to enforce the wanted execution plan, can I let hibernate transform the result of the query into an Operation-object?
The code changes in this case would be minimal:

Code:

Query query = em.createNativeQuery(<SQL Query which enforces the join order>);
Operation op = (Operation)em.getSingleResult();

Thanks
Hoeft

nathanmoon · **Posted:** Thu May 03, 2007 10:15 am

Yes, you can use SQL queries and still get entities back. Check out the docs here: http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#d0e13059

hoeft · **Posted:** Thu May 03, 2007 10:36 am

Thank your very much for your answer, I will try it if no one knows a solution with HQL / JPA-QL;

Hoeft