Query Optimization

1. JPA Query Optimization

Entity Graph

@Entity
@NamedEntityGraph(
    name = "Order.full",
    attributeNodes = {
        @NamedAttributeNode("items"),
        @NamedAttributeNode("user"),
        @NamedAttributeNode("payment")
    }
)
public class Order {
    @Id
    private Long id;

    @OneToMany(mappedBy = "order")
    private Set<OrderItem> items;

    @ManyToOne
    private User user;

    @OneToOne
    private Payment payment;
}

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    @EntityGraph(value = "Order.full")
    Optional<Order> findById(Long id);

    @EntityGraph(attributePaths = {"items", "user"})
    List<Order> findByStatus(OrderStatus status);
}

Query Optimization with Specification

@Service
public class OrderService {

    public Page<Order> findOrders(OrderSearchCriteria criteria, Pageable pageable) {
        Specification<Order> spec = Specification.where(null);

        if (criteria.getStatus() != null) {
            spec = spec.and((root, query, cb) ->
                cb.equal(root.get("status"), criteria.getStatus()));
        }

        if (criteria.getStartDate() != null) {
            spec = spec.and((root, query, cb) ->
                cb.greaterThanOrEqualTo(
                    root.get("orderDate"),
                    criteria.getStartDate()
                ));
        }

        if (criteria.getUserId() != null) {
            spec = spec.and((root, query, cb) ->
                cb.equal(root.get("user").get("id"), criteria.getUserId()));
        }

        return orderRepository.findAll(spec, pageable);
    }
}

@Repository
public interface OrderRepository extends JpaRepository<Order, Long>,
                                      JpaSpecificationExecutor<Order> {

    @EntityGraph(attributePaths = {"items", "user"})
    Page<Order> findAll(Specification<Order> spec, Pageable pageable);
}

2. Native Query Optimization

Complex Joins

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = """
        WITH RankedProducts AS (
            SELECT p.*,
                   COUNT(o.id) as order_count,
                   ROW_NUMBER() OVER (
                       PARTITION BY p.category_id
                       ORDER BY COUNT(o.id) DESC
                   ) as rank
            FROM products p
            LEFT JOIN order_items oi ON p.id = oi.product_id
            LEFT JOIN orders o ON oi.order_id = o.id
            WHERE o.order_date >= :startDate
            GROUP BY p.id
        )
        SELECT *
        FROM RankedProducts
        WHERE rank <= :topN
        """, nativeQuery = true)
    List<Product> findTopSellingProductsByCategory(
        LocalDateTime startDate,
        int topN);

    @Query(value = """
        SELECT p.*,
               AVG(r.rating) as avg_rating,
               COUNT(DISTINCT o.id) as order_count
        FROM products p
        LEFT JOIN reviews r ON p.id = r.product_id
        LEFT JOIN order_items oi ON p.id = oi.product_id
        LEFT JOIN orders o ON oi.order_id = o.id
        WHERE p.category_id = :categoryId
        AND p.status = 'ACTIVE'
        GROUP BY p.id
        HAVING COUNT(DISTINCT o.id) >= :minOrders
        ORDER BY avg_rating DESC, order_count DESC
        LIMIT :limit
        """, nativeQuery = true)
    List<Product> findTopRatedProducts(
        Long categoryId,
        int minOrders,
        int limit);
}

Optimized Aggregations

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query(value = """
        SELECT DATE_TRUNC('day', o.order_date) as date,
               COUNT(*) as total_orders,
               SUM(o.total_amount) as total_revenue,
               COUNT(DISTINCT o.user_id) as unique_customers
        FROM orders o
        WHERE o.order_date BETWEEN :startDate AND :endDate
        GROUP BY DATE_TRUNC('day', o.order_date)
        ORDER BY date DESC
        """, nativeQuery = true)
    List<OrderStats> getDailyStats(
        LocalDateTime startDate,
        LocalDateTime endDate);

    @Query(value = """
        SELECT u.id as user_id,
               u.email,
               COUNT(o.id) as order_count,
               SUM(o.total_amount) as total_spent,
               MAX(o.order_date) as last_order_date
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        WHERE o.order_date >= :startDate
        GROUP BY u.id, u.email
        HAVING COUNT(o.id) >= :minOrders
        ORDER BY total_spent DESC
        LIMIT :limit
        """, nativeQuery = true)
    List<CustomerStats> getTopCustomers(
        LocalDateTime startDate,
        int minOrders,
        int limit);
}

3. Query Plan Optimization

Index Usage

@Repository
public class CustomQueryRepository {

    @PersistenceContext
    private EntityManager em;

    public List<Order> findOrdersWithOptimizedQuery(OrderSearchCriteria criteria) {
        // Create query with index hints
        String sql = """
            SELECT /*+ INDEX(o idx_order_date_status) */
                   o.*, u.*, i.*
            FROM orders o
            JOIN users u ON o.user_id = u.id
            LEFT JOIN order_items i ON o.id = i.order_id
            WHERE o.status = :status
            AND o.order_date >= :startDate
            ORDER BY o.order_date DESC
            """;

        Query query = em.createNativeQuery(sql, Order.class)
            .setParameter("status", criteria.getStatus())
            .setParameter("startDate", criteria.getStartDate())
            .setHint(QueryHints.HINT_FETCH_SIZE, 100)
            .setHint(QueryHints.HINT_READONLY, true);

        return query.getResultList();
    }
}

Query Plan Analysis

@Service
@Transactional(readOnly = true)
public class QueryAnalysisService {

    @PersistenceContext
    private EntityManager em;

    public String analyzeQueryPlan(String sql) {
        String explainSql = "EXPLAIN ANALYZE " + sql;
        Query query = em.createNativeQuery(explainSql);

        List<String> planLines = query.getResultList();
        return String.join("\n", planLines);
    }

    public void optimizeQuery(String sql) {
        // Analyze current query plan
        String currentPlan = analyzeQueryPlan(sql);
        logger.info("Current query plan:\n{}", currentPlan);

        // Suggest optimizations
        List<String> optimizations = new ArrayList<>();

        if (currentPlan.contains("Seq Scan")) {
            optimizations.add("Consider adding index to avoid sequential scan");
        }

        if (currentPlan.contains("Nested Loop")) {
            optimizations.add("Consider using JOIN hints or rewriting query");
        }

        logger.info("Suggested optimizations: {}", optimizations);
    }
}

4. Caching Strategies

Query Result Cache

@Configuration
@EnableCaching
public class QueryCacheConfig {

    @Bean
    public CacheManager queryCacheManager() {
        CaffeineCacheManager cacheManager = new CaffeineCacheManager();

        cacheManager.setCaffeine(Caffeine.newBuilder()
            .maximumSize(1000)
            .expireAfterWrite(Duration.ofMinutes(10))
            .recordStats());

        return cacheManager;
    }
}

@Service
public class ProductService {

    @Cacheable(value = "products",
               key = "#category + '-' + #page + '-' + #size",
               unless = "#result.isEmpty()")
    public Page<Product> findByCategory(String category,
                                      int page,
                                      int size) {
        return productRepository.findByCategory(
            category,
            PageRequest.of(page, size)
        );
    }

    @CacheEvict(value = "products", allEntries = true)
    @Scheduled(fixedRate = 3600000) // 1 hour
    public void clearProductCache() {
        logger.info("Clearing product cache");
    }
}

Custom Cache Implementation

@Component
public class QueryCache {

    private final LoadingCache<String, Object> cache;
    private final MeterRegistry registry;

    public QueryCache(MeterRegistry registry) {
        this.registry = registry;

        this.cache = Caffeine.newBuilder()
            .maximumSize(1000)
            .expireAfterWrite(Duration.ofMinutes(10))
            .recordStats()
            .build(this::loadData);
    }

    public <T> T get(String key, Class<T> type) {
        try {
            return type.cast(cache.get(key));
        } catch (Exception e) {
            registry.counter("cache.errors").increment();
            throw new CacheException("Failed to get from cache", e);
        }
    }

    private Object loadData(String key) {
        // Implement data loading logic
        return null;
    }

    public void recordMetrics() {
        CacheStats stats = cache.stats();

        registry.gauge("cache.size", cache.estimatedSize());
        registry.gauge("cache.hit.rate", stats.hitRate());
        registry.gauge("cache.miss.rate", stats.missRate());
        registry.gauge("cache.eviction.count", stats.evictionCount());
    }
}

5. Best Practices

1. Batch Processing

@Service
@Transactional
public class BatchProcessingService {

    private final EntityManager em;
    private final int batchSize = 100;

    public void processBatch(List<Order> orders) {
        int count = 0;

        for (Order order : orders) {
            em.persist(order);
            count++;

            if (count % batchSize == 0) {
                em.flush();
                em.clear();
            }
        }
    }

    public void updateBatch(List<OrderUpdate> updates) {
        StringBuilder sql = new StringBuilder(
            "UPDATE orders SET status = CASE id ");

        List<Long> ids = new ArrayList<>();
        List<String> statuses = new ArrayList<>();

        for (OrderUpdate update : updates) {
            sql.append(" WHEN ? THEN ? ");
            ids.add(update.getId());
            statuses.add(update.getStatus().name());
        }

        sql.append(" END WHERE id IN (?");
        for (int i = 1; i < ids.size(); i++) {
            sql.append(",?");
        }
        sql.append(")");

        Query query = em.createNativeQuery(sql.toString());

        int paramIndex = 1;
        for (int i = 0; i < ids.size(); i++) {
            query.setParameter(paramIndex++, ids.get(i));
            query.setParameter(paramIndex++, statuses.get(i));
        }
        for (Long id : ids) {
            query.setParameter(paramIndex++, id);
        }

        query.executeUpdate();
    }
}

2. Query Monitoring

@Aspect
@Component
public class QueryMonitor {

    private final MeterRegistry registry;
    private final Logger logger = LoggerFactory.getLogger(QueryMonitor.class);

    @Around("@annotation(org.springframework.transaction.annotation.Transactional)")
    public Object monitorQuery(ProceedingJoinPoint joinPoint) throws Throwable {
        String methodName = joinPoint.getSignature().getName();
        Timer.Sample sample = Timer.start(registry);

        try {
            Object result = joinPoint.proceed();

            sample.stop(Timer.builder("query.execution")
                .tag("method", methodName)
                .register(registry));

            return result;
        } catch (Exception e) {
            registry.counter("query.errors",
                "method", methodName,
                "error", e.getClass().getSimpleName()
            ).increment();

            throw e;
        }
    }

    @AfterReturning("execution(* org.hibernate.SessionFactory.openSession(..))")
    public void afterSessionCreated() {
        registry.counter("hibernate.sessions.created").increment();
    }
}

3. Dynamic Query Building

@Component
public class DynamicQueryBuilder {

    public String buildQuery(QueryCriteria criteria) {
        SQLBuilder sql = new SQLBuilder();

        sql.append("SELECT p.* FROM products p");

        if (criteria.hasCategory()) {
            sql.append("JOIN categories c ON p.category_id = c.id");
        }

        sql.append("WHERE 1=1");

        if (criteria.hasName()) {
            sql.append("AND p.name ILIKE :name");
        }

        if (criteria.hasCategory()) {
            sql.append("AND c.name = :category");
        }

        if (criteria.hasMinPrice()) {
            sql.append("AND p.price >= :minPrice");
        }

        if (criteria.hasMaxPrice()) {
            sql.append("AND p.price <= :maxPrice");
        }

        sql.append("ORDER BY p.created_at DESC");

        if (criteria.hasLimit()) {
            sql.append("LIMIT :limit");
        }

        return sql.toString();
    }

    public Map<String, Object> buildParameters(QueryCriteria criteria) {
        Map<String, Object> params = new HashMap<>();

        if (criteria.hasName()) {
            params.put("name", "%" + criteria.getName() + "%");
        }

        if (criteria.hasCategory()) {
            params.put("category", criteria.getCategory());
        }

        if (criteria.hasMinPrice()) {
            params.put("minPrice", criteria.getMinPrice());
        }

        if (criteria.hasMaxPrice()) {
            params.put("maxPrice", criteria.getMaxPrice());
        }

        if (criteria.hasLimit()) {
            params.put("limit", criteria.getLimit());
        }

        return params;
    }
}

4. Query Optimization Tips

@Service
public class QueryOptimizationService {

    // 1. Use EXISTS instead of COUNT for checking existence
    public boolean hasOrders(Long userId) {
        return em.createQuery("""
            SELECT CASE WHEN EXISTS (
                SELECT 1 FROM Order o
                WHERE o.user.id = :userId
            ) THEN true ELSE false END
            """)
            .setParameter("userId", userId)
            .getSingleResult();
    }

    // 2. Use JOIN FETCH for eager loading
    public List<Order> findOrdersWithItems() {
        return em.createQuery("""
            SELECT DISTINCT o FROM Order o
            JOIN FETCH o.items
            JOIN FETCH o.user
            WHERE o.status = 'ACTIVE'
            """, Order.class)
            .getResultList();
    }

    // 3. Use pagination for large result sets
    public Page<Order> findOrders(OrderCriteria criteria) {
        TypedQuery<Order> query = em.createQuery("""
            SELECT o FROM Order o
            WHERE o.status = :status
            ORDER BY o.createdAt DESC
            """, Order.class)
            .setParameter("status", criteria.getStatus())
            .setFirstResult(criteria.getOffset())
            .setMaxResults(criteria.getLimit());

        return new PageImpl<>(
            query.getResultList(),
            PageRequest.of(criteria.getPage(), criteria.getLimit()),
            countOrders(criteria)
        );
    }

    // 4. Use separate count query for pagination
    private long countOrders(OrderCriteria criteria) {
        return em.createQuery("""
            SELECT COUNT(o) FROM Order o
            WHERE o.status = :status
            """, Long.class)
            .setParameter("status", criteria.getStatus())
            .getSingleResult();
    }

    // 5. Use bulk operations for mass updates
    @Transactional
    public int updateOrderStatus(OrderStatus fromStatus,
                               OrderStatus toStatus) {
        return em.createQuery("""
            UPDATE Order o
            SET o.status = :toStatus
            WHERE o.status = :fromStatus
            """)
            .setParameter("fromStatus", fromStatus)
            .setParameter("toStatus", toStatus)
            .executeUpdate();
    }
}

5. Performance Testing

@SpringBootTest
public class QueryPerformanceTest {

    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private MeterRegistry registry;

    @Test
    public void testQueryPerformance() {
        // Warm up
        for (int i = 0; i < 10; i++) {
            orderRepository.findAll();
        }

        // Test
        Timer timer = registry.timer("query.performance.test");

        timer.record(() -> {
            List<Order> orders = orderRepository.findAll();
            assertFalse(orders.isEmpty());
        });

        // Assert performance
        assertTrue(timer.mean(TimeUnit.MILLISECONDS) < 1000,
            "Query took too long to execute");
    }

    @Test
    public void testBatchPerformance() {
        List<Order> orders = generateTestOrders(1000);

        Timer timer = registry.timer("batch.performance.test");

        timer.record(() -> {
            orderRepository.saveAll(orders);
        });

        // Assert performance
        assertTrue(timer.mean(TimeUnit.MILLISECONDS) < 5000,
            "Batch operation took too long to execute");
    }
}