EntityGraph Cartesian Product Issue

Description

When loading data using an EntityGraph there is no ability to specify the FetchMode and FetchMode.JOIN is used in all scenarios. If an entity has multiple OneToMany relationships this leads to an SQL Query which generates a Cartesian product of the child entities.

Example:
(Using Spring data jpa @EntityGraph annotation)

Member.java

MemberRepository.java

If all members had approximately 5 contacts, 3 addresses, 10 customFields and 15 invoices this leads to a SQL query which returns 5 x 3 x 10 x 15 = 2250 rows to load a single member!

If there was a way to specify the FetchMode dynamically using the Criteria api or similar this would potentially solve the issue. FetchMode.SUBSELECT appears to be the ideal solution here, where a new query would be generated to load ALL the entities of a particular type from the origin point, even when loading a collection.

Example:

MemberRepository.java

1 query for all members
1 query for all addresses for all members
1 query for all contacts for all members
1 query for all customFields for all members
1 query for all invoices for all members

This leads to a fixed number of queries (avoiding the N+1 issue) and no duplicate data being loaded.

However, looking through the source - AbstractEntityGraphVisitationStrategy.class and FetchStyleLoadPlanBuildingAssociationVisitationStrategy.class there appears to be code designed to detect multiple collections and adjust the strategy if needed but it is either not working or incomplete.

Line 247 - 264:

AbstractEntityGraphVisitationStrategy.java

Environment

None

Assignee

Unassigned

Reporter

Nathan Stanley

Fix versions

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Feedback Requested

2019/01/23

Feedback Requested By

Gail Badner

Components

Affects versions

Priority

Major
Configure