Optimize JPA one to many with fetch

 N+1

We all know the famous N+1 problem in the ORM world where the generated sql is not that optimized that we have to get the child collections with N number of separate sql query which leads to serious performance problem if there are many levels of objects tree.

So we can fix the problem in different ways

Let’s say we have an Parent object with a Set of Child objects.

1. Use JPA criteria fetch call

We can explicitly tell the criteria to do fetch instead of having separate query:

    public List<Parent> findAllParent()
    {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Parent> cq = cb.createQuery(Parent.class);
        Root<Parent> root = cq.from(Parent.class);
        root.fetch(Parent_.Child, JoinType.INNER);
        cq.distinct(true);
        return entityManager.createQuery(cq).getResultList();
    }

Note the distinct(true) is important here otherwise you get a lot of duplicate rows. reference HERE.

2. Use @Fetch(SUBSELECT or JOIN)

JPA doesn’t provide any specification on mapping annotations to select fetch strategy. In general, related entities can be fetched in any one of the ways given below

  • SELECT => one query for root entities + one query for related mapped entity/collection of each root entity = (n+1) queries, this is the DEFAULT
  • SUBSELECT => one query for root entities + second query for related mapped entity/collection of all root entities retrieved in first query = 2 queries
  • JOIN => one query to fetch both root entities and all of their mapped entity/collection = 1 query

So SELECT and JOIN are two extremes and SUBSELECT falls in between. One can choose suitable strategy based on her/his domain model.

By default SELECT is used by both JPA/EclipseLink and Hibernate. This can be overridden by using

@Fetch(FetchMode.JOIN)
@Fetch(FetchMode.SUBSELECT)

3. User @Fetch(SELECT) + @BatchSize

in Hibernate. It also allows to set SELECT mode explicitly using @Fetch(FetchMode.SELECT)which can be tuned by using batch size e.g. @BatchSize(size=10)

Corresponding annotations in EclipseLink are

@JoinFetch
@BatchFetch
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s