Navigate back to the homepage

Fixing the N+1 query problem

Mark Brown
April 18th, 2021 · 2 min read

👋 Introduction

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.

The N+1 query problem is sometimes hard to diagnose from reading your code - which is another reason why you should check the generated sql from your application. With the N+1 problem, you will often find that the issue is not the performance of the query generated, but instead the sum of number of executions and that query time. Therefore, the larger the value of N, the more queries that will be executed, and the larger the performance impact. Unless you test your application at scale, you may find that you do not flush out some of these issues until much later in the development cycle..

🧐 Examples

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

f

FetchType.EAGER

Using FetchType.EAGER is generally best avoided because you are going to fetch more data than is required, but also because this fetch type is prone to N+1 issues. The good news, however, this is not always used by default but if your entity relationship is using @ManyToOne or @OneToOne then unfortunately this is used the default value.

So if your mappings look like this;

1@ManyToOne
2@JoinColumn(name = "garage_id", nullable = false)
3private CarGarage garage;

Then you are using the FetchType.EAGER strategy, and every time you fetch the car entity;

1@Query("SELECT c FROM Car c")
2List<Car> findAllCars();

Then you are going to trigger the N+1 issue;

1select car0_.id as id1_0_,
2 car0_.garage_id as garage_i5_0_,
3 car0_.make as make2_0_,
4 car0_.model as model3_0_,
5 car0_.registration as registra4_0_
6from car car0_
7
8select cargarage0_.id as id1_1_0_, cargarage0_.name as name2_1_0_ from car_garage cargarage0_ where cargarage0_.id=?
9select cargarage0_.id as id1_1_0_, cargarage0_.name as name2_1_0_ from car_garage cargarage0_ where cargarage0_.id=?
10select cargarage0_.id as id1_1_0_, cargarage0_.name as name2_1_0_ from car_garage cargarage0_ where cargarage0_.id=?
11select cargarage0_.id as id1_1_0_, cargarage0_.name as name2_1_0_ from car_garage cargarage0_ where cargarage0_.id=?

Notice how the additional SELECT statements are executed too because the car_garage association has to be fetched after loading the list of car entities.

FetchType.LAZY

Almost contradictory to the above example, switching to FetchType.LAZY can also cause the N+1 query problem, for example if switching the CarGarage association to;

1@ManyToOne(fetch = FetchType.LAZY)
2@JoinColumn(name = "garage_id", nullable = false)
3private CarGarage garage;

Fetching a list of car entities will now only execute one sql statement, but when referencing the lazy-loaded association;

1List<Car> cars = (List<Car>) carRepository.findAllCars();
2Set<String> garageNames = cars.stream()
3 .map(Car::getGarage)
4 .map(CarGarage::getName)
5 .collect(Collectors.toSet());

You will again trigger the N+1 query problem;

1select cargarage0_.id as id1_1_0_, cargarage0_.name as name2_1_0_ from car_garage cargarage0_ where cargarage0_.id=?
2select cargarage0_.id as id1_1_0_, cargarage0_.name as name2_1_0_ from car_garage cargarage0_ where cargarage0_.id=?
3select cargarage0_.id as id1_1_0_, cargarage0_.name as name2_1_0_ from car_garage cargarage0_ where cargarage0_.id=?
4select cargarage0_.id as id1_1_0_, cargarage0_.name as name2_1_0_ from car_garage cargarage0_ where cargarage0_.id=?

🛠 Fixing the N+1 issue

The first step you should take to fix the N+1 issue should be to ask yourself; “Do I even need to load this data?”

If the answer is no, you should ensure that your @ManyToOne (or @OneToMany) association has its fetchType set to FetchType.LAZY - in fact, it is better to use this by default to avoid the N+1 issue creeping in across all of your entity relationships.

If you do, there are a few ways of solving this, and it depends on a number of things - including inspecting the execution plan used for any of these options and choosing the one that is most optimal for your example, while also weighing up the pros and cons of each solution.

Using JOIN FETCH

By using JOIN FETCH we can force the generated query to use an INNER JOIN to load the car_garage rows from the database, and therefore eradicating the N+1 issue;

1@Query("SELECT c FROM Car c JOIN FETCH c.garage")
2List<Car> findAllCars();

Will generate the follow query;

1select car0_.id as id1_0_0_,
2 cargarage1_.id as id1_1_1_,
3 car0_.garage_id as garage_i5_0_0_,
4 car0_.make as make2_0_0_,
5 car0_.model as model3_0_0_,
6 car0_.registration as registra4_0_0_,
7 cargarage1_.name as name2_1_1_
8from car car0_
9inner join car_garage cargarage1_ on car0_.garage_id=cargarage1_.id
Entity Graphs

Entity graphs are another way of loading the the garage association and avoiding the N+1 issue, I will cover these in more detail in a later post.

By specifying a new entity graph for the garage relationship.

1@NamedEntityGraph(
2 name = "Car.garage",
3 attributeNodes = { @NamedAttributeNode(value = "garage") }
4)
5public class Car {
6
7@ManyToOne(fetch = FetchType.LAZY)
8@JoinColumn(name = "garage_id", nullable = false)
9private CarGarage garage;
10
11// ETC

And updating the query to make use of this entity graph.

1@EntityGraph(value = "Car.garage", type = EntityGraph.EntityGraphType.LOAD)
2List<Car> findAll();

Then the following query is generated;

1select car0_.id as id1_0_0_,
2 cargarage1_.id as id1_1_1_,
3 car0_.garage_id as garage_i5_0_0_,
4 car0_.make as make2_0_0_,
5 car0_.model as model3_0_0_,
6 car0_.registration as registra4_0_0_,
7 cargarage1_.name as name2_1_1_
8from car car0_
9left outer join car_garage cargarage1_ on car0_.garage_id=cargarage1_.id

⚡️ Conclusion

The N+1 can be subtle, and often undiagnosed for some time - it is best to do your best to avoid it by always defaulting to FetchType.LAZY in your entity relationships, this way you have to adjust your code to fetch the extra information - rather than always having it there when you don’t need it to be!

More articles from Mark Brown

Why (and how) I moved to GatsbyJS

Honestly - the main reason I chose to move away from Wordpress and to GatsbyJS wasn't to upskill in the No. 2 Web Framework from 2020 (according to stackoverflow - but just because.. I liked it better.

April 5th, 2021 · 3 min read

How to introduce a Cartesian Product into your generated queries

When working with an ORM, it is essential that you inspect your generated queries. Often when doing so you will find they are not entirely what you expected. The CriteriaBuilder API can be very useful for building dynamic queries, but you must take great care – especially if you let your UI generate many _wonderful_ queries to fulfil all of its filtering/sorting/etc.

February 24th, 2021 · 1 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/