openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vermeulen (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (OPENJPA-2296) N+1 select in eager fetching relation fields of toOne relations
Date Fri, 09 Nov 2012 11:36:12 GMT

    [ https://issues.apache.org/jira/browse/OPENJPA-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13493917#comment-13493917
] 

Vermeulen edited comment on OPENJPA-2296 at 11/9/12 11:35 AM:
--------------------------------------------------------------

Moving up the call stack I see that the eager select type used for fetching the field entities.Company.employee
is indeeds set to join
by RelationFieldStrategy.selectEagerJoin. I think it is logical that a toOne uses a join instead
of parallel select. However somehow further on this causes JDBCStoreManager.select to not
consider parallel mode anymore for any of the relation fields of Employee. This can be seen
where it calls createEagerSelects using the line

eager = Math.min(eager, fetch.getEagerFetchMode()) 

which results in JOIN, not parallel. Somehow this also doesn't result in an efficient SQL
with join but gives the N+1 select.

I suspect that this problem may have been introduced when someone tried to prevent other bugs.
The RelationFieldStrategy.selectEagerJoin that restricts the eager select mode to join has
this comment (already in oldest version of svn):
        // limit the eager mode to single on recursive eager fetching b/c
        // at this point the select has been modified and an attempt to
        // clone it for a to-many eager select can result in a clone that
        // produces invalid SQL

I am now giving up my debugging because I really have insufficient knowledge of OpenJPA to
know how to proceed from this without introducing new bugs. But I think I have provided more
than enough information for an active OpenJPA committer to proceed from here to solve the
problem for which I would  be very grateful.
                
      was (Author: slowstrider):
    Moving up the call stack I see that the eager select type used for fetching the field
entities.Company.employee is indeeds set to join
by RelationFieldStrategy.selectEagerJoin. I think it is logical that a toOne uses a join instead
of parallel select. However somehow further on this causes JDBCStoreManager.select to not
consider parallel mode anymore for any of the relation fields of Employee. This can be seen
where it calls createEagerSelects using the line

eager = Math.min(eager, fetch.getEagerFetchMode()) 

which results in JOIN, not parallel. Somehow this also doesn't result in an efficient SQL
with join but gives the N+1 select.

I suspect that this problem may have been introduced when someone fixed another bug. The RelationFieldStrategy.selectEagerJoin
that restricts the eager select mode to join has this comment:
        // limit the eager mode to single on recursive eager fetching b/c
        // at this point the select has been modified and an attempt to
        // clone it for a to-many eager select can result in a clone that
        // produces invalid SQL

I am now giving up my debugging because I really have insufficient knowledge of OpenJPA to
know how to proceed from this without introducing new bugs. But I think I have provided more
than enough information for an active OpenJPA committer to proceed from here to solve the
problem for which I would  be very grateful.
                  
> N+1 select in eager fetching relation fields of toOne relations
> ---------------------------------------------------------------
>
>                 Key: OPENJPA-2296
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2296
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, kernel, performance
>    Affects Versions: 2.2.1
>            Reporter: Vermeulen
>            Priority: Critical
>
> We have very serious performance problems in fetching our JPA entities caused by N+1
select problems. I suspect that these are caused by a small bug in OpenJPA's eager fetching
implementation that should not be too hard to fix. This is especially bad in our relatively
complex reservation entity where we need to eagerly fetch customer, contacts, orderlines,
products etc. It takes over 3 seconds to fetch a single reservation from a local db. The problem
seems to occur at multiple places in this object graph. The cause of the problem is probably
the following issue where eager fetching of relations of toOne relations use neither a parallel
or join select strategy but uses separate "load field"s of EACH field of EACH toOne relation
in the object graph. I am not the only one having this issue, see also mailing list http://openjpa.208410.n2.nabble.com/Fetchgroups-recursion-problem-td3874382.html#a7581598
> Suppose we have entities Company 1..* Employee 1..* Project
> According to Chapter 8. Eager Fetching of the manual: "Continuing our example, if the
Employee class had a list of Projects in one of the fetch groups being loaded, OpenJPA would
execute a single additional select in parallel to load the projects of all employees of the
matching companies."
> This is indeed what happens when both relations are eager (or are marked lazy but are
in the fetchplan). The number of queries needed to fetch a Company does not depend on the
number of employees or projects:
> 8913  testPU  TRACE  [main] openjpa.Query - Executing query: SELECT z FROM Company z
> 19885  testPU  TRACE  [main] openjpa.jdbc.SQLDiag - Eager relations: [entities.Employee.projects,
entities.Company.employees]
> 19889  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 12833564, conn 5535786> executing
prepstmnt 20965449 SELECT t0.id, t0.name FROM Company t0
> 19891  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 12833564, conn 5535786> [2 ms]
spent
> 20237  testPU  TRACE  [main] openjpa.jdbc.SQLDiag - Eager relations: [entities.Employee.projects,
entities.Company.employees]
> 20237  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 12833564, conn 5535786> executing
prepstmnt 21924553 SELECT t0.id, t1.id, t1.name FROM Company t0 INNER JOIN Employee t1 ON
t0.id = t1.COMPANY_ID ORDER BY t0.id ASC
> 20248  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 12833564, conn 5535786> [10
ms] spent
> 21308  testPU  TRACE  [main] openjpa.jdbc.SQLDiag - Eager relations: [entities.Employee.projects,
entities.Company.employees]
> 21309  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 12833564, conn 5535786> executing
prepstmnt 13123665 SELECT DISTINCT t1.id, t2.id, t2.name FROM Company t0 INNER JOIN Employee
t1 ON t0.id = t1.COMPANY_ID INNER JOIN Project t2 ON t1.id = t2.EMPLOYEE_ID ORDER BY t1.id
ASC
> 21376  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 12833564, conn 5535786> [67
ms] spent
> 22247  testPU  TRACE  [main] openjpa.jdbc.JDBC - <t 12833564, conn 0> [0 ms] close
> However when the relation between Company and Employee is replaced with a OneToOne or
ManyToOne relation, this efficient eager fetching fails and OpenJPA resorts to single object
loads for the projects field of each Employee:
> .... 
> 41580  testPU  TRACE  [main] openjpa.Query - Executing query: SELECT z FROM Company z
> 49121  testPU  TRACE  [main] openjpa.jdbc.SQLDiag - Eager relations: [entities.Company.employee]
> 49121  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 22418487, conn 10229202> executing
prepstmnt 17563180 SELECT t0.id, t1.id, t1.name, t0.name FROM Company t0 LEFT OUTER JOIN Employee
t1 ON t0.id = t1.COMPANY_ID
> 49142  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 22418487, conn 10229202> [21
ms] spent
> 61282  testPU  TRACE  [main] openjpa.jdbc.SQLDiag - load field: 'projects' for oid=51
class entities.Employee
> 62593  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 22418487, conn 10229202> executing
prepstmnt 27638389 SELECT t0.id, t0.name FROM Project t0 WHERE t0.EMPLOYEE_ID = ? [params=?]
> 62594  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 22418487, conn 10229202> [1
ms] spent
> 64554  testPU  TRACE  [main] openjpa.jdbc.SQLDiag - load field: 'projects' for oid=52
class entities.Employee
> 67645  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 22418487, conn 10229202> executing
prepstmnt 18914646 SELECT t0.id, t0.name FROM Project t0 WHERE t0.EMPLOYEE_ID = ? [params=?]
> .... 
> In the first (efficient) case OpenJPA logs
>   openjpa.jdbc.SQLDiag - Eager relations: [entities.Employee.projects, entities.Company.employees]
>  
> In the second (inefficient) case OpenJPA logs
>   openjpa.jdbc.SQLDiag - Eager relations: [entities.Company.employee]
> This issue may be related to https://issues.apache.org/jira/browse/OPENJPA-1920 which
also uses single "load field"s.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message