Re: [eclipselink-users] Number of problems with query hint extensions

[Michael Simons <michael.simons@xxxxxxxxxxx>]

Hello Andrei,

I've changed my JPQL query to
  query = em.createQuery("SELECT DISTINCT x FROM Depot x WHERE x.scenario = :currentScenario");
  query.setHint (QueryHints.LEFT_FETCH, "x.locatedVehicles.tours.positions");

The SQL that gets generated:
SELECT DISTINCT t1.depot_id, ...
FROM depot t1
LEFT OUTER JOIN vehicle t0 ON (t0.depot_id = t1.depot_id)
LEFT OUTER JOIN tour t2 ON (t2.vehicle_id = t0.vehicle_id)
LEFT OUTER JOIN (tour_position t3
LEFT OUTER JOIN order_stop t4 ON (t4.tour_position_id = t3.tour_position_id)
LEFT OUTER JOIN load_stop t5 ON (t5.tour_position_id = t3.tour_position_id)) ON (t3.tour_id =
t2.tour_id)
WHERE (t1.scenario_id = ?)

That's OK, but unfortunately the 'outer-join' is assumed for every joined table, that makes the
query less performant. So I have to test whether it's fast enough.

Thanks, Michael

Andrei Ilitchev schrieb:
> Instead of the three query hints specify a single query hint:
> query.setHint (QueryHints.FETCH, "x.locatedVehicles.tours.positions");
> 
> ----- Original Message ----- From: "Michael Simons"
> <michael.simons@xxxxxxxxxxx>
> To: "EclipseLink User Discussions" <eclipselink-users@xxxxxxxxxxx>
> Sent: Friday, February 26, 2010 11:03 AM
> Subject: Re: [eclipselink-users] Number of problems with query hint
> extensions
> 
> 
>> Hello once more,
>>
>> when I expand the query to also get the stops within a tour like this:
>> query = em.createQuery("SELECT DISTINCT x FROM Depot x WHERE
>> x.scenario = :currentScenario");
>> query.setHint (QueryHints.FETCH, "x.locatedVehicles");
>> query.setHint (QueryHints.FETCH, "x.locatedVehicles.tours");
>> query.setHint (QueryHints.FETCH, "x.locatedVehicles.tours.positions");
>>
>> things get even worse, as you can see by the SQL statement below. As
>> you might guess that
>> statement runs almost for ever.
>> That's because the statement is much too complicated, e.g. the vehicle
>> is joined three times!
>> But that's not necessary at all, it's bloated.
>> Why is this done?
>>
>>
>> SELECT DISTINCT t1.depot_id, t1.bot_pump_rate, t1.available, ...
>> FROM tour_position t6
>> LEFT OUTER JOIN order_stop t7 ON (t7.tour_position_id =
>> t6.tour_position_id)
>> LEFT OUTER JOIN load_stop t8 ON (t8.tour_position_id =
>> t6.tour_position_id), tour t5, vehicle
>> t4, tour t3, vehicle t2, depot t1, vehicle t0
>> WHERE ((t1.scenario_id = ?)
>> AND ((((((t0.depot_id = t1.depot_id) AND (t2.depot_id = t1.depot_id))
>> AND (t3.vehicle_id = t2.vehicle_id)) AND (t4.depot_id = t1.depot_id))
>> AND (t5.vehicle_id = t4.vehicle_id)) AND (t6.tour_id = t5.tour_id)))
>>
>> kind Regards, Michael
>>
>> Michael Simons schrieb:
>>> Hello,
>>>
>>> Given the follwing JPQL Query:
>>> "SELECT DISTINCT d FROM Depot d WHERE d.scenario = :currentScenario"
>>>
>>> with the following classes:
>>> @Entity @Table(name="depot") class Depot {
>>>   ...
>>>   List<Vehicle> locatedVehicles;
>>> }
>>>
>>> @Entity @Table(name="vehicle") class Vehicle {
>>>   ...
>>>   List<Tour> tours;
>>> }
>>>
>>> @Entity @Table(name="tour") class Tour {
>>>   ...
>>> }
>>>
>>> We want EL to query not only the depots within a given scenario but
>>> also all vehicles located at
>>> these depots, and all tours driven by theses vehicles (trucks).
>>> So, we set the following hints:
>>> query.setHint (QueryHints.LEFT_FETCH, "x.locatedVehicles");
>>> query.setHint (QueryHints.LEFT_FETCH, "x.locatedVehicles.tours");
>>>
>>> The following SQL-statement is created by EL:
>>> SELECT DISTINCT t1.depot_id, t1..., t0.vehicle_id, t0...,
>>> t2.vehicle_id, t2..., t3.tour_id, t3...
>>> FROM depot t1
>>> LEFT OUTER JOIN vehicle t0 ON (t0.depot_id = t1.depot_id)
>>> LEFT OUTER JOIN vehicle t2 ON (t2.depot_id = t1.depot_id)
>>> LEFT OUTER JOIN tour t3 ON (t3.vehicle_id = t2.vehicle_id)
>>> WHERE (t1.scenario_id = ?)
>>>
>>> Why is vehicle joined twice?
>>>
>>> On the other hand, when specifying
>>> query.setHint (QueryHints.BATCH, "x.locatedVehicles");
>>> query.setHint (QueryHints.BATCH, "x.locatedVehicles.tours");
>>>
>>> neither vehciles nor tours are selected at all.
>>>
>>> Is there anything wrong with these hints?
>>> Is there a detailed documentation on how to use QueryHints.BATCH /
>>> .FETCH / .LEFT_FETCH ?
>>>
>>> Kind Regards, Michael
>>> _______________________________________________
>>> eclipselink-users mailing list
>>> eclipselink-users@xxxxxxxxxxx
>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>
>>
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>
> 
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>