Database Optimizations in Java applications

Database Optimizations in Java applications

Database interactions cab be one of the biggest bottlenecks. We need to look at multiple things to improve performace when it comes to database interactions.

1. Query Optimization

   I used Spring Data’s @Query annotation to replace inefficient auto-generated queries:

   // BEFORE: Using derived method name (inefficient)
   List<Order> findByUserIdAndStatusAndCreatedDateBetween(
       Long userId, OrderStatus status, LocalDate start, LocalDate end);
   

   // AFTER: Optimized query
   @Query("SELECT o FROM Order o WHERE o.userId = :userId " +
          "AND o.status = :status " +
          "AND o.createdDate BETWEEN :start AND :end " +
          "ORDER BY o.createdDate DESC")
   List<Order> findUserOrdersInDateRange(
       @Param("userId") Long userId,
       @Param("status") OrderStatus status,
       @Param("start") LocalDate start,
       @Param("end") LocalDate end);
   

   I also optimized a particularly problematic N+1 query by using Hibernate’s @BatchSize:

   @Entity
   public class Order {
       // Other fields
   
       @OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
       @BatchSize(size = 30) // Batch fetch order items
       private Set<OrderItem> items;
   }
   

2. Connection Pool Tuning

   The default HikariCP settings were causing connection contention. After extensive testing, I arrived at this configuration:

   spring:
     datasource:
       hikari:
         maximum-pool-size: 30
         minimum-idle: 10
         idle-timeout: 30000
         connection-timeout: 2000
         max-lifetime: 1800000
   

   The key insight was that more connections isn’t always better; we found our sweet spot at 30 connections, which reduced contention without overwhelming the database.

3. Implementing Strategic Caching

   Caching

TODO

1. https://medium.com/@AlexanderObregon/optimizing-queries-with-query-annotation-in-spring-data-jpa-fe213c8a60a
2. https://thorben-janssen.com/native-queries-with-spring-data-jpa/

See

1. https://stackoverflow.com/questions/70698661/spring-data-very-slow-native-query-compared-to-raw-sql
2. https://stackoverflow.com/questions/42810067/query-on-db-is-fast-but-fetching-by-jparepository-is-slow?rq=4