[JIRA] (HHH-16469) Hibernate repeatedly issues identical SELECT queries to load an optional one-to-one association
by Puhong You (JIRA)
Puhong You ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZTk2MTc0NmY0... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16469?atlOrigin=eyJpIjoiZTk2MT... ) HHH-16469 ( https://hibernate.atlassian.net/browse/HHH-16469?atlOrigin=eyJpIjoiZTk2MT... ) Hibernate repeatedly issues identical SELECT queries to load an optional one-to-one association ( https://hibernate.atlassian.net/browse/HHH-16469?atlOrigin=eyJpIjoiZTk2MT... )
Issue Type: Bug Affects Versions: 6.2.0 Assignee: Unassigned Components: hibernate-core Created: 13/Apr/2023 20:10 PM Environment: Priority: Major Reporter: Puhong You ( 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 –
@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;}}
{{ }}}
}
#AgencyDetail.java –
@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;}}
{{ }}}
}
#User.java
@Entity
@Table(name = "USER", schema = "TEST")
public class User {
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;
}
}
#Group.java
@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;}}
{{ }}}
}
*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=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100221- sha1:5609d8c )
2 years, 12 months