# Database Optimization Techniques for Spring Data JPA

> Advanced techniques for optimizing database queries in Spring Data JPA applications, including lazy loading, query optimization, and performance monitoring.

- **Author:** Utsab Dahal, Backend Developer — Spring Boot & DevOps
- **Filed:** 5 January 2024 (2024-01-05)
- **Section:** Database
- **Reading time:** 10 min read
- **Tags:** JPA, Database, Performance, Optimization
- **Canonical page:** https://dahalutsab.com.np/dispatches/database-optimization-spring-data-jpa

---

Database performance is crucial for any application's success. This guide covers advanced optimization techniques for Spring Data JPA applications.

## Introduction

Spring Data JPA simplifies database operations, but without proper optimization, it can lead to performance issues. Understanding how JPA works under the hood is essential for building efficient applications.

### Common Performance Issues

- N+1 query problems
- Inefficient lazy loading
- Missing indexes
- Poor query design
- Lack of caching

## N+1 Query Problem

The N+1 problem occurs when you fetch a list of entities and then access their related entities, causing additional queries.

### Problem Example

```java
@Entity
public class Author {
    @Id
    private Long id;
    private String name;
    
    @OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
    private List<Book> books;
}

@Entity
public class Book {
    @Id
    private Long id;
    private String title;
    
    @ManyToOne
    private Author author;
}

// This causes N+1 queries
List<Author> authors = authorRepository.findAll();
for (Author author : authors) {
    System.out.println(author.getBooks().size()); // Triggers additional query
}
```

### Solutions

#### 1. Use @EntityGraph

```java
@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {
    
    @EntityGraph(attributePaths = {"books"})
    List<Author> findAllWithBooks();
    
    @EntityGraph(attributePaths = {"books", "books.publisher"})
    List<Author> findAllWithBooksAndPublisher();
}
```

#### 2. Use JOIN FETCH in JPQL

```java
@Query("SELECT a FROM Author a JOIN FETCH a.books")
List<Author> findAllAuthorsWithBooks();

@Query("SELECT DISTINCT a FROM Author a LEFT JOIN FETCH a.books b LEFT JOIN FETCH b.publisher")
List<Author> findAllAuthorsWithBooksAndPublisher();
```

#### 3. Use Projections

```java
public interface AuthorBookProjection {
    Long getId();
    String getName();
    List<BookProjection> getBooks();
    
    interface BookProjection {
        Long getId();
        String getTitle();
    }
}

@Query("SELECT a FROM Author a JOIN FETCH a.books")
List<AuthorBookProjection> findAllAuthorsWithBooksProjection();
```

## Lazy Loading Strategies

### Understanding Fetch Types

```java
@Entity
public class Order {
    @Id
    private Long id;
    
    // Eager loading - always fetched
    @OneToMany(fetch = FetchType.EAGER)
    private List<OrderItem> items;
    
    // Lazy loading - fetched when accessed
    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer;
}
```

### Batch Fetching

```java
@Entity
@BatchSize(size = 10)
public class Author {
    @Id
    private Long id;
    
    @OneToMany(mappedBy = "author")
    @BatchSize(size = 5)
    private List<Book> books;
}
```

### Subselect Fetching

```java
@Entity
public class Department {
    @OneToMany(mappedBy = "department")
    @Fetch(FetchMode.SUBSELECT)
    private List<Employee> employees;
}
```

## Query Optimization

### Custom Queries with Pagination

```java
@Repository
public interface BookRepository extends JpaRepository<Book, Long> {
    
    @Query(value = "SELECT b FROM Book b WHERE b.publishedDate >= :date",
           countQuery = "SELECT count(b) FROM Book b WHERE b.publishedDate >= :date")
    Page<Book> findRecentBooks(@Param("date") LocalDate date, Pageable pageable);
    
    @Query("SELECT new com.example.dto.BookSummary(b.id, b.title, a.name) " +
           "FROM Book b JOIN b.author a WHERE b.genre = :genre")
    List<BookSummary> findBookSummariesByGenre(@Param("genre") String genre);
}
```

### Native Queries for Complex Operations

```java
@Query(value = "SELECT * FROM books b " +
               "WHERE b.rating > :rating " +
               "AND b.published_date BETWEEN :startDate AND :endDate " +
               "ORDER BY b.rating DESC, b.published_date DESC",
       nativeQuery = true)
List<Book> findTopRatedBooksInPeriod(@Param("rating") Double rating,
                                     @Param("startDate") LocalDate startDate,
                                     @Param("endDate") LocalDate endDate);
```

### Bulk Operations

```java
@Modifying
@Query("UPDATE Book b SET b.price = b.price * 1.1 WHERE b.genre = :genre")
int increasePriceByGenre(@Param("genre") String genre);

@Modifying
@Query("DELETE FROM Book b WHERE b.publishedDate < :date")
int deleteOldBooks(@Param("date") LocalDate date);
```

## Caching Strategies

### Second-Level Cache

```java
@Entity
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Category {
    @Id
    private Long id;
    private String name;
    
    @OneToMany(mappedBy = "category")
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private List<Product> products;
}
```

### Query Result Cache

```java
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
    
    @QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
    @Query("SELECT p FROM Product p WHERE p.featured = true")
    List<Product> findFeaturedProducts();
}
```

### Spring Cache Abstraction

```java
@Service
@Transactional
public class ProductService {
    
    @Cacheable(value = "products", key = "#id")
    public Product findById(Long id) {
        return productRepository.findById(id).orElse(null);
    }
    
    @CacheEvict(value = "products", key = "#product.id")
    public Product save(Product product) {
        return productRepository.save(product);
    }
    
    @CacheEvict(value = "products", allEntries = true)
    public void clearCache() {
        // Method implementation
    }
}
```

## Performance Monitoring

### Enable SQL Logging

```properties
# Show SQL queries
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

# Show parameter values
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

# Show statistics
spring.jpa.properties.hibernate.generate_statistics=true
```

### Custom Performance Interceptor

```java
@Component
public class QueryCountInterceptor implements Interceptor {
    private static final ThreadLocal<Integer> queryCount = new ThreadLocal<>();
    
    public static void startCounter() {
        queryCount.set(0);
    }
    
    public static int getQueryCount() {
        return queryCount.get() == null ? 0 : queryCount.get();
    }
    
    public static void clear() {
        queryCount.remove();
    }
    
    @Override
    public boolean onLoad(Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) {
        incrementCounter();
        return false;
    }
    
    private void incrementCounter() {
        Integer count = queryCount.get();
        if (count == null) {
            count = 0;
        }
        queryCount.set(count + 1);
    }
}
```

### Performance Testing

```java
@Test
public void testQueryPerformance() {
    QueryCountInterceptor.startCounter();
    
    List<Author> authors = authorRepository.findAllWithBooks();
    
    int queryCount = QueryCountInterceptor.getQueryCount();
    assertThat(queryCount).isLessThanOrEqualTo(1); // Should be 1 query with JOIN FETCH
    
    QueryCountInterceptor.clear();
}
```

## Database Indexing

### JPA Index Annotations

```java
@Entity
@Table(indexes = {
    @Index(name = "idx_book_title", columnList = "title"),
    @Index(name = "idx_book_author_genre", columnList = "author_id, genre"),
    @Index(name = "idx_book_published_date", columnList = "published_date")
})
public class Book {
    @Id
    private Long id;
    
    @Column(length = 255)
    private String title;
    
    @Column(length = 100)
    private String genre;
    
    @Column(name = "published_date")
    private LocalDate publishedDate;
    
    @ManyToOne
    @JoinColumn(name = "author_id")
    private Author author;
}
```

### Composite Indexes

```java
@Entity
@Table(indexes = {
    @Index(name = "idx_order_customer_date", 
           columnList = "customer_id, order_date DESC"),
    @Index(name = "idx_order_status_date", 
           columnList = "status, order_date")
})
public class Order {
    // Entity fields
}
```

## Connection Pool Optimization

### HikariCP Configuration

```properties
# Connection pool settings
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=600000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.leak-detection-threshold=60000
```

## Best Practices Summary

### 1. Entity Design
- Use appropriate fetch types
- Implement equals() and hashCode() properly
- Use @BatchSize for collections
- Consider using DTOs for read operations

### 2. Query Optimization
- Use projections for read-only operations
- Implement pagination for large datasets
- Use native queries for complex operations
- Avoid N+1 problems with JOIN FETCH

### 3. Caching Strategy
- Enable second-level cache for reference data
- Use query result cache for expensive queries
- Implement application-level caching with Spring Cache

### 4. Monitoring and Testing
- Enable SQL logging in development
- Monitor query counts and execution times
- Write performance tests
- Use database profiling tools

## Conclusion

Database optimization in Spring Data JPA requires understanding of JPA internals, proper entity design, and strategic use of caching. By following these techniques, you can significantly improve your application's performance:

- Eliminate N+1 query problems
- Use appropriate loading strategies
- Implement effective caching
- Monitor and measure performance
- Design proper database indexes

Remember that optimization is an iterative process. Always measure before and after implementing changes to ensure they provide the expected performance improvements.

---

Source: https://dahalutsab.com.np/dispatches/database-optimization-spring-data-jpa
From The Utsab Dahal Times — the record of Utsab Dahal, Backend Developer — Spring Boot & DevOps, Kathmandu, Nepal.
Full record: https://dahalutsab.com.np/llms-full.txt