Hello,
I am having performance problems with eager loading and OneToOne (or ManyToOne).
I mark all my relations EAGER. The example of chapter 8.1 about parallel eager
fetch mode with a structure of Company - Employee - Project where each relation
is OneToMany works fine: selecting N companies always creates just 3 SQL
queries.
However when I change the List<Employee> in Company to a single Employee (we
could call it the CEO) and map it with @OneToOne or @ManyToOne the trouble
starts. When I select all companies, for each company a SQL select statement is
performed to select the list of Projects inside Employee.
....
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=?]
....
The big difference in this situation is that Company now has the foreign key to
Employee instead of vice versa.
Question 1 a) Is this a bug in OpenJPA or is this expected behavior?
b) is there a way to bring back selecting all companies to 3 queries (or at
least independent of N)?
My real use case is an internationalized String (a StringI18N entity) that
holds different translations of a String through a List<StringI18NSingleValue>
where each StringI18NSingleValue has a Locale and a String (should in fact be a
Map but this gave another performance problem as described in
https://issues.apache.org/jira/browse/OPENJPA-1920).
I currently map this with OneToOne, which means that I get this performance
problem for each entity that needs to have translated Strings. It is not
possible (and not nice) to move the OneToOne foreign key from the entity to
StringI18N because many different unrelated entities may have StringI18N's.
I know two solutions with drawbacks:
- Hardcoding the available languages as different columns in StringI18N.
Drawback: makes it impossible for users of our application to add new languages.
- Serialize the StringI18N. Drawback: cannot perform queries on the Strings.
Question 2: any ideas how I can solve this performance problem for my
StringI18N use case without resorting to this?
Regards,
Henno Vermeulen
Huize Molenaar
