[EJB 3.0] - HQL/ JPA-QL Query Tuning: How to enforce the join order
by 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
View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4042797#4042797
Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4042797
17 years, 4 months
[Performance Tuning] - HQL/ JPA-QL Query Tuning: How to enforce the join order
by 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
View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4042796#4042796
Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4042796
17 years, 4 months