The number of queries depends on the fetch strategy. Currently there are two fetch strategy:
<property name="openjpa.jdbc.EagerFetchMode" value="join"/> <property name="openjpa.jdbc.EagerFetchMode" value="parallel"/> If you are using join strategy, in the example that A contains oneToMany relation to B, and B contains OneToMany relation to C, openjpa will generate the following sql for JPQL "select a from EntityA a": SELECT t0.id, t0.age, t0.name FROM EntityA t0 SELECT t0.id, t0.age, t0.name FROM EntityB t0 WHERE t0.A_ID = ? SELECT t0.id, t0.name FROM EntityC t0 WHERE t0.B_ID = ? SELECT t0.id, t0.name FROM EntityC t0 WHERE t0.B_ID = ? The number of queries will be 1 + (L*M). where L is the number of A instances. M is the number of B instances per A. If you are using parallel strategy, openjpa will generate sql with inner join to retrieve all the data to reduce the number of queries. In the above example, a JPQL "select a from EntityA a" will only generate three queries: (1) SELECT t0.id, t0.age, t0.name FROM EntityA t0 (2) SELECT t0.id, t1.id, t1.age, t1.name FROM EntityA t0 INNER JOIN EntityB t1 ON t0.id = t1.A_ID ORDER BY t0.id ASC (3) SELECT DISTINCT t1.id, t2.id, t2.name FROM EntityA t0 INNER JOIN EntityB t1 ON t0.id = t1.A_ID INNER JOIN EntityC t2 ON t1.id = t2.B_ID ORDER BY t1.id ASC Hope this helps! Fay --- On Mon, 5/25/09, Michael Simons <[email protected]> wrote: > From: Michael Simons <[email protected]> > Subject: Re: Problem with join fetch > To: [email protected] > Date: Monday, May 25, 2009, 5:01 AM > Hello Catalina, > > > If you define ToMany fetch type to EAGER, then a > simple > > select a from A > > will get all Bs and Cs loaded. > > unfortunately, using EAGER does not solve the > (N+1)-problem. If there are L instances of A each > holds M instances of B and each b has N instances of C then > there will be L*M*N queries > generated to get the instances of C. > In the case that we need to solve here, there are about > L=100, M=15, N=2 and there's even one > more level D with about 2 instances per each c. So there > are about 6000 queries, to get > information that can be fetched in one single select. > > Michael > > >
