OneToOne mapping leading to performance impact due to N+1 select issue

Question

Below are my entities and I am currently facing hibernate n+1 issue with D entity. I am trying the get the details from table A whose response contains more than 500 entries. After that it is querying table D one by one and getting socket timeout as it is taking more than 2 minutes to get all the data. For other tables B and C, it is creating collection wrapper but it is not taking much time. All time is consumed in getting the data for table D.

Data of table D is being eagerly fetched as relation with table D is nullable currently. Also Table A and D has the shared primary key. Only workaround currently for me to reduce the batchsize of response data.

Parent Entity -

@Entity
@Table(name="A")
public class A{
    

    @Id
    private Long id;
    
    @OneToMany(cascade = CascadeType.ALL,mappedBy="bParent")
    @Fetch(FetchMode.SUBSELECT)
    private List<B> b;

    @OneToMany(cascade = CascadeType.ALL,mappedBy="cParent")
    @Fetch(FetchMode.SUBSELECT)
    private List<C> c;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "cParent")
    private D d;
----
}

B child Entity -

@Entity
@Table(name="B")
public class B{
    

    @Id
    private Long bid;

    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name="id")
    protected A bParent;
----
}

C child Entity -

@Entity
@Table(name="C")
public class C{
    

    @Id
    private Long cid;

    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name="id")
    protected A cParent;
----
}

D child Entity -

@Entity
@Table(name="D")
public class D{
    
    @Id
    @GeneratedValue(generator = "generator")
    @Column(name = "id", unique = true, nullable = false)
    private Long id;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @PrimaryKeyJoinColumn
    private A dParent;
----
}

Query I am trying is: findByIdIn(List<Long> ids) Id's list is currently around 500.

Solutions I tried -

1. Tried Join Fetch and Entity graph to resolve N+1 issue but data from table D is fetched fine in very less time but getting getting lazy exception for table B and C by using fetch as lazy otherwise getting - Hibernate throws MultipleBagFetchException - cannot simultaneously fetch multiple bags for fetchtype eager.

2. Also tried ManyToOne rather than OneToOne but it was giving some composite key issue in table D.

3. Also tried below to make onetoone mapping as lazy but it didn't worked.

@LazyToOne(value = LazyToOneOption.NO_PROXY)
OR 
@Fetch(FetchMode.SUBSELECT)

Any ideas on how can I improve the performance either by using Lazy or Eager whichever works.

Answer 1

To improve the performance you should use both collections with EAGER. The real problem is in MultipleBagFetchException, because both collections are logically Bags. To fix this there are two ways:

1. Add @OrderColumn
2. Replace List with Set

More details about the exception are described here: https://www.baeldung.com/java-hibernate-multiplebagfetchexception.