Advanced JPA: Query Optimization & N+1 Solutions in Spring Boot

Introduction

JPA makes database operations easy—until your application grinds to a halt because of hidden performance problems. The infamous N+1 query problem, lazy loading pitfalls, and inefficient fetch strategies can turn a fast application into a slow one.

What You'll Learn

✅ Understand the N+1 query problem and why it matters
✅ Detect N+1 issues with logging and tools
✅ Fix N+1 with fetch joins, entity graphs, and batch fetching
✅ Choose the right fetch strategy (LAZY vs EAGER)
✅ Use projections and DTOs for read-only queries
✅ Write efficient JPQL and native queries
✅ Optimize bulk operations
✅ Monitor query performance in production

Prerequisites

Spring Boot basics (Getting Started guide)
JPA fundamentals (Database Integration guide)
Understanding of SQL and relational databases

1. Understanding the N+1 Query Problem

What is N+1?

The N+1 problem occurs when your code executes 1 query to fetch N entities, then N additional queries to fetch related data for each entity.

Example scenario:

// Entity relationships
@Entity
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
 
    @OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
    private List<Book> books = new ArrayList<>();
 
    // Getters, setters...
}
 
@Entity
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
 
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id")
    private Author author;
 
    // Getters, setters...
}

The problem code:

@Service
public class AuthorService {
 
    public List<AuthorWithBooksDto> getAllAuthorsWithBooks() {
        // Query 1: Get all authors
        List<Author> authors = authorRepository.findAll();
 
        // For each author, accessing books triggers another query!
        return authors.stream()
            .map(author -> new AuthorWithBooksDto(
                author.getId(),
                author.getName(),
                author.getBooks().size()  // ❌ N queries here!
            ))
            .toList();
    }
}

What happens with 100 authors:

-- Query 1: Fetch authors
SELECT * FROM authors;
 
-- Query 2-101: Fetch books for each author
SELECT * FROM books WHERE author_id = 1;
SELECT * FROM books WHERE author_id = 2;
SELECT * FROM books WHERE author_id = 3;
-- ... 97 more queries
SELECT * FROM books WHERE author_id = 100;

That's 101 queries instead of 1 or 2!

Why N+1 is Dangerous

Scenario	Without N+1	With N+1
100 authors	2 queries, ~50ms	101 queries, ~2000ms
1000 authors	2 queries, ~100ms	1001 queries, ~20000ms
10000 authors	2 queries, ~500ms	10001 queries, minutes

The performance degrades linearly with data growth. In production, this causes:

Slow response times
Database connection exhaustion
Increased server costs
Poor user experience

2. Detecting N+1 Problems

Enable SQL Logging

application.yml:

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        # Log statistics
        generate_statistics: true
        # Log slow queries (>500ms)
        session.events.log.LOG_QUERIES_SLOWER_THAN_MS: 500
 
logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.orm.jdbc.bind: TRACE  # Show query parameters
    org.hibernate.stat: DEBUG           # Show statistics

Use Hibernate Statistics

@Component
@Slf4j
public class HibernateStatisticsLogger {
 
    @Autowired
    private EntityManagerFactory emf;
 
    @EventListener(ApplicationReadyEvent.class)
    public void enableStatistics() {
        SessionFactory sessionFactory = emf.unwrap(SessionFactory.class);
        sessionFactory.getStatistics().setStatisticsEnabled(true);
    }
 
    @Scheduled(fixedRate = 60000) // Log every minute
    public void logStatistics() {
        SessionFactory sessionFactory = emf.unwrap(SessionFactory.class);
        Statistics stats = sessionFactory.getStatistics();
 
        log.info("=== Hibernate Statistics ===");
        log.info("Query count: {}", stats.getQueryExecutionCount());
        log.info("Entity fetch count: {}", stats.getEntityFetchCount());
        log.info("Collection fetch count: {}", stats.getCollectionFetchCount());
        log.info("Second-level cache hit: {}", stats.getSecondLevelCacheHitCount());
 
        // Reset for next interval
        stats.clear();
    }
}

Use Query Count Assertions in Tests

@SpringBootTest
@Import(QueryCountConfig.class)
class AuthorServiceTest {
 
    @Autowired
    private AuthorService authorService;
 
    @Autowired
    private QueryCounter queryCounter;
 
    @Test
    void getAllAuthorsWithBooks_shouldNotCauseNPlus1() {
        // Given: 100 authors with books
        createTestData(100);
 
        // When
        queryCounter.reset();
        List<AuthorWithBooksDto> result = authorService.getAllAuthorsWithBooks();
 
        // Then: Should use at most 2 queries (not 101!)
        assertThat(queryCounter.getCount()).isLessThanOrEqualTo(2);
        assertThat(result).hasSize(100);
    }
}
 
@Component
public class QueryCounter {
 
    private final AtomicInteger count = new AtomicInteger(0);
 
    public void increment() {
        count.incrementAndGet();
    }
 
    public int getCount() {
        return count.get();
    }
 
    public void reset() {
        count.set(0);
    }
}

Use Hypersistence Utils (Recommended)

Add the library that makes N+1 detection automatic:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-63</artifactId>
    <version>3.7.0</version>
</dependency>

@SpringBootTest
class AuthorServiceTest {
 
    @Test
    void getAllAuthors_shouldNotHaveNPlus1() {
        SQLStatementCountValidator.reset();
 
        authorService.getAllAuthorsWithBooks();
 
        // Assert exactly 2 SELECT statements
        SQLStatementCountValidator.assertSelectCount(2);
    }
}

3. Fixing N+1 with Fetch Joins

Solution 1: JPQL Fetch Join

The most common solution—fetch related entities in a single query:

public interface AuthorRepository extends JpaRepository<Author, Long> {
 
    // ✅ Fetch join - loads authors AND books in one query
    @Query("SELECT a FROM Author a LEFT JOIN FETCH a.books")
    List<Author> findAllWithBooks();
 
    // ✅ With conditions
    @Query("SELECT a FROM Author a LEFT JOIN FETCH a.books WHERE a.name LIKE %:name%")
    List<Author> findByNameWithBooks(@Param("name") String name);
 
    // ✅ Multiple relationships
    @Query("""
        SELECT a FROM Author a
        LEFT JOIN FETCH a.books b
        LEFT JOIN FETCH b.publisher
        WHERE a.active = true
        """)
    List<Author> findActiveWithBooksAndPublisher();
}

The optimized service:

@Service
public class AuthorService {
 
    public List<AuthorWithBooksDto> getAllAuthorsWithBooks() {
        // Now executes only 1 query!
        List<Author> authors = authorRepository.findAllWithBooks();
 
        return authors.stream()
            .map(author -> new AuthorWithBooksDto(
                author.getId(),
                author.getName(),
                author.getBooks().size()  // ✅ Already loaded!
            ))
            .toList();
    }
}

Generated SQL:

SELECT a.*, b.*
FROM authors a
LEFT JOIN books b ON a.id = b.author_id

Fetch Join with Pagination (Careful!)

⚠️ Problem: Fetch joins with @OneToMany don't work well with pagination:

// ❌ This causes issues!
@Query("SELECT a FROM Author a LEFT JOIN FETCH a.books")
Page<Author> findAllWithBooks(Pageable pageable);

Hibernate will load ALL data in memory and paginate there (bad!).

Solution: Two-query approach

public interface AuthorRepository extends JpaRepository<Author, Long> {
 
    // Query 1: Get IDs with pagination
    @Query("SELECT a.id FROM Author a")
    Page<Long> findAllIds(Pageable pageable);
 
    // Query 2: Fetch data for those IDs
    @Query("SELECT a FROM Author a LEFT JOIN FETCH a.books WHERE a.id IN :ids")
    List<Author> findByIdsWithBooks(@Param("ids") List<Long> ids);
}
 
@Service
public class AuthorService {
 
    public Page<AuthorWithBooksDto> getAuthorsWithBooksPaginated(Pageable pageable) {
        // Query 1: Get page of IDs
        Page<Long> idPage = authorRepository.findAllIds(pageable);
 
        if (idPage.isEmpty()) {
            return Page.empty(pageable);
        }
 
        // Query 2: Fetch full data for those IDs
        List<Author> authors = authorRepository.findByIdsWithBooks(idPage.getContent());
 
        // Maintain order from pagination
        Map<Long, Author> authorMap = authors.stream()
            .collect(Collectors.toMap(Author::getId, a -> a));
 
        List<AuthorWithBooksDto> dtos = idPage.getContent().stream()
            .map(authorMap::get)
            .filter(Objects::nonNull)
            .map(this::toDto)
            .toList();
 
        return new PageImpl<>(dtos, pageable, idPage.getTotalElements());
    }
}

4. Entity Graphs

Entity Graphs provide a declarative way to define fetch plans without changing your queries.

Named Entity Graph

@Entity
@NamedEntityGraph(
    name = "Author.withBooks",
    attributeNodes = @NamedAttributeNode("books")
)
@NamedEntityGraph(
    name = "Author.withBooksAndPublisher",
    attributeNodes = {
        @NamedAttributeNode(value = "books", subgraph = "books-subgraph")
    },
    subgraphs = {
        @NamedSubgraph(
            name = "books-subgraph",
            attributeNodes = @NamedAttributeNode("publisher")
        )
    }
)
public class Author {
    @Id
    private Long id;
    private String name;
 
    @OneToMany(mappedBy = "author")
    private List<Book> books;
}

Using Entity Graphs in Repository

public interface AuthorRepository extends JpaRepository<Author, Long> {
 
    // Use named graph
    @EntityGraph(value = "Author.withBooks")
    List<Author> findByActiveTrue();
 
    // Use named graph with query
    @EntityGraph(value = "Author.withBooksAndPublisher")
    @Query("SELECT a FROM Author a WHERE a.name LIKE %:name%")
    List<Author> searchByName(@Param("name") String name);
 
    // Ad-hoc entity graph (no need for @NamedEntityGraph)
    @EntityGraph(attributePaths = {"books", "books.publisher"})
    Optional<Author> findWithBooksById(Long id);
}

Dynamic Entity Graphs

@Service
public class AuthorService {
 
    @PersistenceContext
    private EntityManager em;
 
    public Author findWithDynamicGraph(Long id, List<String> attributePaths) {
        EntityGraph<Author> graph = em.createEntityGraph(Author.class);
 
        for (String path : attributePaths) {
            graph.addAttributeNodes(path);
        }
 
        Map<String, Object> hints = Map.of("jakarta.persistence.fetchgraph", graph);
 
        return em.find(Author.class, id, hints);
    }
}

Entity Graph vs Fetch Join

Feature	Fetch Join	Entity Graph
Syntax	JPQL string	Annotations/API
Reusability	Per query	Reusable across queries
Dynamic	Hard to change	Easy to modify at runtime
Multiple joins	Complex syntax	Clean attribute paths
Cartesian product	Can happen	Can happen

5. Batch Fetching

When you can't use fetch joins (e.g., with pagination), batch fetching reduces N queries to N/batch_size queries.

Configure Batch Size

Globally (application.yml):

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 20

Per-entity:

@Entity
public class Author {
 
    @OneToMany(mappedBy = "author")
    @BatchSize(size = 20)  // Fetch 20 books per batch
    private List<Book> books;
}

How It Works

Without batch fetching (N+1):

SELECT * FROM books WHERE author_id = 1;
SELECT * FROM books WHERE author_id = 2;
SELECT * FROM books WHERE author_id = 3;
-- ... 97 more

With batch size = 20 (6 queries for 100 authors):

SELECT * FROM books WHERE author_id IN (1, 2, 3, ..., 20);
SELECT * FROM books WHERE author_id IN (21, 22, 23, ..., 40);
SELECT * FROM books WHERE author_id IN (41, 42, 43, ..., 60);
SELECT * FROM books WHERE author_id IN (61, 62, 63, ..., 80);
SELECT * FROM books WHERE author_id IN (81, 82, 83, ..., 100);

Subselect Fetching

An alternative that uses a subquery:

@Entity
public class Author {
 
    @OneToMany(mappedBy = "author")
    @Fetch(FetchMode.SUBSELECT)
    private List<Book> books;
}

Generated SQL:

SELECT * FROM books
WHERE author_id IN (SELECT id FROM authors);

6. Projections and DTOs

The fastest queries return only what you need, not entire entities.

Interface Projection

// Define what you need
public interface AuthorSummary {
    Long getId();
    String getName();
    Integer getBookCount();  // Derived from query
}
 
public interface AuthorRepository extends JpaRepository<Author, Long> {
 
    // Spring Data creates implementation automatically
    @Query("""
        SELECT a.id as id, a.name as name, COUNT(b) as bookCount
        FROM Author a
        LEFT JOIN a.books b
        GROUP BY a.id, a.name
        """)
    List<AuthorSummary> findAllSummaries();
 
    // Simple projection without aggregation
    List<AuthorSummary> findByActiveTrue();
}

Class-Based Projection (DTO)

public record AuthorDto(
    Long id,
    String name,
    int bookCount
) {}
 
public interface AuthorRepository extends JpaRepository<Author, Long> {
 
    @Query("""
        SELECT new com.example.dto.AuthorDto(
            a.id, a.name, SIZE(a.books)
        )
        FROM Author a
        """)
    List<AuthorDto> findAllAsDto();
}

Tuple Projection

For dynamic queries:

@Service
public class AuthorService {
 
    @PersistenceContext
    private EntityManager em;
 
    public List<Map<String, Object>> findAuthorsWithStats() {
        List<Tuple> results = em.createQuery("""
            SELECT a.id as id, a.name as name,
                   COUNT(b) as bookCount,
                   AVG(b.price) as avgPrice
            FROM Author a
            LEFT JOIN a.books b
            GROUP BY a.id, a.name
            """, Tuple.class)
            .getResultList();
 
        return results.stream()
            .map(tuple -> Map.of(
                "id", tuple.get("id"),
                "name", tuple.get("name"),
                "bookCount", tuple.get("bookCount"),
                "avgPrice", tuple.get("avgPrice")
            ))
            .toList();
    }
}

When to Use Projections

Scenario	Use
Read-only display	✅ Interface projection
Complex aggregations	✅ DTO projection
Need to modify data	❌ Use full entity
Complex object graphs	❌ Use entity + fetch join

Performance comparison:

// Fetches entire entities (slower, more memory)
List<Author> authors = authorRepository.findAll();
 
// Fetches only needed fields (faster, less memory)
List<AuthorSummary> summaries = authorRepository.findAllSummaries();

7. Efficient JPQL and Native Queries

JPQL Best Practices

public interface BookRepository extends JpaRepository<Book, Long> {
 
    // ✅ Use parameters - never concatenate!
    @Query("SELECT b FROM Book b WHERE b.title LIKE %:keyword%")
    List<Book> searchByTitle(@Param("keyword") String keyword);
 
    // ✅ Use pagination in the database
    @Query("SELECT b FROM Book b WHERE b.price > :minPrice ORDER BY b.price")
    Page<Book> findExpensiveBooks(@Param("minPrice") BigDecimal minPrice, Pageable pageable);
 
    // ✅ Select only what you need
    @Query("SELECT b.title, b.price FROM Book b WHERE b.author.id = :authorId")
    List<Object[]> findTitleAndPriceByAuthor(@Param("authorId") Long authorId);
 
    // ✅ Use EXISTS for checking existence (faster than COUNT)
    @Query("SELECT CASE WHEN COUNT(b) > 0 THEN true ELSE false END FROM Book b WHERE b.isbn = :isbn")
    boolean existsByIsbn(@Param("isbn") String isbn);
}

Native Queries

For complex queries or database-specific features:

public interface BookRepository extends JpaRepository<Book, Long> {
 
    // Native query for complex SQL
    @Query(value = """
        SELECT b.*
        FROM books b
        INNER JOIN authors a ON b.author_id = a.id
        WHERE b.title ILIKE '%' || :keyword || '%'
           OR a.name ILIKE '%' || :keyword || '%'
        ORDER BY b.created_at DESC
        LIMIT :limit
        """, nativeQuery = true)
    List<Book> fullTextSearch(@Param("keyword") String keyword, @Param("limit") int limit);
 
    // Using PostgreSQL-specific features
    @Query(value = """
        SELECT b.*, similarity(b.title, :title) as sim
        FROM books b
        WHERE similarity(b.title, :title) > 0.3
        ORDER BY sim DESC
        LIMIT 10
        """, nativeQuery = true)
    List<Book> findSimilarTitles(@Param("title") String title);
 
    // Count with native for performance
    @Query(value = "SELECT COUNT(*) FROM books WHERE author_id = :authorId", nativeQuery = true)
    long countByAuthorFast(@Param("authorId") Long authorId);
}

Query Hints for Performance

public interface BookRepository extends JpaRepository<Book, Long> {
 
    // Read-only hint (Hibernate won't track changes)
    @QueryHints(@QueryHint(name = "org.hibernate.readOnly", value = "true"))
    @Query("SELECT b FROM Book b WHERE b.price > :minPrice")
    List<Book> findExpensiveReadOnly(@Param("minPrice") BigDecimal minPrice);
 
    // Set fetch size for large results
    @QueryHints(@QueryHint(name = "org.hibernate.fetchSize", value = "100"))
    @Query("SELECT b FROM Book b")
    Stream<Book> streamAllBooks();
 
    // Comment hint (for query identification in logs)
    @QueryHints(@QueryHint(name = "org.hibernate.comment", value = "dashboard-books-query"))
    List<Book> findByAuthorId(Long authorId);
}

8. Bulk Operations

Single-entity operations are slow for large datasets. Use bulk operations instead.

Bulk Updates

public interface BookRepository extends JpaRepository<Book, Long> {
 
    // Bulk update - bypasses entity lifecycle
    @Modifying
    @Query("UPDATE Book b SET b.price = b.price * :multiplier WHERE b.category = :category")
    int applyDiscount(@Param("category") String category, @Param("multiplier") BigDecimal multiplier);
 
    // Bulk delete
    @Modifying
    @Query("DELETE FROM Book b WHERE b.soldOut = true AND b.updatedAt < :cutoffDate")
    int deleteOldSoldOutBooks(@Param("cutoffDate") LocalDateTime cutoffDate);
 
    // Soft delete in bulk
    @Modifying
    @Query("UPDATE Book b SET b.deleted = true, b.deletedAt = CURRENT_TIMESTAMP WHERE b.id IN :ids")
    int softDeleteByIds(@Param("ids") List<Long> ids);
}

Important: Always use @Modifying and wrap in a transaction:

@Service
@Transactional
public class BookService {
 
    public void applyHolidaySale(String category) {
        int updated = bookRepository.applyDiscount(category, new BigDecimal("0.80"));
        log.info("Applied 20% discount to {} books", updated);
 
        // ⚠️ Persistence context is now stale!
        // Either clear it or re-fetch entities
        entityManager.clear();
    }
}

Batch Insert

@Service
public class BookImportService {
 
    @PersistenceContext
    private EntityManager em;
 
    @Transactional
    public void importBooks(List<BookDto> books) {
        int batchSize = 50;
 
        for (int i = 0; i < books.size(); i++) {
            BookDto dto = books.get(i);
            Book book = new Book(dto.title(), dto.price(), dto.isbn());
            em.persist(book);
 
            // Flush and clear every batch
            if (i > 0 && i % batchSize == 0) {
                em.flush();
                em.clear();
                log.debug("Flushed batch at index {}", i);
            }
        }
 
        // Final flush
        em.flush();
        em.clear();
    }
}

Configure batch size in properties:

spring:
  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: 50
          batch_versioned_data: true
        order_inserts: true
        order_updates: true

Using Spring's JdbcTemplate for Maximum Performance

For very large operations, bypass JPA entirely:

@Service
public class BookBulkService {
 
    private final JdbcTemplate jdbcTemplate;
 
    public void bulkInsert(List<Book> books) {
        String sql = "INSERT INTO books (title, price, isbn, author_id) VALUES (?, ?, ?, ?)";
 
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Book book = books.get(i);
                ps.setString(1, book.getTitle());
                ps.setBigDecimal(2, book.getPrice());
                ps.setString(3, book.getIsbn());
                ps.setLong(4, book.getAuthor().getId());
            }
 
            @Override
            public int getBatchSize() {
                return books.size();
            }
        });
    }
 
    public void bulkUpdate(Map<Long, BigDecimal> priceUpdates) {
        String sql = "UPDATE books SET price = ? WHERE id = ?";
 
        List<Object[]> batchArgs = priceUpdates.entrySet().stream()
            .map(entry -> new Object[]{entry.getValue(), entry.getKey()})
            .toList();
 
        jdbcTemplate.batchUpdate(sql, batchArgs);
    }
}

9. Fetch Strategy Guidelines

When to Use LAZY (Default)

@Entity
public class Author {
 
    // ✅ LAZY for collections - always!
    @OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
    private List<Book> books;
 
    // ✅ LAZY for large objects
    @Lob
    @Basic(fetch = FetchType.LAZY)
    private String biography;
}

LAZY is the default for:

@OneToMany and @ManyToMany
Large text/binary fields

When to Use EAGER

Almost never! But if data is always needed together:

@Entity
public class Order {
 
    // EAGER might make sense if you ALWAYS need customer info
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "customer_id")
    private Customer customer;
}

Decision Table

Relationship	Default	Recommendation
`@ManyToOne`	EAGER	Consider LAZY, use fetch join when needed
`@OneToOne`	EAGER	Consider LAZY, especially if optional
`@OneToMany`	LAZY	Keep LAZY, use fetch join or batch
`@ManyToMany`	LAZY	Keep LAZY, use fetch join or batch

Avoid the Open Session in View Anti-Pattern

Disable OSIV:

spring:
  jpa:
    open-in-view: false  # ✅ Recommended for production

When OSIV is disabled, you must fetch all needed data in the service layer:

@Service
@Transactional(readOnly = true)
public class AuthorService {
 
    public AuthorDto getAuthorDetails(Long id) {
        // All fetching happens here, within the transaction
        Author author = authorRepository.findWithBooksById(id)
            .orElseThrow(() -> new NotFoundException("Author not found"));
 
        // Map to DTO while session is open
        return new AuthorDto(
            author.getId(),
            author.getName(),
            author.getBooks().stream()
                .map(b -> new BookDto(b.getId(), b.getTitle()))
                .toList()
        );
    }
}

10. Query Monitoring and Profiling

Log Slow Queries

spring:
  jpa:
    properties:
      hibernate:
        session:
          events:
            log:
              LOG_QUERIES_SLOWER_THAN_MS: 100  # Log queries > 100ms

Use Spring Boot Actuator

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>

management:
  endpoints:
    web:
      exposure:
        include: health,metrics,prometheus
  metrics:
    tags:
      application: ${spring.application.name}

Monitor these metrics:

hibernate.query.executions - Total queries executed
hibernate.sessions.open - Active sessions
hikaricp.connections.active - Active DB connections

Custom Query Logging

@Component
@Aspect
@Slf4j
public class QueryPerformanceAspect {
 
    @Around("execution(* org.springframework.data.jpa.repository.JpaRepository+.*(..))")
    public Object logQueryPerformance(ProceedingJoinPoint joinPoint) throws Throwable {
        String methodName = joinPoint.getSignature().toShortString();
 
        long startTime = System.currentTimeMillis();
        try {
            return joinPoint.proceed();
        } finally {
            long duration = System.currentTimeMillis() - startTime;
 
            if (duration > 100) {
                log.warn("Slow query: {} took {}ms", methodName, duration);
            } else if (log.isDebugEnabled()) {
                log.debug("Query: {} took {}ms", methodName, duration);
            }
        }
    }
}

Database-Side Monitoring

PostgreSQL slow query log:

-- Check current setting
SHOW log_min_duration_statement;
 
-- Set to log queries > 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

Find missing indexes:

-- Queries without index usage
SELECT schemaname, tablename, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

11. Hands-On: Optimizing a Real Service

Let's apply everything to a realistic e-commerce scenario.

The Problem

// ❌ Naive implementation with multiple N+1 problems
@Service
public class OrderService {
 
    public List<OrderDetailsDto> getRecentOrders(Long customerId) {
        List<Order> orders = orderRepository.findByCustomerId(customerId);
 
        return orders.stream()
            .map(order -> new OrderDetailsDto(
                order.getId(),
                order.getOrderDate(),
                order.getCustomer().getName(),           // N+1 #1
                order.getItems().stream()                // N+1 #2
                    .map(item -> new OrderItemDto(
                        item.getProduct().getName(),     // N+1 #3
                        item.getQuantity(),
                        item.getProduct().getPrice()     // Already fetched
                    ))
                    .toList(),
                order.getTotalAmount()
            ))
            .toList();
    }
}

The Optimized Solution

Step 1: Create optimized query

public interface OrderRepository extends JpaRepository<Order, Long> {
 
    @Query("""
        SELECT o FROM Order o
        JOIN FETCH o.customer
        JOIN FETCH o.items i
        JOIN FETCH i.product
        WHERE o.customer.id = :customerId
        ORDER BY o.orderDate DESC
        """)
    List<Order> findByCustomerIdWithDetails(@Param("customerId") Long customerId);
 
    // For pagination, use the two-query approach
    @Query("SELECT o.id FROM Order o WHERE o.customer.id = :customerId ORDER BY o.orderDate DESC")
    Page<Long> findIdsByCustomerId(@Param("customerId") Long customerId, Pageable pageable);
 
    @Query("""
        SELECT o FROM Order o
        JOIN FETCH o.customer
        JOIN FETCH o.items i
        JOIN FETCH i.product
        WHERE o.id IN :ids
        ORDER BY o.orderDate DESC
        """)
    List<Order> findByIdsWithDetails(@Param("ids") List<Long> ids);
}

Step 2: Use projection for list views

public interface OrderSummary {
    Long getId();
    LocalDateTime getOrderDate();
    String getCustomerName();
    BigDecimal getTotalAmount();
    Integer getItemCount();
}
 
public interface OrderRepository extends JpaRepository<Order, Long> {
 
    @Query("""
        SELECT o.id as id,
               o.orderDate as orderDate,
               c.name as customerName,
               o.totalAmount as totalAmount,
               SIZE(o.items) as itemCount
        FROM Order o
        JOIN o.customer c
        WHERE c.id = :customerId
        ORDER BY o.orderDate DESC
        """)
    List<OrderSummary> findSummariesByCustomerId(@Param("customerId") Long customerId);
}

Step 3: Optimized service

@Service
@Transactional(readOnly = true)
public class OrderService {
 
    // For list view - lightweight projection
    public List<OrderSummary> getOrderSummaries(Long customerId) {
        return orderRepository.findSummariesByCustomerId(customerId);
    }
 
    // For detail view - full entity with fetch joins
    public OrderDetailsDto getOrderDetails(Long orderId) {
        Order order = orderRepository.findByIdWithDetails(orderId)
            .orElseThrow(() -> new NotFoundException("Order not found"));
 
        return mapToDetailsDto(order);
    }
 
    // For paginated results
    public Page<OrderDetailsDto> getOrdersPaginated(Long customerId, Pageable pageable) {
        Page<Long> idPage = orderRepository.findIdsByCustomerId(customerId, pageable);
 
        if (idPage.isEmpty()) {
            return Page.empty(pageable);
        }
 
        List<Order> orders = orderRepository.findByIdsWithDetails(idPage.getContent());
 
        // Maintain order
        Map<Long, Order> orderMap = orders.stream()
            .collect(Collectors.toMap(Order::getId, o -> o));
 
        List<OrderDetailsDto> dtos = idPage.getContent().stream()
            .map(orderMap::get)
            .filter(Objects::nonNull)
            .map(this::mapToDetailsDto)
            .toList();
 
        return new PageImpl<>(dtos, pageable, idPage.getTotalElements());
    }
 
    private OrderDetailsDto mapToDetailsDto(Order order) {
        return new OrderDetailsDto(
            order.getId(),
            order.getOrderDate(),
            order.getCustomer().getName(),
            order.getItems().stream()
                .map(item -> new OrderItemDto(
                    item.getProduct().getName(),
                    item.getQuantity(),
                    item.getProduct().getPrice()
                ))
                .toList(),
            order.getTotalAmount()
        );
    }
}

Performance comparison:

Operation	Before (N+1)	After (Optimized)
10 orders, 5 items each	56 queries	1-2 queries
100 orders, 5 items each	506 queries	1-2 queries
Response time (100 orders)	~2000ms	~50ms

Summary and Key Takeaways

✅ N+1 is the #1 JPA performance killer - always watch for it
✅ Enable SQL logging in development to catch issues early
✅ Use fetch joins for loading related entities in one query
✅ Use entity graphs for reusable fetch plans
✅ Configure batch fetching when fetch joins aren't practical
✅ Use projections/DTOs for read-only queries to reduce overhead
✅ Bulk operations for large-scale updates/inserts
✅ Keep LAZY as default, fetch eagerly only when needed
✅ Disable OSIV in production for predictable behavior
✅ Monitor queries with logging, metrics, and database tools

Quick Reference: N+1 Solution Decision Tree

Need to load related data?
├── Always needed together?
│   ├── Yes → Consider EAGER (rare) or default fetch join
│   └── No → Keep LAZY
│
├── For display/read-only?
│   └── Yes → Use Projection/DTO
│
├── Need full entities?
│   ├── Small result set → Fetch Join
│   ├── Need pagination → Two-query approach
│   └── Large result set → Batch Fetching
│
└── Multiple relationships?
    ├── One level → Fetch Join with multiple joins
    └── Deep graph → Entity Graph

What's Next?

Now that you've optimized your JPA queries, continue with:

Continue the Spring Boot series:

Docker & Kubernetes Deployment - Deploy your optimized app
Spring Boot Caching with Redis - Add caching for even better performance
Performance Optimization & Profiling - Full application profiling

Related Posts:

Database Integration with JPA & PostgreSQL - JPA fundamentals
REST API Best Practices - API design patterns
Spring Boot Testing Guide - Test your optimized queries

Database Fundamentals:

Relational Database Fundamentals - How databases work
Database Schema Design Guide - Design efficient schemas

Part of the Spring Boot Learning Roadmap series

Introduction

What You'll Learn

Prerequisites

1. Understanding the N+1 Query Problem

What is N+1?

Why N+1 is Dangerous

2. Detecting N+1 Problems

Enable SQL Logging

Use Hibernate Statistics

Use Query Count Assertions in Tests

Use Hypersistence Utils (Recommended)

3. Fixing N+1 with Fetch Joins

Solution 1: JPQL Fetch Join

Fetch Join with Pagination (Careful!)

4. Entity Graphs

Named Entity Graph

Using Entity Graphs in Repository

Dynamic Entity Graphs

Entity Graph vs Fetch Join

5. Batch Fetching

Configure Batch Size

How It Works

Subselect Fetching

6. Projections and DTOs

Interface Projection

Class-Based Projection (DTO)

Tuple Projection

When to Use Projections

7. Efficient JPQL and Native Queries

JPQL Best Practices

Native Queries

Query Hints for Performance

8. Bulk Operations

Bulk Updates

Batch Insert

Using Spring's JdbcTemplate for Maximum Performance

9. Fetch Strategy Guidelines

When to Use LAZY (Default)

When to Use EAGER

Decision Table

Avoid the Open Session in View Anti-Pattern

10. Query Monitoring and Profiling

Log Slow Queries

Use Spring Boot Actuator

Custom Query Logging

Database-Side Monitoring

11. Hands-On: Optimizing a Real Service

The Problem

The Optimized Solution

Summary and Key Takeaways

Quick Reference: N+1 Solution Decision Tree

What's Next?

📬 Subscribe to Newsletter

💬 Comments