[hibernate-issues] [JIRA] (HHH-16469) Hibernate repeatedly issues identical SELECT queries to load an optional one-to-one association

Andrea Boriero ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZTE0OGEyMmYw... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16469?atlOrigin=eyJpIjoiZTE0OG... ) HHH-16469 ( https://hibernate.atlassian.net/browse/HHH-16469?atlOrigin=eyJpIjoiZTE0OG... ) Hibernate repeatedly issues identical SELECT queries to load an optional one-to-one association ( https://hibernate.atlassian.net/browse/HHH-16469?atlOrigin=eyJpIjoiZTE0OG... ) Change By: Andrea Boriero ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *Object models: An Agency contains many users. Each user belongs to 0 or more groups. An Agency can optionally have detail information. *Database tables: AGENCY: AGENCY_ID integer not null primary key, AGENCY_NAME varchar(1024) not null AGENCY_DETAIL: AGENCY_ID integer not null primary key, AGENCY_DETAIL varchar(1024) null USER: USER_ID integer not null primary key, USER_NAME varchar(256) not null, AGENCY_ID integer not null GROUP: GROUP_ID integer not null primary key, GROUP_NAME varchar(256) not null, AGENCY_ID integer not null GROUP_USER: GROUP_ID integer not null, USER_ID integer not null, composite key (GROUP_ID, USER_ID) *Entity Beans #Agency.java -- { { noformat} @Entity }} {{ @Table(name = "AGENCY", schema = "TEST") }} {{ public class Agency { }} {{ private Integer agencyId; }} {{ private String agencyName; }} {{ private AgencyDetail agencyDetail; }} {{ private Set<User> users; }} {{ private Set<Group> groups; }} {{ @Id }} {{ @Column(name = "AGENCY_ID") }} {{ public Integer getAgencyId() { }} {{ return agencyId; }} {{ } }} {{ public void setAgencyId(Integer agencyId) { }} {{ this.agencyId = agencyId; }} {{ } }} {{ @Column(name = "AGENCY_TXT") }} {{ public String getAgencyName() { }} {{ return agencyName; }} {{ } }} {{ public void setAgencyName(String agencyName) { }} {{ this.agencyName = agencyName; }} {{ } }} {{ @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER) }} {{ @PrimaryKeyJoinColumn(name = "AGENCY_ID") }} {{ public AgencyDetail getAgencyDetail() { }} {{ return agencyDetail; }} {{ } }} {{ public void setAgencyDetail(AgencyDetail agencyDetail) { }} {{ this.agencyDetail = agencyDetail; }} {{ } }} {{ @OneToMany( }} {{ cascade = CascadeType.ALL, }} {{ orphanRemoval = true, }} {{ fetch = FetchType.EAGER, }} {{ mappedBy = "agency") }} {{ @Fetch(FetchMode.SELECT) }} {{ public Set<User> getUsers() { }} {{ return users; }} {{ } }} {{ public void setUsers(Set<User> users) { }} {{ this.users = users; }} {{ } }} {{ @OneToMany( }} {{ cascade = CascadeType.ALL, }} {{ orphanRemoval = true, }} {{ fetch = FetchType.EAGER, }} {{ mappedBy = "agency") }} {{ @Fetch(FetchMode.SELECT) }} {{ public Set<Group> getGroups() { }} {{ return groups; }} {{ } }} {{ public void setGroups(Set<Group> groups) { }} {{ this.groups = groups; }} {{ } }} } { { noformat } }} #AgencyDetail.java -- { { noformat} @Entity }} {{ @Table(name = "AGENCY_DETAIL", schema = "TEST") }} {{ public class AgencyDetail { }} {{ private Integer agencyId = null; }} {{ private String agencyDetail = null; }} {{ @Id }} {{ @Column(name = "AGENCY_ID") }} {{ public Integer getAgencyId() { }} {{ return agencyId; }} {{ } }} {{ public void setAgencyId(Integer agencyId) { }} {{ this.agencyId = agencyId; }} {{ } }} {{ @Column(name = "AGENCY_DETAIL") }} {{ public String getAgencyDetail() { }} {{ return agencyDetail; }} {{ } }} {{ public void set AgencyDetail(String agencyDetail) { }} {{ agencyDetail = agencyDetail; }} {{ } }} } { { noformat } }} #User.java {noformat} @Entity @Table(name = "USER", schema = "TEST") public class User { {noformat} private Integer userId; private String userName; private Agency agency; @Id @Column(name = "USER_ID") public Integer getUserId() { return userId; } public void setUserId(Integer userId) { userId = userId; } @Column(name = "USER_NAME") public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } @ManyToOne @JoinColumn(name = "AGENCY_ID") public Agency getAgency() { return agency; } public void setAgency(Agency agency) { this.agency = agency; } } {noformat} } #Group.java { { noformat} @Entity }} {{ @Table(name = "GROUP", schema = "TEST") }} {{ public class Group { }} {{ private Integer groupId; }} {{ private String name; }} {{ private Agency agency; }} {{ private Set<User> users; }} {{ @Id }} {{ @Column(name = "GROUP_ID") }} {{ public Integer getGroupId() { }} {{ return groupId; }} {{ } }} {{ public void setGroupId(Integer groupId) { }} {{ this.groupId = groupId; }} {{ } }} {{ @Column(name = "GROUP_NAME", nullable = false, unique = false) }} {{ public String getName() { }} {{ return name; }} {{ } }} {{ public void setName(String name) { }} {{ this.name = name; }} {{ } }} {{ @ManyToMany(cascade = CascadeType.PERSIST, fetch = FetchType.EAGER) }} {{ @JoinTable( }} {{ name = "GROUP_USER", }} {{ schema = "CRISOP", }} {{ joinColumns = @JoinColumn(name = "GROUP_ID"), }} {{ inverseJoinColumns = @JoinColumn(name = "USER_ID")) }} {{ @Fetch(FetchMode.SUBSELECT) }} {{ public Set<User> getUsers() { }} {{ return users; }} {{ } }} {{ public void setUsers(Set<User> users) { }} {{ this.users = users; }} {{ } }} {{ @ManyToOne }} {{ @JoinColumn(name = "AGENCY_ID") }} {{ public Agency getAgency() { }} {{ return agency; }} {{ } }} {{ public void setAgency(Agency agency) { }} {{ this.agency = agency; }} {{ } }} } { { noformat } }} *Database table contents INSERT INTO TEST.AGENCY VALUES(1, ‘TEST AGENCY’); INSERT INTO TEST.USER VALUES (1, 'TEST USER 1); INSERT INTO TEST.USER VALUES (2, 'TEST USER 2); INSERT INTO TEST.USER VALUES (3, 'TEST USER 3); INSERT INTO TEST.USER VALUES (4, 'TEST USER 4); INSERT INTO TEST.USER VALUES (5, 'TEST USER 5); INSERT INTO TEST.USER VALUES (6, 'TEST USER 6); INSERT INTO TEST.USER VALUES (7, 'TEST USER 7); INSERT INTO TEST.USER VALUES (8, 'TEST USER 8); INSERT INTO TEST.GROUP VALUES(1, ‘TEST GROUP 1’); INSERT INTO TEST.GROUP_USER VALUES (1,1); INSERT INTO TEST.GROUP_USER VALUES (1,2); INSERT INTO TEST.GROUP_USER VALUES (1,3); INSERT INTO TEST.GROUP_USER VALUES (1,4); *Test code Agency agency = session.get(Agency.class, 1); * Problems load the Agency object from DB using the above test code generates 4 identical queries -- ~[04/13/23 14:53:43.425] DEBUG : main : SQL - select t1_0.AGENCY_ID,t1_0.AGENCY_DETAIL from TEST.AGENCY_DETAIL t1_0 where t1_0.AGENCY_ID=?~ ~[04/13/23 14:53:43.425] TRACE : main : bind - binding parameter [1] as [INTEGER] - [1]~ ~[04/13/23 14:53:43.514] DEBUG : main : SQL - select t1_0.AGENCY_ID,t1_0.AGENCY_DETAIL from TEST.AGENCY_DETAIL t1_0 where t1_0.AGENCY_ID=?~ ~[04/13/23 14:53:43.515] TRACE : main : bind - binding parameter [1] as [INTEGER] - [1]~ ~[04/13/23 14:53:43.601] DEBUG : main : SQL - select t1_0.AGENCY_ID,t1_0.AGENCY_DETAIL from TEST.AGENCY_DETAIL t1_0 where t1_0.AGENCY_ID=?~ ~[04/13/23 14:53:43.601] TRACE : main : bind - binding parameter [1] as [INTEGER] - [1]~ ~[04/13/23 14:53:43.692] DEBUG : main : SQL - select t1_0.AGENCY_ID,t1_0.AGENCY_DETAIL from TEST.AGENCY_DETAIL t1_0 where t1_0.AGENCY_ID=?~ ~[04/13/23 14:53:43.692] TRACE : main : bind - binding parameter [1] as [INTEGER] - [1]~ If I add four more rows into the GROUP_USER table: INSERT INTO TEST.GROUP_USER VALUES (1,5); INSERT INTO TEST.GROUP_USER VALUES (1,6); INSERT INTO TEST.GROUP_USER VALUES (1,7); INSERT INTO TEST.GROUP_USER VALUES (1,8); then loading the Agency object from DB using the test code generates 8 identical queries to SELECT from the AGENCY_DETAIL table. In our project we have an agency with  5000+ users and 100+ groups, and thousands of rows in the GROUP_USER table. It took minutes to load the agency because the same SELECT from the AGENCY_DETAIL table query was issued thousands of times. If I insert a row into the AGENCY_DETAIL table for agency with id 1 -- INSERT INTO TEST.AGENCY_DETAIL VALUES (1, ‘DETAIL’); then loading the Agency object using the test code finished quickly, and there is no repeatedly identical SELECT from AGENCY_DETAIL query. ( https://hibernate.atlassian.net/browse/HHH-16469#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16469#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100222- sha1:cb3bdf0 )