N+1 select problem with related entities inside a OneToOne or ManyToOne field

[Henno Vermeulen <he...@huizemolenaar.nl>]

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