The labs on this site need JavaScript to run. The same content is written out below.

Learn › JPA › Proxies and fetching

N+1

Why are many queries sent when you load the order list only once?

You load a list of orders and then take the member name from each order. Depending on when and how the associated member is fetched, the number of queries sent to the DB changes. When one query loads the list and N more queries are then sent to load the associated data, it is called the N+1 selects problem.

Checked against real execution · The number of SQL statements for 112 combinations was checked against Hibernate 7.4.5 and H2

Concepts in this lab

N+1 selects problem

The problem where, after 1 query loads a list, 1 more query runs for each of the N results to load its associated data, so N more queries run in total. As the data grows, the number of queries grows in proportion and performance drops sharply. To solve it, load the associated data together in a single query (fetch join) or load several items in one batch (batch fetching).

Lazy fetching and eager fetching

Settings for when to load an associated entity. Eager fetching (EAGER) loads the associated entities together when the entity is loaded. Lazy fetching (LAZY) puts a proxy object in the place of the associated entity and loads it when its values are actually used. Eager fetching always loads data even when it is not needed and produces queries that are hard to predict, so using lazy fetching as the default is recommended.

Fetch join

A JPQL feature that loads an entity and its associated entities together in one go with a SQL join. Even an association configured for lazy fetching is loaded immediately in this query, which solves the N+1 selects problem. Be careful: if you load several collections together, the number of result rows multiplies.

@EntityGraph

A feature for specifying, with an annotation, which associations to load together in a query. When you put it on a Spring Data JPA repository method, you get the same effect as a fetch join without writing JPQL yourself.

Batch fetching (batch size)

A setting that loads lazy fetching targets several at a time, grouped in an IN condition, instead of one by one. For example, with a size of 100, 100 pieces of associated data are loaded in a single query, so a query that used to run N times runs N/100 times. In Hibernate 7.3 and earlier, where pagination is done in memory when you fetch join a collection, this is the solution that lets you use collection associations together with pagination (from 7.4, the combination of fetch join and pagination is also handled in the database).

First-level cache

The store inside the persistence context that keeps entities by identifier. If you load the same identifier again within the same transaction, it returns the stored entity without sending a query to the database. As a result, entities with the same identifier are always the same object within the same transaction. By default it disappears when the transaction ends (in a web request with Open Session in View (OSIV) turned on, it lasts until the request ends), so it is different from an application-wide cache.

Try these

• Default settings — Lazy fetching, 10 orders, 10 members. 1 + 10 queries are sent.
• Does switching to eager fetching solve it? — The number of queries stays the same, and only the time they are sent moves earlier.
• Fetch join — Fetches the orders and the members in one query.
• Batch size setting — Fetches the members 5 at a time with an IN query.
• When one member has many orders — 10 orders, 3 members. A member that has already been read is not queried again.
• When you do not use the member information — Lazy fetching does not query a member you do not use. Compare it with eager fetching.
• Raise it to 30 orders — With lazy fetching, the queries grow as the data grows.

Running with the default settings

1. Query 1: Fetches the order list (10 orders). The members were not fetched. JPQL becomes SQL that reads only the order table, exactly as written.
2. Query 2: At the moment order 1 takes the name of member 1, a query is sent to fill the empty member object (the proxy).
3. Query 3: At the moment order 2 takes the name of member 2, a query is sent to fill the empty member object (the proxy).
4. Query 4: At the moment order 3 takes the name of member 3, a query is sent to fill the empty member object (the proxy).
5. Query 5: At the moment order 4 takes the name of member 4, a query is sent to fill the empty member object (the proxy).
6. Query 6: At the moment order 5 takes the name of member 5, a query is sent to fill the empty member object (the proxy).
7. Query 7: At the moment order 6 takes the name of member 6, a query is sent to fill the empty member object (the proxy).
8. Query 8: At the moment order 7 takes the name of member 7, a query is sent to fill the empty member object (the proxy).
9. Query 9: At the moment order 8 takes the name of member 8, a query is sent to fill the empty member object (the proxy).
10. Query 10: At the moment order 9 takes the name of member 9, a query is sent to fill the empty member object (the proxy).
11. Query 11: At the moment order 10 takes the name of member 10, a query is sent to fill the empty member object (the proxy).

Result of this run

• On top of the 1 list query, as many queries as members followed. This is the N+1 selects problem. The number of extra queries is decided not by the number of orders but by the number of distinct members that have not been read yet (10).

Code

@Entity class Order {
    @ManyToOne(fetch = FetchType.LAZY)
    Member member;
}

interface OrderRepository {
    @Query("select o from Order o")
    List<Order> findOrders();
}

// Service (inside a transaction)
List<Order> orders = repo.findOrders();   // 10 orders
for (Order o : orders) {
    names.add(o.getMember().getName());  // uses the member name
}

Assumptions of this model

This lab assumes that an order (Order) refers to a member (Member) with @ManyToOne, and that the loop runs while the transaction (persistence context) is open. If you touch a lazily fetched target after the persistence context is closed, the default setting throws a LazyInitializationException instead of sending a query. The number of queries was confirmed by running 112 combinations (5 methods, 1, 10, or 30 orders, 1, 3, 10, or 30 members, batch sizes 2, 5, or 20, and whether the name is used) on real Hibernate 7.4.5 and an H2 database, and the number of SQL statements actually sent matched the screen. @EntityGraph was run with the same JPA entity graph hint that Spring Data uses internally. The 1 + the number of members for eager fetching is the value when there is no batch setting. Batch fetching follows the way used since Hibernate 6, which splits the ids by the batch size; with the default way in Hibernate 5, more queries may be sent. The shape of the SQL depends on the DB: MySQL uses an IN list, and PostgreSQL uses an array parameter (= any(?)). The round-trip time is simplified to be the same for every query. In reality, one join query can be heavier than one simple query.

Previous lab: Proxies and lazy fetching · Next lab: JPQL and fetch joins