Hibernate/GORM: Solving the N+1 Problem

Many developers who work with Hibernate or any other ORM framework eventually run into the so-called N+1 problem.

Our team faced it when we were working on a project using Grails. For its ORM, Grails uses a GORM "under the hood" that contains the same old Hibernate. In case you haven't encountered this problem yet, let’s give you the gist of it. Let's say we have the following perfectly typical scheme: "News - Comment(s)".

There is a "News" item, and it can have several "Comments."

If we need to get the last ten news items with their comments, based on the default settings, we will perform eleven database queries: one to get the news list and one for each news item in order to get its comments.

The ideal situation is one where the database is on the same machine, or at least the same local network, and the number of news items is limited to ten. But more likely, the database will be located on a dedicated server and there will be about 50 or so more news items on the page. This can lead to an issue with the server’s performance. Several solutions can be found to this problem using Hibernate. Let's take a quick look at them.

FetchMode.JOIN

In the mapping for the association we're interested in, or directly when executing the query, we can set up the JOIN fetch mode. In this case, the necessary association will be received by the same query. This will work for 1-1 or -1 connections, but for 1- queries, we will run into certain problems. Let's take a look at the following query:

The first obvious problem is when limit 10 doesn't work the way we need it to. Instead of returning the first ten news items, this query will return the first ten entries. The number of news items in these ten entries will depend on the number of comments. If the first news item has 10+ comments, it will be the only fetch result we get. All of this forces Hibernate to reject the database's native methods for limiting and offsetting the fetch and process the results on the application server end.

The second problem is less obvious: if we don't make it clear to Hibernate that we only want unique news items, then we're going to get a list of doubled news items (one for each comment). In order to fix this, we need to insert the Result Transformer for the criterion:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Even if we get rid of all these drawbacks, this method still has more serious limitations: for example, it can't cope with the task "also get the article’s author in addition to comments."

FetchMode.SUBSELECT

Another potential alternative is SUBSELECT. Instead of doing a JOIN, it executes an additional query for linked entities while using the original query as SUBSELECT. In the end, we get only two queries rather than eleven: one base query and one query for each association.

This is a great option that will also work if we need to get both comments and authors at the same time. However, it also has some limitations.

First of all, it can only be used during the mapping-description phase by using the annotation @Fetch(FetchMode.SUBSELECT).

Second, we have no way of monitoring the use of this mode (unlike the same JOIN) at the moment the query is executed. We, therefore, have no way of knowing whether this mode is actually being used or not. If another developer changes the mapping, everything could fall apart. For example, the optimization might stop working and the original version with 11 queries could start being used again. If this happens, this connection will be incomprehensible to whoever made the change.

Third (and this is the deciding factor for us), this mode is not supported by a GORM-Grails framework for working with databases built on top of Hibernate.

Follow this link to learn more about possible fetch strategies.

Given all of these disadvantages, our only remaining option was to arm ourselves with an IDEA and lots of free time, and really dig around in the depths of Hibernate. The result was...

The Ultimate Solution

If we fantasize a little about the perfect solution, the following version suggests itself: make the fetch we need, then load the necessary collections all at once if necessary. It'd look something like this:

Query q = session.createQuery(“from News order by newDate“)
    q.setMaxResults(10)
    List news = q.list()
    BatchCollectionLoader.preloadCollections(session, news, “comments”)

Now let's switch from fantasy to reality. The result of our inquiries was the following Groovy code (it can easily be rewritten in Java if necessary):

package cv.hibernate

import groovy.transform.CompileStatic
import org.grails.datastore.gorm.GormEnhancer
import org.hibernate.HibernateException
import org.hibernate.MappingException
import org.hibernate.QueryException
import org.hibernate.engine.spi.LoadQueryInfluencers
import org.hibernate.engine.spi.SessionFactoryImplementor
import org.hibernate.engine.spi.SessionImplementor
import org.hibernate.loader.collection.BasicCollectionLoader
import org.hibernate.loader.collection.OneToManyLoader
import org.hibernate.persister.collection.QueryableCollection
import org.hibernate.persister.entity.EntityPersister
import org.hibernate.type.CollectionType
import org.hibernate.type.Type

/**
 * Date: 08/03/2017
 * Time: 15:52
 */
@CompileStatic
class BatchCollectionLoader {
    protected static QueryableCollection getQueryableCollection(
        Class entityClass,
        String propertyName,
        SessionFactoryImplementor factory) throws HibernateException {
        String entityName = entityClass.name
        final EntityPersister entityPersister = factory.getEntityPersister(entityName)
        final Type type = entityPersister.getPropertyType(propertyName)
        if (!type.isCollectionType()) {
            throw new MappingException(
                "Property path [" + entityName + "." + propertyName + "] does not reference a collection"
            )
        }

        final String role = ((CollectionType) type).getRole()
        try {
            return (QueryableCollection) factory.getCollectionPersister(role)
        }
        catch (ClassCastException cce) {
            throw new QueryException("collection role is not queryable: " + role, cce)
        }
        catch (Exception e) {
            throw new QueryException("collection role not found: " + role, e)
        }
    }

    private
    static void preloadCollectionsInternal(SessionImplementor session, Class entityClass, List entities, String collectionName) {
        def sf = session.factory
        def collectionPersister = getQueryableCollection(entityClass, collectionName, sf)
        def entityIds = new Serializable[entities.size()]
        int i = 0
        for (def entity : entities) {
            if (entity != null) {
                entityIds[i++] = (Serializable) entity["id"]
            }
        }
        if (i != entities.size()) {
            entityIds = Arrays.copyOf(entityIds, i)
        }
        def loader = collectionPersister.isOneToMany() ?
            new OneToManyLoader(collectionPersister, entityIds.size(), sf, LoadQueryInfluencers.NONE) :
            new BasicCollectionLoader(collectionPersister, entityIds.size(), sf, LoadQueryInfluencers.NONE)
        loader.loadCollectionBatch(session, entityIds, collectionPersister.keyType)
    }

    private static Class getEntityClass(List entities) {
        for (def entity : entities) {
            if (entity != null) {
                return entity.getClass()
            }
        }
        return null
    }

    static void preloadCollections(List entities, String collectionName) {
        Class entityClass = getEntityClass(entities)
        if (entityClass == null) {
            return
        }
        GormEnhancer.findStaticApi(entityClass).withSession { SessionImplementor session ->
            preloadCollectionsInternal(session, entityClass, entities, collectionName)
        }
    }

    static void preloadCollections(SessionImplementor session, List entities, String collectionName) {
        Class entityClass = getEntityClass(entities)
        if (entityClass == null) {
            return
        }
        preloadCollectionsInternal(session, entityClass, entities, collectionName)
    }
}

This class contains two reloaded preloadCollections methods. The first one will only work for GORM (without a session), and the second one will work in both cases.

I hope this article is useful to you and will help you write great code!

P.S. Link to GIST.