DEV Community

Devanshu Biswas
Devanshu Biswas

Posted on

I Built an N+1 Query Visualizer (JPA/Hibernate's Quietest Performance Killer)

#spring #java #database #backend

The N+1 query problem is the bug that doesn't look like a bug. With 5 rows in dev it's instant; in production with 500 rows it's 501 queries and a page that takes seconds. It never throws, so it hides until it hurts. So I built a visualizer that shows the query log filling one line per row — and then collapsing when you fix it.

▶ Live demo: https://dev48v.github.io/n-plus-one-visualizer/
Source (single file, zero deps): https://github.com/dev48v/n-plus-one-visualizer

What N+1 actually is

Load a list, then touch a lazy association on each element:

List<Order> orders = orderRepo.findAll();          // 1 query
for (Order o : orders) {
    System.out.println(o.getCustomer().getName());  // ← fires a query PER order
}
Enter fullscreen mode Exit fullscreen mode 
select * from orders;                 -- 1
select * from customer where id = ?;  -- then ONE per order → N
Enter fullscreen mode Exit fullscreen mode

That's 1 + N queries for a single page. The demo runs it with a live counter: N=10 → 11 queries, N=100 → 101. Each one is a network round-trip to the database.

The fixes, side by side

  • JOIN FETCH — one query, the association eagerly joined: 
  @Query("select o from Order o join fetch o.customer")
  List<Order> findAllWithCustomer();
Enter fullscreen mode Exit fullscreen mode
  • @EntityGraph — the same single JOIN, declaratively, no JPQL: 
  @EntityGraph(attributePaths = "customer")
  List<Order> findAll();
Enter fullscreen mode Exit fullscreen mode
  • Batch fetching — keep lazy, but group the child selects: 
  @BatchSize(size = 5)   // or hibernate.default_batch_fetch_size=5
Enter fullscreen mode Exit fullscreen mode 
  select * from customer where id in (?,?,?,?,?);  -- ⌈N/5⌉ batched selects
Enter fullscreen mode Exit fullscreen mode

The demo's "vs lazy" stat shows the savings: JOIN/EntityGraph → 1 query; batch → 1 + ⌈N/5⌉.

The trade-off nobody mentions

JOIN FETCH across two collections causes a cartesian product — rows multiply, and it gets slower, not faster. That's exactly when batch fetching wins. And join fetch + pagination (setMaxResults) makes Hibernate paginate in memory with a warning. There's no single right answer — which is why seeing the query counts for each strategy beats memorizing a rule.

Catch it in your own app 

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.generate_statistics=true
Enter fullscreen mode Exit fullscreen mode

If one endpoint logs dozens of near-identical select ... where id=? lines, that's your N+1.

If this saved you from an N+1 in prod, a star helps others find it: https://github.com/dev48v/n-plus-one-visualizer

Top comments (0)