On one fine day, a reporting query used by business analysts started taking 15+ minutes to execute after data growth. The query joined multiple large tables:

ORDERS (10M+ rows)

CUSTOMERS (2M+ rows)

PAYMENTS (8M+ rows)

Original Query

Step 1: Analyze Execution Plan

Using:

Findings:

Full Table Scans on ORDERS and PAYMENTS

High cost (~120K)

Nested Loop joins causing repeated scans

Root Cause

Missing indexes on join/filter columns

Optimizer choosing inefficient join method

Large data scan due to poor filtering

Step 2: Apply Indexing

Created indexes:

Result:

Execution time reduced to ~4 minutes

Index Range Scan used instead of Full Scan

Step 3: Rewrite Query

Optimized query:

Improvements:

Hash Join used instead of Nested Loop

Reduced repeated access

Execution time: ~1.5 minutes

Step 4: Gather Statistics

Why?

Optimizer depends on statistics for decision-making.

Final Optimization

Partitioned ORDERS table by date

Enabled parallel query

Final Execution Time:

-> Reduced from 15 minutes → 20 seconds

==> Key Takeaways

Always analyze execution plan first

Indexing is powerful but must be strategic

Query rewrite can outperform hardware scaling

Statistics are critical for optimizer decisions

Think in terms of data flow, not just SQL syntax

Conclusion

SQL tuning is not about guessing—it’s about measuring, analyzing, and optimizing step by step. Even small changes like indexing or join strategy can lead to massive improvements.

Thanks,

Jyoti.