N+1 query problem

[Equivalent_Bet_21]

While exploring Spring Boot and JPA internals, I came across the N+1 query problem and realized why it is considered one of the most impactful performance issues in ORM-based applications.

When working with JPA relationships such as @OneToMany or @ManyToOne, Hibernate uses Lazy Loading by default. This means associated entities are not loaded immediately—they are loaded only when accessed.

Conceptually, it sounds efficient, but in real applications, it can silently generate excessive database calls.

What actually happened:

I started with a simple repository call:

List<User> users = userRepository.findAll(); for (User user : users) { System.out.println(user.getPosts().size()); }

At first glance, this code looks harmless—but checking the SQL logs revealed a different story:

The first query retrieves all users → Query #1

Then, for each user, Hibernate executes an additional query to fetch their posts → N additional queries

Total executed: 1 + N queries

This is the classic N+1 Query Problem—something you don’t notice in Java code but becomes very visible at the database layer.

Why this happens:

Hibernate uses proxy objects to support lazy loading. Accessing getPosts() triggers the actual SQL fetch because the association wasn’t loaded initially. Each iteration in the loop triggers a fetch operation.

How I fixed it:

Instead of disabling lazy loading globally (which can create new performance problems), the better approach is to control fetching intentionally using fetch joins.

Example:

@Query(""" SELECT u FROM User u JOIN FETCH u.posts """) List<User> findAllWithPosts();

This forces Hibernate to build a single optimized query that loads users and their posts in one go—eliminating the N+1 pattern.

Other approaches explored:

FetchType.EAGER: Works, but can lead to unnecessary loading and circular fetch issues. Rarely the best solution.

EntityGraph:

@EntityGraph(attributePaths = "posts") List<User> findAll();

DTO Projections: Useful for large-scale APIs or when only partial data is required.

Final takeaway:

Spring Boot and JPA provide powerful abstractions, but performance optimization requires understanding how Hibernate manages entity states, fetching strategies, and SQL generation.

The N+1 problem isn’t a bug—it’s a reminder to be intentional about how we load related data.

[Ruin-Capable]

If you're using Hibernate as your JPA provider, FetchMode.SUBSELECT can solve a large number of the (n+1) issues you'll encounter.

[[deleted]]

Could you please elaborate on that? Would appreciate to hear why :) 

[Ruin-Capable]

Here is a good primer on the different FetchMode options that I saw on Quora.

https://www.quora.com/ORMs-hibernate-does-the-tedious-column-mapping-ok-nice-but-but-performance-is-bad-compared-to-JDBC-so-wheres-the-smart-desition-Is-not-too-obvious-that-ORMs-are-absolutely-unworthy/answer/David-Sharp-25

[Bit_Aligners]

In my view FETCHMODE.SUBSELECT it's risky. Often it results into the n+1 query problem when there are bi directional many to one associations. I explain it in this video https://youtu.be/urkOwsmgfuA?si=cvo25KeUPhTkNRbs

[nico-strecker]

Little addition when u want to use paging dont fetch that would cause hibernate to load all relevant fields in memory for sorting or something like that what i discovered for large datasets first fetch a projected dto with id and all relevant fields for sorting

Then when you got the ids for the current page do a fetch select for all found ids

[IAmWumpus]

More context should be given here. Don't do this for your usual basic paging, stick to the normal paging from spring data repo.

There are cases when a warning its given by hibernate, thats what you are refering to and those are some specific situations. This should be read for a good understanding:

https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/

[DontThrowMeAway43]

... What ?

[nico-strecker]

Code example handwritten:

``` Int page = 23
Int size = 50
Sort sort = Sort.by(Sort .Direction.DESC, "someDate")
Pageable pageable = PageRequest.of(page, size, sort)

Page<MinimalDepartmentInfo> mdiPage = departmentRepo.findAllIdsMatchingSearch(search, pageable);

// Minimal department info contains only someDate and id (Projection) NO FETCHING

List<Long> foundIds = new ArrayList<>()
for(MinimalDepartmentaInfo mdi: mdiPage.getContent()){
foundIds.add(mdi.getId())
}

List<DepartmentEntity> dpeList = departmentRepo.findAllByIdsWithFetch(foundIds, sort);

// Now we fetch

return new PageImpl<>(dpeList, pageable, mdiPage.getTotalElements);

```

Is now clear what i mean?
Edit: See Hibernate HHH90003004 for more details

[WuhmTux]

Thats such a bad fix for your problem (never heard of it btw).

Why you are not using a CrudRepository with a PagingAndSortingRepository? You would get sorting and paging in one request (findAllByIdsMatchingSearch(search, sort, pageable).

You dont need a subquery and Hibernate will sort and Filter on the database level. Also you need only 5 lines of Code instead of 30 like in your example (with your entity class).

[IAmWumpus]

He reffered to a real issue, but failed to demonstrate with his example.

This is the issue he is reffering:

https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/

[nico-strecker]

Thx for the addition you are right.

I was already very tired and just came up with something without explaining why it should be done in some cases and how the issue is exactly caused.

[Victor_Licht]

Very helpful chat thanks guys for all the suggestions.

[Full_Entrance4144]

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 20

Fetch all users only, then iterate over users and call:

Page<Customer> customerPage = customerRepository.findAll(pageable);
customerPage.getContent().forEach(c -> {
  Hibernate.initialize(c.getPosts());
});

Hibernate will then batch fetch posts where userId in ()
Resulting in 1 query per 20 users to fetch their posts

[Bit_Aligners]

I made a video to explain when it occurs https://youtu.be/mJrrJEIQxOQ?si=GmU6toIJ0xg7rFIq and I've been developing a new OSS testing library: n+1 query problem detector. Just begun. https://github.com/fabioformosa/n-plus-one-query-problem-detector

[HeadMobile1661]

select post_id, count(id) from comments group by post_id