The N+1 select problem

Key points

• One query loads 100 authors. Touching author.getBooks() inside a loop fires 100 more. That is N+1.
• It never hurts on ten dev rows. Log SQL with spring.jpa.show-sql, or assert a query count in tests, to catch it early.
• Fix 1: join fetch in JPQL - one query, one round trip. In Hibernate 6 duplicate parents are removed for you, so distinct is no longer needed.
• Fix 2: @EntityGraph(attributePaths = "books") on a Spring Data method - same effect, no custom query.
• Fix 3: hibernate.default_batch_fetch_size loads lazy associations in chunks using an IN list. A great global safety net.
• Never combine a collection fetch join with pagination: Hibernate has to page in memory. Use batch fetching there instead.

Example

// N+1: 1 query for authors, then 1 per author for books
List<Author> authors = em.createQuery("select a from Author a", Author.class)
                         .getResultList();
authors.forEach(a -> a.getBooks().size());

// Fix 1 - fetch join, a single query
List<Author> withBooks = em.createQuery(
        "select a from Author a left join fetch a.books", Author.class)
    .getResultList();

// Fix 2 - Spring Data entity graph
public interface AuthorRepository extends JpaRepository<Author, Long> {
    @EntityGraph(attributePaths = "books")
    List<Author> findByCountry(String country);
}

// Fix 3 - application.properties, batches lazy loads 25 parents at a time
// spring.jpa.properties.hibernate.default_batch_fetch_size=25

One screen should cost a handful of queries, not one per row.