openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Dick <michael.d.d...@gmail.com>
Subject Re: How work around OpenJPA-1365
Date Thu, 19 Nov 2009 15:43:59 GMT
Hi Michael,

I think there are two issues here.

1. The FetchPlan implementation with OpenJPA doesn't generate JOIN FETCH
statements like you're expecting. As far as I know the only way to get the
behavior you're looking for is to put the JOIN FETCH clause in your JPQL
statement itself.

2. OpenJPA's query engine translates multiple JOIN FETCH clauses into
separate statements. I'm not sure why this was done or what it would take to
fix it, but at first glance that looks like the reason why your query :
"SELECT p from Product p JOIN FETCH p.displayOptions JOIN FETCH p.locals"
generates so many SQL statements..

The merit of issue 1 may depend on your environment. I've seen multiple
statements perform signifcantly better than a JOIN FETCH approach when
paired with OpenJPA's Large ResultSets. Your mileage may vary of course.

I'd have to dig in to the multiple statements aspect of issue 2. If it is a
result of splitting multiple JOIN FETCHes into multiple SQL statements I
suspect it'll be very involved to 'fix'. I could be pleasantly surprised
though.

-mike

On Thu, Nov 19, 2009 at 3:46 AM, Michael Simons
<michael.simons@optitool.de>wrote:

> Hello Mike,
>
> My Model:
> Product(1:*)LocalizedProduct
> Product(1:*)ProductDisplayOption
> ProductDisplayOption extends DisplayOption
>
> I've setup a FetchPlan with this:
> @FetchGroups({
>        @FetchGroup(name="productDetails", attributes={
>                                        @FetchAttribute(name="locals"),
>
>  @FetchAttribute(name="displayOptions")
>        })
> })
> public class Product implements java.io.Serializable, SingleLocalized,
> Customizable {
>  /** Localizations */
>  @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY,
> mappedBy="product")
>  private Set<LocalizedProduct> locals = new HashSet<LocalizedProduct> (13);
>
>  @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY,
> mappedBy="product")
>  private Set<ProductDisplayOption> displayOptions = new
> HashSet<ProductDisplayOption> (113);
>  ...
> }
>
> When running the following code:
>  private void retrieveProducts (ISentinel cache) {
>    Query query = em.createQuery ("SELECT p from Product p");
>    OpenJPAQuery oq = OpenJPAPersistence.cast (query);
>    oq.getFetchPlan ().setMaxFetchDepth (10).addFetchGroup
> ("productDetails");
>    for (Object obj : query.getResultList ()) {
>      ...
>    }
>  }
>
>
> Unfortunately the produced SQL looks like this:
> SELECT t0.product_id, t0.jdo_version, t0.alt_ex_id, t0.density, t0.ext_id,
> t0.indefinite FROM
> product t0
>
> SELECT t1.display_option_id, t0.display_option_id, t1.jdo_version,
> t1.display_color, ...
> FROM product_display_option t0 INNER JOIN display_option t1 ON
> t0.display_option_id =
> t1.display_option_id
> WHERE t0.product_id = ?
>
> SELECT t0.localized_product_id, t0.jdo_version, t0.description,
> t0.lnguage_id, t0.product_id
> FROM localized_product t0 WHERE t0.product_id = ?
>
> SELECT t1.display_option_id, t0.display_option_id, t1.jdo_version,
> t1.display_color, ...
> FROM product_display_option t0 INNER JOIN display_option t1 ON
> t0.display_option_id =
> t1.display_option_id
> WHERE t0.product_id = ?
>
> SELECT t0.localized_product_id, t0.jdo_version, t0.description,
> t0.lnguage_id, t0.product_id
> FROM localized_product t0 WHERE t0.product_id = ?
>
> ... // the second and third statement are repeated for every record in the
> product table.
>
> What I would expect (and got with the formerly used JDO Impl) is like this:
> SELECT ...
> FROM product t0
> LEFT JOIN product_display_option t1 on t1.product_id = t0.product_id
> JOIN display_option t2 on t2.display_option_id = t1.display_option_id;
> SELECT ...
> FROM product t0
> LEFT JOIN localized_product t1 on t1.product_id = t0.product_id;
>
> ---
> So how do I convince OpenJPA to join the tables when using fetchplan?
>
> When I run the following code instead without a fetchplan:
>  private void retrieveProducts (ISentinel cache) {
>    Query query = em.createQuery (
>      "SELECT p from Product p JOIN FETCH p.displayOptions JOIN FETCH
> p.locals");
>    for (Object obj : query.getResultList ()) {
>      ...
>    }
>  }
>
> The produced SQL looks like (what's more like what I would have expected,
> but still produces too
> much "single selects"):
> SELECT ...
> FROM product t0
> INNER JOIN product_display_option t1 ON t0.product_id = t1.product_id
> INNER JOIN display_option t2 ON t1.display_option_id = t2.display_option_id
>
> SELECT t0.localized_product_id, t0.jdo_version, t0.description,
> t0.lnguage_id, t0.product_id
> FROM localized_product t0
> WHERE t0.product_id = ?
> ... // The last statement again is executed for every record in the product
> table.
>
> How can I reduce the number of statements generated?
>
> kind regards,
> Michael
>
> Michael Dick schrieb:
> > Hi Michael,
> >
> > Regarding seeing OPENJPA-1365 on 'vanilla' OpenJPA 1.2.1, I'll have to
> take
> > a look at that. It probably happens if the query does not fetch records
> from
> > the database. If that's what you're seeing then I just need to update the
> > JIRA with that info.
> >
> > I'm not terribly familiar with JDO. I was under the impression that
> > OpenJPA's FetchPlans were identical (or nearly identical) to those in
> JDO.
> >
> > To use my example (Employee and Manager) you would do something like this
> :
> >     OpenJPAEntityManager em = getEntityManager();
> >
> >     OpenJPAQuery q = em.createQuery("SELECT m from Manager m ");
> >     q.getFetchPlan().addField(Manager.class, "employees");
> >
> >     List<Manager> managers = q.getResultList();
> >
> > There's a lot more in the manual about it, I'd start looking at [1].
> >
> > [1]
> >
> http://openjpa.apache.org/builds/1.2.1/apache-openjpa-1.2.1/docs/manual/manual.html#ref_guide_fetch
> >
> > Hope this helps,
> > -mike
> >
> >
> > On Tue, Nov 17, 2009 at 11:02 AM, Michael Simons <
> michael.simons@optitool.de
> >> wrote:
> >
> >> Hi Mike,
> >>
> >> Do you mean fetchplan like described in the manual in chapter 1.7.4?
> >> Or is there a possibility to use JDO-FetchPlan with OpenJPA? If so, is
> >> there a description, how
> >> to do that?
> >>
> >> kind regards,
> >> Michael
> >>
> >> Michael Dick schrieb:
> >>> Hi all,
> >>>
> >>> Daryl's correct, the duplicates returned by a JOIN FETCH clause are
> >> correct
> >>> behavior. If you don't want the duplicates to be returned you should
> use
> >> the
> >>> DISTINCT keyword and OpenJPA will remove duplicates from the list.
> >>>
> >>> There are several outstanding issues though (which I'm working on):
> >>>
> >>> 1.) OPENJPA-894:  When results are returned from the database OpenJPA
> >>> automatically removes duplicates from the list. If the results are
> >> fetched
> >>> from memory the duplicates reappear.
> >>>
> >>> 2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the distinct
> >>> keyword doesn't work. This is because OpenJPA merely prepends the
> >> DISTINCT
> >>> keyword to the SQL generated which doesn't work if you're selecting
> >> across
> >>> multiple tables. Instead we need to filter the result list after
> >> retrieving
> >>> from the database.
> >>>
> >>> There are two proposed fixes for OPENJPA-894 each of which have some
> >>> drawbacks.
> >>>
> >>> 3a.) Mike's fix : supports pagination but does not support multiple
> JOIN
> >>> FETCH statements (ie SELECT m FROM Manager m JOIN FETCH m.employees
> JOIN
> >>> FETCH m.projects returns the wrong number of results).
> >>>
> >>> 3b.) Fay's fix : supported multiple JOIN FETCH statements, but does not
> >>> support pagination (ie query.setMaxResults(), query.setFirstResult()
> >> doesn't
> >>> page forward as expected).
> >>>
> >>> So there is work being done, but it's turned out to be a very ticklish
> >> issue
> >>> to solve.
> >>>
> >>> At the risk of muddying the waters a bit if you're migrating a JDO
> >>> application have you considered using OpenJPA's FetchPlan
> implementation
> >> to
> >>> eagerly load some fields? Over medium - large datasets I've found this
> to
> >> be
> >>> significantly faster than using a JOIN FETCH, but YMMV.
> >>>
> >>> Hope this helps,
> >>>
> >>> -mike
> >>>
> >>> On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <daryl@6degrees.com>
> >> wrote:
> >>>> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
> >>>> <michael.simons@optitool.de>wrote:
> >>>>
> >>>>
> >>>>> You state, that you're query with distinct and join fetch does work
> >>>>> properly. But this would
> >>>>> mean OpenJPA-1365 doesn't occur, does it?
> >>>>>
> >>>> The JIRA states:
> >>>> This issue occurs if the proposed fix for OPENJPA-894 is in place.
> >>>>
> >>>> So 1365 does not occur unless you've patched your code such that 894
> is
> >>>> fixed. What version of OpenJPA are you using and do you have any
> patches
> >> in
> >>>> place?
> >>>>
> >>>>
> >>>>> When we call "select a from A a join fetch B" we get n instances
of
> A,
> >>>> with
> >>>>> n = numbers of
> >>>>> A-B-associations.
> >>>>>
> >>>> This is the correct behavior. I have found with OpenJPA 1.2.1, I get
> >>>> distinct rows of A which sounds like what you (and I) want but is
> >> improper.
> >>>> 894 shows that a second run of the query in the same EntityManager
> >> yields
> >>>> duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
> >>>>
> >>>> select distinct a from A a join fetch a.bs
> >>>> ?
> >>>>
> >>>> How about these two:
> >>>> select a from A a left join fetch a.bs
> >>>> select distinct a from A a left join fetch a.bs
> >>>>
> >>>> I don't want to give the impression that I'm an expert on the matter,
> >> just
> >>>> that I've dealt with this issue and I want to be sure my understanding
> >> of
> >>>> things is accurate.
> >>>>
> >>>> --
> >>>> Daryl Stultz
> >>>> _____________________________________
> >>>> 6 Degrees Software and Consulting, Inc.
> >>>> http://www.6degrees.com
> >>>> mailto:daryl@6degrees.com
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message