[OPENJPA-1920] N+1 select with entities that contain Maps (even with FetchType.EAGER)

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.0.0
• Component/s: kernel
• Assignee: Unassigned
• Reporter: Vermeulen
• Created: 07/Jan/11 16:00
• Updated: 14/Nov/12 10:00

Description

When I have an entity that contains a Map, e.g.:

@Entity
public class TestEntity {
@ElementCollection(fetch = FetchType.EAGER)
Map<String, String> strings = new HashMap<String, String>();
}

And I select all existing entities
String query = "SELECT z FROM " + TestEntity.class.getSimpleName()
+ " z";
List<TestEntity> result = em.createQuery(query, TestEntity.class)
.getResultList();

n + 1 selects are performed which can be seen by using <property name="openjpa.Log" value="DefaultLevel=TRACE" />

917 testPU TRACE [main] openjpa.Query - Executing query: SELECT z FROM TestEntity z
918 testPU TRACE [main] openjpa.jdbc.SQL - <t 4837279, conn 6040101> executing prepstmnt 17507279 SELECT t0.id, t0.name FROM TestEntity t0
918 testPU TRACE [main] openjpa.jdbc.SQL - <t 4837279, conn 6040101> [0 ms] spent
919 testPU TRACE [main] openjpa.jdbc.SQLDiag - load field: 'strings' for oid=entities.TestEntity-1 class entities.TestEntity
919 testPU TRACE [main] openjpa.jdbc.SQL - <t 4837279, conn 6040101> executing prepstmnt 7493991 SELECT t0.KEY0, t0.value FROM TestEntity_strings t0 WHERE t0.TESTENTITY_ID = ? [params=(long) 1]
919 testPU TRACE [main] openjpa.jdbc.SQL - <t 4837279, conn 6040101> [0 ms] spent
920 testPU TRACE [main] openjpa.jdbc.SQLDiag - load field: 'strings' for oid=entities.TestEntity-51 class entities.TestEntity
920 testPU TRACE [main] openjpa.jdbc.SQL - <t 4837279, conn 6040101> executing prepstmnt 7200207 SELECT t0.KEY0, t0.value FROM TestEntity_strings t0 WHERE t0.TESTENTITY_ID = ? [params=(long) 51]

etc.....

It doesn't matter whether or not I use <property name="openjpa.jdbc.EagerFetchMode" value="parallel"/> or <property name="openjpa.jdbc.EagerFetchMode" value="join"/>.

This is extremely inefficient when I wish to load a list of products that have their name set in multiple languages by using a Map from language to String.
As a workaround I can turn the Map into a List and search the List myself for the right entry.

Issue Links

• relates to: OPENJPA-2296 N+1 select problems when eager fetching OneToOne and ManyToOne fields