You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Sirisha Chiruvolu <ch...@gmail.com> on 2012/11/03 16:03:04 UTC
Re: Fetchgroups recursion problem
I have below domain classes
@FetchGroup(name="Group1", attributes={
@FetchAttribute(name="aCase")
})
Class A{
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="CASE_UID", nullable=false, insertable=false,
updatable=false)
private ClassB aCase.....
}
Class B{
@OneToMany(mappedBy = "aCase", fetch = FetchType.Lazy)
private List<Class C> caseList;...
}
Class C{
...
}
Class Test{
OpenJPAEntityManager oem = OpenJPAPersistence.cast(em);
em.clear();
oem.getFetchPlan().setMaxFetchDepth(3).addFetchGroup("Group1");
}
My problem is Class C records are not getting eagerly fetched, there are
sperate queries that are getting fired . I want to fetch all the records in
a single query.
Can anyone help me..
OpenJPAQuery q1=oem.createQuery(statement.toString());
--
View this message in context: http://openjpa.208410.n2.nabble.com/Fetchgroups-recursion-problem-tp3874382p7581598.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
RE: Fetchgroups recursion problem
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
I added OpenJPA test cases to https://issues.apache.org/jira/browse/OPENJPA-2296 that show the bug (TestRecursiveEagerSQL.java).
The N+1 select problem also occurs for ManyToOne when we only have two entities. E.g. A OneToMany B and when the ManyToOne relation from B to A is eager and we select B: then for each B it's A is loaded using a separate query, see TestManyOneEagerSQL.java. For A OneToOne B the problem does not occur, see TestOneOneEagerSQL.java.
I also found a partial fix: it can be fixed for the parallel eager select strategy by having RelationFieldStrategy.selectEagerJoin not limit recursive eager fetching to JOIN. I ran all tests in persistence-jdbc and it does result in the existing TestManyEagerSQL failing with duplicates in the list, perhaps this can be fixed using DISTINCT? (TestJPQLSubquery also fails but only because it is too sensitive to the generated SQL and is easily adjusted).
The join eager select strategy is not used at JDBCStoreManager.createEagerSelects because
- boolean hasJoin = fetch.hasJoin(fms[i].getFullName(false)); is false
- inEagerJoin = true (select query already has an eager to-one join)
I do not know if this can be fixed as well.
Finally a workaround can be done by using cached entities in the entity manager context. Simply select all entities that are otherwise loaded by their id as in the "load field" trace.
For the Company - Employee - Project example this would mean performing the query "SELECT e FROM Employee e" before the actual query.
So for Sirisha's problem selecting all B objects that would be returned inside the object graph return by the query for A could solve the problem.
RE: Fetchgroups recursion problem
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
Alright, I stopped complaining and started work at solving this problem. I created https://issues.apache.org/jira/browse/OPENJPA-2296 and I started debugging the issue with the Eclipse debugger to localize the problem.
I suspect that the bug is inside JDBCStoreManager.createEagerSelects(). This is the method that determines the list of Eager relations that is shown in the trace. When Project - Employee is toOne it does consider Employee.projects but somehow skips it. One theory I have is that it thinks this relation is a toOne relation as well, but of course this is a toMany. When Company - Employee is toMany, this same code does not skip the relation and adds it as eager.
I think at a high level in the EagerResultList we then see the consequence: the project relations are not yet fetched inside the "main query" of ResultObjectProvider.open(). After this, the while loop of EagerResultList adds entities and each call to load an Employee entity has to do an actual database load instead of reusing the entities that should be present in the context.
I think someone more familiar with JDBCStoreManager now already has good directions of finding and fixing the problem and perhaps with the consequences of a fix.
I will try and debug some more and add my results as comments to OPENJPA-2296.
RE: Fetchgroups recursion problem
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
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
RE: Fetchgroups recursion problem
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
Last year I also reported an N+1 select problem with simple Maps that gives the same "load field" trace and may well have the same cause. https://issues.apache.org/jira/browse/OPENJPA-1920
About the issue reported here by Sirisha and also experienced by me...
The problem means that almost all objects deep in the graph structure can get fetched with a single query per object. These can be very many and should normally be efficiently fetched with a single or perhaps a few fixed queries. This problem can already occur when simply having an entity with a OneToOne or ManyToOne relation that has relations of its own. This seems to me to be extremely common. This is such a huge performance issue that I am really flabbergasted that no one else has reported it yet.
RE: Fetchgroups recursion problem
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
Sirisha (and OpenJPA committers please read!),
After some searching I found that I had already reported this problem 18 months ago http://openjpa.208410.n2.nabble.com/N-1-select-problem-with-related-entities-inside-a-OneToOne-or-ManyToOne-field-td6073663.html I didn't create a Jira issue back then because I could work around my problem.
In this example I took an example of the OpenJPA documentation where they have a structure like:
Company (OneToMany) Employee (OneToMany) Project
When querying for Company, OpenJPA uses 3 queries to fetch all reachable Employees and Projects, regardless of how many Employees and Projects there are.
However when I change the relation from Company to OneToOne (I think ManyToOne has the same effect), OpenJPA starts loading individual Employees with individual queries:
....
2905 testPU TRACE [main] openjpa.jdbc.SQLDiag - load field: 'projects' for oid=467 class entities.Employee
2905 testPU TRACE [main] openjpa.jdbc.SQL - <t 23117648, conn 1247640> executing prepstmnt 1830320 SELECT t0.id, t0.name FROM Project t0 WHERE t0.EMPLOYEE_ID = ? [params=?]
2906 testPU TRACE [main] openjpa.jdbc.SQL - <t 23117648, conn 1247640> [0 ms] spent
2907 testPU TRACE [main] openjpa.jdbc.SQLDiag - load field: 'projects' for oid=468 class entities.Employee
2907 testPU TRACE [main] openjpa.jdbc.SQL - <t 23117648, conn 1247640> executing prepstmnt 26341410 SELECT t0.id, t0.name FROM Project t0 WHERE t0.EMPLOYEE_ID = ? [params=?]
....
In my real application I have a complicated graph for a reservation of multiple rooms with a customer with contacts and many product order lines and related products and many more related entities. This problem occurs at different places in the object graph (below the Customer entity and below the order line entity) and s really killing the performance of the most important entity in our application because it can take about 3.5 seconds to fetch a single relatively simple reservation. It is especially bad when making a report on multiple reservations based on a time range OpenJPA query.
My complex graph also seems to suffer from a "load field" problem that may be caused by ProductOrderLine having a parent-child relation to itself ("package deals"), not sure if this is an instance of the same problem.
Can someone from OpenJPA at least look into this issue? If necessary I can create a JIRA issue with the simple Company - Employee - Project structure.
Regards,
Henno
-----Oorspronkelijk bericht-----
Van: Sirisha Chiruvolu [mailto:chsiri100@gmail.com]
Verzonden: woensdag 7 november 2012 18:02
Aan: users@openjpa.apache.org
Onderwerp: RE: Fetchgroups recursion problem
Henno,
Thanks for your response.
Yes my concern is c is getting fetched with more queries(N+1), rather being
fetched along with A.
Thanks
Sirisha
--
View this message in context: http://openjpa.208410.n2.nabble.com/Fetchgroups-recursion-problem-tp3874382p7581654.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
RE: Fetchgroups recursion problem
Posted by Sirisha Chiruvolu <ch...@gmail.com>.
Henno,
Thanks for your response.
Yes my concern is c is getting fetched with more queries(N+1), rather being
fetched along with A.
Thanks
Sirisha
--
View this message in context: http://openjpa.208410.n2.nabble.com/Fetchgroups-recursion-problem-tp3874382p7581654.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
RE: Fetchgroups recursion problem
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
I'm not sure I can help, but I may have seen some similar performance problems in my code.
What is the query you use? What do you mean by class C is not eagerly fetched?
>From your post I assume that you are querying for an A object. In my case when I did that, some related objects (like C in your case) are fetched, but with way too many queries than you would use when hand writing the queries.
So do you really mean that C is not yet fetched when querying for A or do you mean that when you query for A you can see from OpenJPA's trace that C is fetched with way too many queries (N + 1 select problem)?
Henno
-----Oorspronkelijk bericht-----
Van: Sirisha Chiruvolu [mailto:chsiri100@gmail.com]
Verzonden: zaterdag 3 november 2012 16:03
Aan: users@openjpa.apache.org
Onderwerp: Re: Fetchgroups recursion problem
I have below domain classes
@FetchGroup(name="Group1", attributes={
@FetchAttribute(name="aCase")
})
Class A{
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="CASE_UID", nullable=false, insertable=false,
updatable=false)
private ClassB aCase.....
}
Class B{
@OneToMany(mappedBy = "aCase", fetch = FetchType.Lazy)
private List<Class C> caseList;...
}
Class C{
...
}
Class Test{
OpenJPAEntityManager oem = OpenJPAPersistence.cast(em);
em.clear();
oem.getFetchPlan().setMaxFetchDepth(3).addFetchGroup("Group1");
}
My problem is Class C records are not getting eagerly fetched, there are
sperate queries that are getting fired . I want to fetch all the records in
a single query.
Can anyone help me..
OpenJPAQuery q1=oem.createQuery(statement.toString());
--
View this message in context: http://openjpa.208410.n2.nabble.com/Fetchgroups-recursion-problem-tp3874382p7581598.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.