Automate away your N+1 problems with Hibernate Statistics

Recap ♻

So what is the N+1 problem? Well, I’ll divert your attention to one of my earlier posts. TL;DR check your generated queries.

BUT, I could have sworn I fixed this?? 🤨

See also: works on my machine

Entity relationships can be tricky to get just right and just like Goldilocks, you don’t want to be sleeping when Papa bear growls “Why is this application so slow??” (or whatever he says). 🧸

Now the good news is - you can write tests to prove your queries don’t suffer the N+1 issue, and don’t start to over time - of course, you’ll have definitely checked this before pushing it the last time… 👀

Hibernate Statistics

Hibernate statistics is a very powerful mechanism that is unfortunately not that well known. The Statistics mechanism is not enabled by default, but many applications can benefit from using them - and in this short example we’ll look at how you can use them to monitor how many queries are being executed on your database.

This API contains a whole host of functions that you can use, but today I’ll be using only 2; getPrepareStatementCount() and getQueryExecutionCount(). Together these can help us achieve the simple ask - I just want to know my single query really is a single query as generated by the ORM (In my case, Spring Framework).

The problem 🤦🏻‍♂️

Consider the following car_garage and car tables which from a one-to-many relationship;

Now, as an avid reader of this blog you’ll know that the best way to map this relationship is with FetchType.LAZY and then by using either JOIN FETCH or a @NamedEntityGraph to pull back the garages a Car is associated it (right?)

But, how do you prove it does what you think it does, and, more importantly - continues to?

The fix 👨🏻‍🔧

In this simple test I am using the Entity Graph I set up for my previous post, but this would work the same if you prefer to use JOIN FETCH as a solution to your problem.

List<Car> cars = entityManager.createQuery("SELECT c FROM Car c")
    .setHint("javax.persistence.loadgraph", entityManager.getEntityGraph("Car.garage"))
    .getResultList() as List<Car>

(Note: how I have given my query the hint to use my Entity Graph - no N+1 here)

Now put it all together and what do you get?

def "Car.garage entity graph executes one query to load all relationships"(String graphType) {
    setup: "2 Car garages, with 3 Cars"
        CarGarage g1 = carGarageService.create(new CarGarageDto(name: "Browns"))
        CarGarage g2 = carGarageService.create(new CarGarageDto(name: "Smyths"))
        carService.create(new CarDto(garage: g1, make: "Ford"))
        carService.create(new CarDto(garage: g2, make: "BMW"))
    and: "Clear statistics"
        Session session = entityManager.unwrap(Session.class)
        Statistics statistics = session.getSessionFactory().getStatistics()
        statistics.setStatisticsEnabled(true)
        statistics.clear()
        assert statistics.getQueryExecutionCount() == 0
    when: "Fetching all Cars using the Car.garage entity graph"
        List<Car> cars = entityManager.createQuery("SELECT c FROM Car c")
                .setHint(graphType, entityManager.getEntityGraph("Car.garage"))
                .getResultList() as List<Car>
    then: "Only 1 query executed"
        statistics.getQueryExecutionCount() == 1
        statistics.getPrepareStatementCount() == 1
    and: "Both cars returned"
        cars.size() == 2
    cleanup:
        statistics.setStatisticsEnabled(false)
    where:
        graphType << ["javax.persistence.loadgraph", "javax.persistence.fetchgraph"]
    }

Cool, right?

Conclusion

In what really is quite a simple test, I’m able to test that the Entity Graph I’ve written does what I want it to do when loading my Car entity. All examples from this post can be found on my GitHub.