Query Optimization

Overview

Strategies for optimizing security-filtered queries.

Fetch Strategies

Eager Fetch Joins

@Query("SELECT DISTINCT e FROM Event e " +
       "LEFT JOIN FETCH e.races " +
       "WHERE e.orgId IN :orgIds")
List<Event> findAllInOrgsWithRaces(@Param("orgIds") Set<Long> orgIds);

Batch Fetching

@BatchSize(size = 25)
@OneToMany(mappedBy = "event")
private Set<Race> races;

Query-Level Filtering

// Security applied at database level
SELECT e FROM Event e
WHERE e.orgId IN (10, 20, 30)  -- User's accessible orgs

// Not in application memory

Pagination Without Count

public Slice<Event> findSliceByCriteria(
        EventCriteria criteria,
        Pageable pageable) {
    // Uses Slice instead of Page to avoid expensive count query
    return eventRepository.findAll(spec, pageable);
}

Performance Monitoring

@Around("execution(* *QueryService.findByCriteria(..))")
public Object monitorQueryPerformance(ProceedingJoinPoint pjp) {
    long start = System.currentTimeMillis();
    Object result = pjp.proceed();
    long duration = System.currentTimeMillis() - start;

    if (duration > 1000) {
        log.warn("Slow query: {}ms", duration);
    }
    return result;
}