DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Fixing Common Oracle Database Problems
  • How to Restore a Transaction Log Backup in SQL Server
  • Why I Built the Ultimate Text Comparison Tool (And Why You Should Try It)
  • Enhancing Avro With Semantic Metadata Using Logical Types

Trending

  • How to Practice TDD With Kotlin
  • Scalability 101: How to Build, Measure, and Improve It
  • Setting Up Data Pipelines With Snowflake Dynamic Tables
  • Scaling in Practice: Caching and Rate-Limiting With Redis and Next.js
  1. DZone
  2. Data Engineering
  3. Databases
  4. Hibernate/GORM: Solving the N+1 Problem

Hibernate/GORM: Solving the N+1 Problem

Tired of running into the N+1 wall? Let's see how Hibernate and Groovy's GORM can work together to form the ultimate solution.

By 
Alex Azarov user avatar
Alex Azarov
·
Updated Feb. 19, 18 · Tutorial
Likes (6)
Comment
Save
Tweet
Share
19.0K Views

Join the DZone community and get the full member experience.

Join For Free

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)".

WaveAccess_news-comment

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.

WaveAccess_Gorm_1

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:

WaveAccess_Gorm_2

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.

WaveAccess_Gorm_3

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.

Database

Published at DZone with permission of Alex Azarov. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Fixing Common Oracle Database Problems
  • How to Restore a Transaction Log Backup in SQL Server
  • Why I Built the Ultimate Text Comparison Tool (And Why You Should Try It)
  • Enhancing Avro With Semantic Metadata Using Logical Types

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!