JPA Performance When Using ONE TO MANY with More Than One Table (Doc ID 1198504.1)

Last updated on NOVEMBER 09, 2011

Applies to:

Oracle Weblogic Server - Version: 10.3 to 10.3.3
Information in this document applies to any platform.

Symptoms

Usecase
Performance issues seen when using tables with one to many relationship like below;

Team --------------------------> Coach, Player, Results
The performance issue is seen when you query find by primary key of 'Team' Entity.

"entityManager.find(Team.class, teamID);"

SQL output in WLS 10.3
===================
1.) SELECT t0.NAME, t1.TEAM_ID_COACH, t1.ID, t1.COACHNAME
FROM SPOLINEN.TEAM t0, SPOLINEN.COACH t1
WHERE t0.ID = ? AND t0.ID = t1.TEAM_ID_COACH(+)
ORDER BY t1.TEAM_ID_COACH ASC, 1003, 1007)>

2.) SELECT t0.ID, t0.NICKNAME FROM SPOLINEN.PLAYER t0 WHERE t0.TEAM_ID = ?,
1003, 1007)>

SELECT t1.ID, t1.NAME FROM SPOLINEN.PLAYER t0, SPOLINEN.TEAM t1 WHERE t0.ID
= ? AND t0.TEAM_ID = t1.ID, 1003, 1007)>
SELECT t1.ID, t1.NAME FROM SPOLINEN.PLAYER t0, SPOLINEN.TEAM t1 WHERE t0.ID
= ? AND t0.TEAM_ID = t1.ID, 1003, 1007)>
SELECT t1.ID, t1.NAME FROM SPOLINEN.PLAYER t0, SPOLINEN.TEAM t1 WHERE t0.ID
= ? AND t0.TEAM_ID = t1.ID, 1003, 1007)>
SELECT t1.ID, t1.NAME FROM SPOLINEN.PLAYER t0, SPOLINEN.TEAM t1 WHERE t0.ID
= ? AND t0.TEAM_ID = t1.ID, 1003, 1007)>
SELECT t1.ID, t1.NAME FROM SPOLINEN.PLAYER t0, SPOLINEN.TEAM t1 WHERE t0.ID
= ? AND t0.TEAM_ID = t1.ID, 1003, 1007)>
SELECT t1.ID, t1.NAME FROM SPOLINEN.PLAYER t0, SPOLINEN.TEAM t1 WHERE t0.ID
= ? AND t0.TEAM_ID = t1.ID, 1003, 1007)>
SELECT t1.ID, t1.NAME FROM SPOLINEN.PLAYER t0, SPOLINEN.TEAM t1 WHERE t0.ID
= ? AND t0.TEAM_ID = t1.ID, 1003, 1007)>

3.) SELECT t0.ID, t0.RESULT, t0.YEAR FROM SPOLINEN.WORLDCUP_RESULTS t0 WHERE
t0.WORLDCUP_RESULTS_ID = ?, 1003, 1007)>

SELECT t1.ID, t1.NAME FROM SPOLINEN.WORLDCUP_RESULTS t0, SPOLINEN.TEAM t1
WHERE t0.ID = ? AND t0.WORLDCUP_RESULTS_ID = t1.ID, 1003, 1007)>
SELECT t1.ID, t1.NAME FROM SPOLINEN.WORLDCUP_RESULTS t0, SPOLINEN.TEAM t1
WHERE t0.ID = ? AND t0.WORLDCUP_RESULTS_ID = t1.ID, 1003, 1007)>
SELECT t1.ID, t1.NAME FROM SPOLINEN.WORLDCUP_RESULTS t0, SPOLINEN.TEAM t1
WHERE t0.ID = ? AND t0.WORLDCUP_RESULTS_ID = t1.ID, 1003, 1007)>
SELECT t1.ID, t1.NAME FROM SPOLINEN.WORLDCUP_RESULTS t0, SPOLINEN.TEAM t1
WHERE t0.ID = ? AND t0.WORLDCUP_RESULTS_ID = t1.ID, 1003, 1007)>
SELECT t1.ID, t1.NAME FROM SPOLINEN.WORLDCUP_RESULTS t0, SPOLINEN.TEAM t1
WHERE t0.ID = ? AND t0.WORLDCUP_RESULTS_ID = t1.ID, 1003, 1007)>

The same usecase on WLS 10.0 gives the below SQL output:
============================================
1.)SELECT t0.NAME, t1.TEAM_ID_COACH, t1.ID, t1.COACHNAME, t2.ID, t2.NAME
FROM SPOLINEN.TEAM t0, SPOLINEN.COACH t1, SPOLINEN.TEAM t2 WHERE t0.ID = ?
AND t0.ID = t1.TEAM_ID_COACH(+) AND t1.TEAM_ID_COACH = t2.ID(+) ORDER BY
t1.TEAM_ID_COACH ASC)>
2.)SELECT t0.ID, t0.NICKNAME, t1.ID, t1.NAME FROM SPOLINEN.PLAYER t0,
SPOLINEN.TEAM t1 WHERE t0.TEAM_ID = ? AND t0.TEAM_ID = t1.ID(+))>
3.)SELECT t0.ID, t0.RESULT, t1.ID, t1.NAME, t0.YEAR FROM
SPOLINEN.WORLDCUP_RESULTS t0, SPOLINEN.TEAM t1 WHERE t0.WORLDCUP_RESULTS_ID
= ? AND t0.WORLDCUP_RESULTS_ID = t1.ID(+))>

So in WLS 10.3 the SQL entries are not joining instead it increases the number of queries.

Changes

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms