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

[hoeft]

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:

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

The resulting SQL-Statement:

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:

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:

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:

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

Thanks
Hoeft

[fhh]

You can not enforce join order. Not in JPA QL nor in SQL. The join order is determined by the rdbms on what seems most useful (leaving aside dirty nasty things like hints.)

Regards

Felix

[hoeft]

Thank your for your answer.

But: Your are wrong, it is possible to enforce the join order in SQL. Dan Tow describes it in his book and I think his book wouldn't have got such a good resonance if he hadn't known what he was writting about.

Consider the following Sql-Statement:

 select *
 from Table1 T1, Table2 T2, Table3 T3
 where T1.Key2_ID=T2.Key2_ID
 and T1.Key3_ID = T3.Key3_ID;

If I want to enforce the join oder T1,T3,T2 this can be easily done:

 select *
 from Table1 T1, Table2 T2, Table3 T3
 where T1.Key3_ID = T3.Key3_ID
 and T1.Key2_ID + 0 * T3.Key3_ID =T2.Key2_ID;

So it is possible in SQL to enforce the join order I want.

Hoeft

[fhh]

select *
 from Table1 T1, Table2 T2, Table3 T3
 where T1.Key3_ID = T3.Key3_ID
 and T1.Key2_ID + 0 * T3.Key3_ID =T2.Key2_ID

This is just sick disgusting rubbish! Who ever invented that stuff - fire him!

Regards

Felix

[oskar.carlstedt]

This is just sick disgusting rubbish! Who ever invented that stuff - fire him!

No, sorry, this is not "sick disgusting rubbish". As a matter of fact, the join order is of importance for many databases. One can think that a databse's execution planner should fix this, but some of them aren't.

Ok, why is the order of importance. When joining the same table with different other tables one get subsets of data that in turn will be joined again etc.

In this example whe have T1, T2 and T3. Let say a join between T1 and T2 gives 1.000.000 records in return and a join between T1 and T3 gives 1000 records in returns. Of course it is better to do the T1 and T3 join before and the make a join between result of (T1, T3) and T2. It will speed up a lot because you are cutting off most of the records in the first join.

With kind regards
Oskar

[fhh]

Yeah, but this is what proper statistics are good for. The "solution" is just a nasty hack.

If the query optimizer chooses an obviously wrong join order than something is wrong with the statistics. The proper way is to fix those.

Three reasons suddenly come to my mind why using this "hack" is wrong:

1.) You have to modify every single query in your application.

2.) The queries become less readable. Your code will be less maintainable.

3.) The behaviour is unpredictable. New useful indxes might not be used; the query optimizer might realize in later versions that multiplying with 0 is always 0...

Don't cure the symptoms. Fix the problem.

Regards

Felix