[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Number of problems with query hint extensions
|
Hello Bernard,
Thanks for the links. I will add comments or open a new issue.
>From my point of view JPQL or even JPA as a whole is a step backwards compared to JDO 2.0, esp
the FetchPlan/FetchGroup.
Also with JDO you had to know SQL, of course. As JDO did only determine what to do but not how,
you had to know the tops and flaws of the implementations to make a decision. So you often had
to study the SQL statements that were generated.
But that's quite the same with JPA, so there's no progress in this point.
As you already might have guessed, we are porting an application implemented with JDO to JPA (to
get it 3tier-ready). There are a lot of things we have to struggle with, one of the most
important is how queries are mapped to SQL (another is the lack of "entity uniqueness within a
persistence context").
To sum up, we are disappointed by JPA, because we have at lot of work and cost to migrate but no
benefit at all.
Because of these experiences, I will not use JPA in another project I'm working on.
Kind Regards, Michael
bht@xxxxxxxxxxxxx schrieb:
> Hi Michael,
>
> Regarding unnecessary joins, if you can identify them, I would like to
> ask you to make a testcase and file it as bug containing the phrase
> "unnecessary join" in the description to protect your interests and
> the interests of the community. I think the wole idea is that
> computers do the work and not we developers become the slaves of the
> computers.
>
> It is ridiculous that when writing compact, good JPQL one then has to
> check the generated SQL for inflated, inefficient syntax.
>
> Quite the contrary should happen:
>
> Different databases have different tricks of writing efficient,
> performing SQL, and the JPA provider, while providing an abstraction,
> should take care of them. It just blows my mind what is happening
> today.
>
> In using the native EclipseLink API, one becomes dependent on a
> specific JPA provider, in addition to the risk of database vendor
> lock-in. Just check the number of digits in Oracle license prices.
>
> You are quite welcome to make your comments on the following
> EclipseLink issues, and you are welcome to copy the tescases as
> starter in case it helps you filing an issue quicker. I know it is all
> additional work, but in the end it pays off when they start getting it
> right instead of forcing us to learn yet another API.
>
>
> JPQL exists subquery generates unnecessary table join
> https://bugs.eclipse.org/bugs/show_bug.cgi?id=298494
>
> https://bugs.eclipse.org/bugs/show_bug.cgi?id=300625
> Unnecessary Table Join in Native Query generated from JPQL
>
> https://bugs.eclipse.org/bugs/show_bug.cgi?id=301599
> Unnecessary Table Join in Native Many-to-Many Query generated from
> JPQL
>
>
> All the best,
>
> Bernard
>
>
> On Fri, 26 Feb 2010 19:28:44 +0100, you wrote:
>
>> Hello Tom,
>>
>> I've tried the possibility mentioned by Andrei, but that seems to slow because of the
>> unnecessary outer-joins. So it only works for QueryHints.FETCH.
>> So the Native EclipseLink API definitely is an option for us. Can You please tell where to get
>> information about it.
>>
>> Kind Regards, Michael
>>
>>
>> Tom Ware schrieb:
>>> Hi Michael,
>>>
>>> The problem here is that each of your hints is being treated as
>>> individual.
>>>
>>> i.e.
>>> x.locatedVehicles causes one fetch and x.locatedVehicles.tours causes a
>>> completely independent fetch and so on. There is probably something we
>>> could do here to enhance the framework to allow expressions like
>>> x.locatedVehicles and x.locatedVehicles.tours to refer to the same
>>> thing. (maybe some kind of aliasing strategy like JPQL uses for its
>>> non-fetch joins) Please feel free to enter an enhancement.
>>>
>>> To workaround, the only thing I can think of is to use Native
>>> EclipseLink API. Let me know if that is an option for you and I can
>>> help out.
>>>
>>> As for batching, that works a little differently, you should not see
>>> any SQL related to the batch on your original query, but when you
>>> traverse the relationship that is batched, you will get all the objects
>>> on the other side of the relationship as a batch.
>>>
>>> -Tom
>>>
>>> Michael Simons wrote:
>>>> 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
>>>
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>
