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

[Laird Nelson <ljnelson@xxxxxxxxx>]

On Thu, Jun 13, 2013 at 10:49 AM, Christopher Delahunt <christopher.delahunt@xxxxxxxxxx> wrote:

The find query only return [sic] a single value, an A with ID=1 and so only needed a single additional query for its associated Bs.

OK; I see.  From reading http://java-persistence-performance.blogspot.com/2010/08/batch-fetching-optimizing-object-graph.html, specifically the section entitled "N + 1 queries problem", it looked like EclipseLink would somehow issue a single query per child.  For example, in the example in that article the employee's phone numbers are each retrieved by ID, not by WHERE (employeeId = ?).  I guess that's not actually how it works?

What I hear you saying instead is that for any parent (A), by default all children of a @OneToMany relationship (Bs) will be selected in one shot for that one parent/A instance.  What will NOT be the case is that if I select lots of As, all of the relevant Bs will be selected in one shot, UNLESS I turn on batch fetching.

To put it yet another way:

• Without batch fetching: if I get one A that logically has 10 Bs, I'll have two queries: one for the A, and one for all Bs that relate to it.  (Or that's what I'm observing, and what I had a question about; I would have thought I'd have 11 queries, not 2.)
  
• Without batch fetching: if I get two As, each of which logically has 10 Bs, I'll have four queries: one each for each A, and one each for each A's Bs.  (Again, I thought I'd see 22 queries.)
• With batch fetching: if I get ten As, each of which logically has 10 Bs, I'll still get only two queries (not worrying about batch size at the moment): one for the As, and one for all relevant Bs.

My question can then be better asked: why, in my first bullet point above, do I see two queries and not 11?

Thanks,

Best,

Laird

--
http://about.me/lairdnelson