I have (re)read the manual on fetch groups and on eager fetching but I cannot
find anything which solves the problem.
I tried all performance improvement tricks I currently know of
- left join fetch ("SELECT z FROM " + Company.class.getSimpleName() + " z LEFT
JOIN FETCH z.employee LEFT JOIN FETCH z.employee.projects")
- setting openjpa.jdbc.EagerFetchMode to join
- instead of marking the relation as EAGER, created a fetch group
EmployeeProjects with recursionDepth = 1000
- marking Employee to Company as lazy and selecting all Employees with the same
entityManager before the main query. This shifts the N+1 select problem on
Employee.projects to the first query and reuses the cached Employees in the
main query.
- marking Project to Employee as lazy and selecting all Projects with the same
entityManager before the main query
Note that the problem also occurs when Company - Employee is ManyToOne instead
of OneToOne, but NOT when the relation from Employee to Project is a ToOne
relation.
Anytime the problem occurs I get this trace:
3395 testPU TRACE [main] openjpa.Query - Executing query: SELECT z FROM
Company z
3396 testPU TRACE [main] openjpa.jdbc.SQLDiag - Eager relations:
[entities.Company.employee]
For each Employee:
...
4074 testPU TRACE [main] openjpa.jdbc.SQLDiag - load field: 'projects' for
oid=1100 class entities.Employee
4075 testPU TRACE [main] openjpa.jdbc.SQL - <t 20056641, conn 33017287>
executing prepstmnt 4428272 SELECT t0.id, t0.EMPLOYEE_ID, t0.name FROM Project
t0 WHERE t0.EMPLOYEE_ID = ? [params=?]
...
What may be very interesting for fixing the problem in OpenJPA's code is that
the first query for Company shows "Eager relations:
[entities.Company.employee]", even when Company.employees is definitely in the
fetchplan or marked EAGER.
When having Company - Employee as a OneToMany relation it shows "Eager
relations: [entities.Employee.projects, entities.Company.employees]"
The only ugly workaround I can find for the problem might be to make it
OneToMany and let the application enforce that there is at most one element in
the list.
Hope this is enough information so that someone can help us out with solving
this.
Regards,
Henno
