Speeding up a n+1 hibernate/jpa query where the sql call is fast but loading is slow

Question

I have a performance issue when I'm loading entities that I'm looking at how to speed up. So I have the following classes:

@MappedSuperClass
public abstract class MySuperList {
...

   @OneToMany(... fetch = fetchType.LAZY)
   private Set<Attributes> myAttributes;

   @OneToMany(... fetch = fetchType.LAZY)
   private Set<Tag> myTags;

...
}

public class MyList extends MySuperList{
...

   @OneToMany(... fetch = fetchType.LAZY)
   private Set<MetadataItems> myMetadataItems;

...
}

When I load several of these items using a similar code below:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<MyList> cq = cb.createQuery(MyList.class);
Root<MyList> myList = cq.from(MyList.class);
cq.select(myList).distinct(true).where(buildPredicate()).orderBy(buildOrder());
List<MyList> result = cb.createQuery(cq).setFirstResult(1).setMaxResult(50).setHint(QueryHints.PASS_DISTINCT_THROUGH, false).getResultList();

Then I see 151 calls to the database:

• One to get the MyList records - 50 records are returned.
• 50 calls to load the Attributes for the 50 MyList records returned.
• 50 calls to load the Tags for the 50 MyList records returned.
• 50 calls to load the MetadataItems for the 50 MyList records returned.

This seems to be a normal n+1 issue. It is quite slow so I decide to eagerly load using the fetch operation prior to the get result list:

myList.fetch("myAttributes", JoinType.LEFT)
myList.fetch("myTags", JoinType.LEFT)
myList.fetch("myMetadataItems", JoinType.LEFT)

This then does a Cartesian product and returns everything in the SQL call and loads all entities and the child entities. This is slightly faster to the n+1 calls but is still slow.

The SQL itself, when run manually, is fast.

So I think what if I load each of the children manually (I've tried both JPA query and native query) i.e. I don't fetch the children but I build 3 separate "criteria builders" that load the Attribute, Tag, MyMetadataItem using the MyList ids returned using the IN operator. So something like this:

• SELECT * FROM Attribute WHERE MyListId IN ( <the 50 MyListIds> )
• SELECT * FROM Tag WHERE MyListId IN ( <the 50 MyListIds> )
• SELECT * FROM MyMetadataItem WHERE MyListId IN ( <the 50 MyListIds> )

So I find the initial load of MyList quite speedy and so is the loading of the Attribute, Tag and MyMetadataItem...

but unfortunately when I access a MyList record then once again it lazy loads the Attribute, Tag and MyMetadataItem. I was hoping that if I load the children entities manually that it would be in the Hibernate context and would magically link the MyList record to the children records (and vice versa).

I've even tried to manually "link" it. I.e. I loop through the MyList and call setMyAttribute() with the Attribute records returned that are specific to the MyList record but unfortunately still the same issue.

So I guess my questions is:

1. When we have a n+1 issue then I guess the best thing to do is eagerly load the children?
2. If I load children items manually then is there a way to link it to the parent somehow?
3. Is the above approach even right! :) or is there a better way to speed things up?

Java 1.8 Hibernate 5.4.15 Oracle database. ojdbc 18.3

Any words of wisdom would be greatly appreciated.

Thanks.

Answer 1

Thanks to Chris's comment I found the @BatchSize annotation in Hibernate. This batches the fetching of the child entities and has helped to speed up the calls.