Implementing Query Optimization
1. Writing Efficient Queries
| Practice | Detail |
|---|---|
| Project only needed columns | Avoid SELECT * |
| Filter early | WHERE before JOIN where possible |
| Use indexes | Match leading columns |
| Limit rows | LIMIT/pagination |
| Avoid functions on indexed cols | Breaks index usage |
2. Using Query Hints
| Engine | Hint |
|---|---|
| Oracle | /*+ INDEX(t idx) */ |
| MySQL | USE INDEX(idx), STRAIGHT_JOIN |
| PostgreSQL | pg_hint_plan extension |
| JPA | @QueryHint for fetch size, timeout |
Warning: Hints lock you into a plan; revisit when stats change.
3. Implementing Batch Fetching
| Mechanism | Detail |
|---|---|
Hibernate @BatchSize | Group lazy loads into IN clause |
JDBC setFetchSize | Server-side cursor |
Spring Data EntityGraph | Eager fetch specific paths |
4. Implementing Query Result Caching
| Layer | Tool |
|---|---|
| App | Caffeine, @Cacheable |
| ORM | Hibernate 2nd-level + query cache |
| Distributed | Redis |
| DB | Materialized views |
5. Implementing N+1 Query Prevention
Example: JOIN FETCH
// BAD: orders.forEach(o -> o.getLines()) // each triggers a query
// GOOD:
@Query("select o from Order o join fetch o.lines where o.status = :s")
List<Order> findOpenWithLines(@Param("s") String status);6. Implementing Projection Queries
Example: DTO projection
@Query("select new com.acme.OrderSummary(o.id, o.totalCents) from Order o where o.status = :s")
List<OrderSummary> summarize(@Param("s") String status);7. Implementing Native Queries
| Use | Detail |
|---|---|
| DB-specific features | JSONB, window functions, CTEs |
| Performance | Hand-tuned SQL |
| JPA | @Query(nativeQuery=true) |
| Trade-off | Loses portability |
8. Analyzing Query Execution Plans
| Tool | Command |
|---|---|
| PostgreSQL | EXPLAIN (ANALYZE, BUFFERS) ... |
| MySQL | EXPLAIN ANALYZE ... (8.0+) |
| Oracle | EXPLAIN PLAN FOR ... |
| Look for | Seq Scan on big table, mismatched estimates |
9. Optimizing Subqueries
| Pattern | Replacement |
|---|---|
| Correlated subquery | JOIN or LATERAL |
IN (subquery) | EXISTS or JOIN |
| Repeated subquery | CTE / temp table |
10. Using Criteria API
Example: Type-safe Criteria
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Order> q = cb.createQuery(Order.class);
Root<Order> root = q.from(Order.class);
q.select(root).where(cb.equal(root.get("status"), "OPEN"));
em.createQuery(q).getResultList();11. Implementing Query Pagination
| Style | Detail |
|---|---|
| Offset | Slow for large offsets |
| Keyset (seek) | WHERE (placed_at, id) < (?, ?) ORDER BY ... LIMIT n |
| Window function | ROW_NUMBER() OVER ... |
12. Implementing Query Indexing Strategy
| Step | Detail |
|---|---|
| Profile real workload | pg_stat_statements |
| Match WHERE/JOIN/ORDER | Composite index columns in that order |
| Cover hot SELECTs | INCLUDE non-key columns |
| Drop unused | pg_stat_user_indexes idx_scan = 0 |
| Beware write cost | Each index slows INSERT/UPDATE |