Navigate back to the homepage

Automate away your N+1 problems with Hibernate Statistics

Mark Brown
November 29th, 2021 · 1 min read

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.

bad time

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 powerul 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 follwing car_garage and car tables which from a one-to-many relationship;

schema

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.

1List<Car> cars = entityManager.createQuery("SELECT c FROM Car c")
2 .setHint("javax.persistence.loadgraph", entityManager.getEntityGraph("Car.garage"))
3 .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?

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

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.

More articles from Mark Brown

Introduction to Indexes, and the Primary Key

Database indexing is a development task. The most important information for indexing is not the storage system, or the configuration of the server - but instead how the application queries the data. This knowledge is not easily obtained from DBAs or external consultants - so it's on us, the developers.

August 18th, 2021 · 4 min read

Fixing the N+1 query problem

In an earlier blog post, I touched upon the cardinal sin of performance issues - N+1 queries. In this blog post I am going to explain through a few examples what the N+1 query problem is, and also suggest how to fix each in your application code.

April 18th, 2021 · 2 min read
© 2020–2021 Mark Brown
Link to $https://twitter.com/marktjbrownLink to $https://github.com/mtjbLink to $https://instagram.com/marktjbrownLink to $https://www.linkedin.com/in/mark-brown-9952b4a8/