The N+1 query problem is said to occur when an ORM, like hibernate, executes 1 query to retrieve the parent entity and N queries to retrieve the child entities. As the number of entities in the database increases, the queries being executed separately can easily affect the performance of the application. This article will demonstrate how N+1 queries occur and their solution through an example in spring boot.
The N+1 Query Problem
Consider a Content Management System that stores a list of articles per publication. A publication can have a category and list of articles associated with it. Below is a simplified entity for Publication and Article.
A Publication has a one-to-many relation with an Article entity. Assume the database currently has the following data, with publication_1 having two articles and publication_2 having one article.
Now, when we try to fetch all the publications that come under a certain category, say technology, using findByCategory(‘technology’), a SELECT query is first executed to fetch records from the Publication table.
The above query returns two records with ids ‘publication_1’ and ‘publication_2’. Now for each of these publications, you need to fetch corresponding articles from the Article table and JPA internally generates two more SELECT queries:
As you can see here, after the first query for fetching data from the publication table, two extra queries were generated (N=2), to fetch the related articles from the child table. Hence for fetching the data, N+1 queries are generated, where N is the number of entities in parent table.
JPA EntityGraphs
EntityGraphs provides a way to formulate better performing queries by defining which entities need to be retrieved from the database using SQL JOINS.
There are two types of entityGraphs, Fetch and Load, which defines if the entities not specified by attributeNodes of entityGraphs should be fetched lazily or eagerly. Attributes specified by attributeNodes of entityGraph are always fetched eagerly.
FETCH TYPE: Attributes that are specified by attributeNodes of entityGraph are treated as FetchType.EAGER and rest of the attributes are treated as FetchType.Lazy.
LOAD TYPE: Attributes that are specified by attributeNodes of entityGraph are treated as FetchType.EAGER and rest of the attributes are treated according to their specified or default fetchTypes.
EntityGraphs can be defined in two ways:
1. Using NamedEntityGraph Annotation
To use a NamedEntityGraph, first annotate the entity class Publication with JPA’s @NamedEntityGraph annotation , and then attach the @EntityGraph annotation to the repository method, with the name of the graph.
2. Without NamedEntityGraph Annotation
You can also define an ad-hoc EntityGraph, using attributePaths, without using NamedEntityGraph annotation on the entity. AttributePaths should include the names of the entities to be fetched eagerly.
Ad-hoc EntityGraphs are more dynamic and are used for a single or specific use-case. NamedEntityGraphs are useful over ad-hoc ones, when there are multiple usages of queries on the same entity. The attributes to be fetched via JOIN on each query can be specified just once in the NamedEntityGraph for all queries. Query generated by JPA after using EntityGraphs:
Thus, N+1 queries was reduced to only a single query to fetch data from both the tables, using JOIN. EntityGraphs provide a mechanism by which entities can be fetched eagerly from the database in a single select statement, helping in improving the performance of the application. You can also use Subgraphs to define the entities for the child class, that needs to be fetched eagerly along with the parent class.