openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Henno Vermeulen <he...@huizemolenaar.nl>
Subject N+1 select problem with related entities inside a OneToOne or ManyToOne field
Date Mon, 28 Feb 2011 15:25:50 GMT
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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message