You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Jostein Gogstad (JIRA)" <ji...@apache.org> on 2013/09/02 14:24:55 UTC

[jira] [Issue Comment Deleted] (OPENJPA-2296) N+1 select problems when eager fetching OneToOne and ManyToOne fields

     [ https://issues.apache.org/jira/browse/OPENJPA-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jostein Gogstad updated OPENJPA-2296:
-------------------------------------

    Comment: was deleted

(was: Thanks for the reply, [~jan.vermeulen@isencia.com].

Your solution is viable if you know compile time which entites that will be loaded in the OneToMany-relation, or if you can infer it runtime before you select the entities containing the OneToMany-relations (the employees in the example in the description. If you dont then - continuing the example in the description - you have to mark the OneToMany as lazy, do a query that selects the companies (the entity containing the ManyToOne or OneToOne) and then do a query that loads the entities that will at some time be in the employees OneToMany-relation into the persistence context (phew!). OpenJPA will then hopefully use the entities in the persistence context when the lazy loaded OneToMany-releation is accessed.

The downside of this solution is of course that the entities has to be lazy-loaded by default, which might hurt performance elsewhere. I guess you could achieve something by keeping the OneToMany-releation eager loaded and then remove it from the fetch plan (http://openjpa.apache.org/builds/2.2.2/apache-openjpa/docs/ref_guide_fetch.html#ref_guide_fetch_single_field) whenever you need to load Company-like objects with a ManyToOne or OneToOne relation.)
    
> N+1 select problems when eager fetching OneToOne and ManyToOne fields
> ---------------------------------------------------------------------
>
>                 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
>         Attachments: OPENJPA-2296-tests.zip
>
>
> EDIT: 
>  - an N+1 select problem also always occurs when eagerly fetching a ManyToOne field that is bidirectional (see TestManyOneEagerSQL)
>  - The patch of "RelationFieldStrategy.selectEagerJoin" I suggested works for the unit tests but does not work in my real application so that may be a different N+1 select issue. (Somehow my fields are in the list of eager relations in OpenJPA's trace, but OpenJPA still uses a "load field" for them.)
> 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