Hello Fay, Thanks a lot that's what I was looking for. Hope it works.
Thanks, Michael Fay Wang schrieb: > 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 >> >> >> > > > >
