My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.