Re: [eclipselink-users] N + 1 SELECT: not seeing it on an EAGER load

[Christopher Delahunt <christopher.delahunt@xxxxxxxxxx>]

This is still the N+1 problem.  Batch fetching forces the N queries for 
each set of Bs into a single query, and is not enabled by default.

The find query only return a single value, an A with ID=1 and so only 
needed a single additional query for its associated Bs.  Had it been a 
readallquery with N results, you would have seen 1 Query for all As, 
then N queries for each A's Bs.

Best Regards,
Chris





On 13/06/2013 1:39 PM, Laird Nelson wrote:
> I want to make sure I'm understanding the behavior of EclipseLink
> properly with regards to when it will spontaneously avoid the N + 1
> SELECTs problem.
>
> I have an entity A with a @OneToMany relationship with B.  The fetch
> type here is marked FetchType.EAGER for various reasons, most of which
> are invalid :-) but let's just leave that alone for now since I don't
> really have control over it. :-)  It also has an @OrderBy annotation
> specifying a displayOrder field.
>
> If I do an entityManager.find(A.class, 1L), I see (as I would expect)
> the SQL fly by that selects A's fields and a WHERE clause specifying A's
> id (1 in this case).
>
> Fine so far.
>
> Now I would expect to see the N + 1 SELECT problem: that is, EclipseLink
> is now going to go get all of A's Bs, since they're supposed to be
> loaded eagerly.  So I brace myself for an onslaught of SELECT b.x, b.y
> FROM b WHERE (b.id <http://b.id> = ?).  I've seen this too many times
> before and am familiar (I think!) with what causes the problem.
>
> But instead I see (surprising to me, but surprising in a good way :-)) a
> single SQL statement that appears to grab all Bs in one shot:
>
>     SELECT b.x, b.y FROM b WHERE (b.aid = ?) ORDER BY b.displayOrder ASC
>
>
> This is nice.  Huh, I think, batch fetching for free.  I check around to
> see if someone has specified any batch fetching hints, but they haven't.
>   The only two "out of the ordinary" things I can see are:
>
>   * a FetchType of EAGER
>   * a JPA @OrderBy annotation
>
> So: under what unhinted circumstances will EclipseLink avoid the N + 1
> problem?  Is it the EAGER specification that's causing this behavior, or
> the @OrderBy (obviously to order a list you need a list, not individual
> one-shot retrievals)?
>
> Thanks,
> Best,
> Laird
>
> --
> http://about.me/lairdnelson
>
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users