openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Simons <michael.sim...@optitool.de>
Subject Re: Problem with join fetch
Date Tue, 26 May 2009 17:17:30 GMT
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 <michael.simons@optitool.de> wrote:
> 
>> From: Michael Simons <michael.simons@optitool.de>
>> Subject: Re: Problem with join fetch
>> To: users@openjpa.apache.org
>> 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
>>
>>
>>
> 
> 
>       
> 



Mime
View raw message