optimize-a-slow-query

name	optimize-a-slow-query
category	code
description	Measure and improve a slow database query while preserving result correctness, concurrency behavior, and operational safety. Use when a query, report, endpoint, or job is too slow, consumes excessive resources, or needs an evidence-backed indexing or rewrite decision.

optimize-a-slow-query

Optimize from a representative execution plan and workload, not from query appearance. Protect result equivalence and write cost while changing the smallest proven bottleneck.

Inputs

• Capture the query, parameters, schema, indexes, database version, row counts, and caller.
• Record a representative workload, latency distribution, resource pressure, and freshness requirements.
• Use a production-shaped safe environment before running expensive analysis or changes.

Procedure

1. Reproduce the slow case with representative parameters and record result count, latency, reads, memory, and plan.
2. Verify the query returns the intended rows. Save a result-equivalence fixture before changing it.
3. Inspect the execution plan for scans, estimates, joins, sorts, spills, repeated work, filters, and lock behavior.
4. Check data distribution and stale statistics. Compare fast and slow parameter shapes before assuming one plan fits all.
5. Identify one bottleneck supported by measurements.
6. Choose the smallest intervention: predicate correction, query rewrite, precomputation, pagination, statistics update, or index.
7. For an index, estimate size, build time, write amplification, maintenance, selectivity, and overlap with existing indexes.
8. Apply the change in the safe environment and repeat the same workload several times.
9. Compare result sets, median and tail latency, reads, CPU, memory, plan stability, and affected write performance.