JPA N+1 query visualizer
Load some orders and walk their items. Switch between lazy, join fetch, entity graph, batch size and subselect, and see exactly how many SQL statements Hibernate sends and what they look like.
Statements sent, in order
Explain what I am seeing
Statements for each strategy (same data)
The Java behind it
Why N+1 happens, and why it hides
The model here is the classic one: an Order has many OrderItem rows, and each item points at a Product. Both associations are lazy. findAll() runs a single query for the orders. The moment your code calls order.getItems() inside a loop, Hibernate initialises the collection of that order with its own query. With 5 orders you get 6 queries, with 500 orders you get 501. Nothing in the code looks wrong, which is why the problem appears only when the data grows or when a dashboard gets slow.
The five strategies
- Lazy is the JPA default for collections. It is cheap when you do not touch the collection and expensive when you loop over it.
- join fetch loads orders and items in one joined statement. It fixes the collection, but a lazy
item.productstill costs one query per distinct product, so the nested N+1 remains until you fetch that too. - Entity graph expresses the same idea as an annotation, and its attribute paths can reach nested associations such as
items.product. - @BatchSize keeps lazy loading but groups the work into IN queries, so statements drop to about N divided by the batch size. Because it does not join, it is safe with pagination.
- Subselect loads the collections of all parents from the original query with one extra statement.
First-level cache details
Within one persistence context each entity is loaded once. If three items point at the same product, the product is selected a single time, which is why the simulator counts distinct products rather than items. Statement counts also assume that the second-level cache and query cache are off.
Learn the concepts
Lazy loading and N+1 ·JPA entities ·Persistence context ·Spring Data repositories ·@Transactional ·DDL to JPA entity
Statement counts follow Hibernate's documented behaviour for these mappings. Exact SQL text varies with dialect, aliases and column lists.
Frequently asked questions
What is the N+1 query problem in JPA and Hibernate?
You run one query to load N parent rows, then touch a lazy association on each parent, which fires one more query per parent. The result is N+1 round trips instead of one or two. It is invisible in the code because the extra queries are triggered by calling a getter.
Is JOIN FETCH always the right fix?
No. It gives one statement, but each parent row is repeated for every child, which inflates the result. Fetching a collection together with pagination makes Hibernate paginate in memory and log HHH90003004. Fetching two collections at once can produce a cartesian product or a MultipleBagFetchException.
What is the difference between JOIN FETCH and an entity graph?
They produce similar SQL. A fetch join is written into the JPQL string. An entity graph (@EntityGraph on a repository method, or a fetch graph hint) is declared separately, so the same query can be used with different loading plans, and it accepts nested paths such as items.product.
How does @BatchSize reduce queries?
When a lazy collection is first touched, Hibernate loads the collections of up to batch-size parents with one IN query instead of one query per parent. 40 orders with a batch size of 10 need 4 collection queries instead of 40. It works with pagination.
How do I find N+1 in a real application?
Turn on Hibernate statistics (hibernate.generate_statistics) or use a datasource proxy to count statements per request, then assert the count in an integration test. Many teams fail the build when a request exceeds a statement budget.