⚡ SQL Performance Optimization

Inventory queries were taking 45 seconds to run. Real-time reporting was impossible. Analysts relied on morning snapshots because live queries were too slow.

Diagnosed issues using execution plans. Found correlated subqueries running 500+ times per query, implicit type conversions, and missing indexes.

Replaced correlated subqueries with CTEs, fixed type mismatches, and created composite covering indexes.

-- Before: 45 seconds
SELECT * FROM inventory i
WHERE (SELECT MAX(date) FROM orders o 
       WHERE o.sku_id = i.sku_id) IS NOT NULL

-- After: 8 seconds with CTE + index
WITH latest_orders AS (
    SELECT sku_id, MAX(date) as last_order
    FROM orders GROUP BY sku_id
)
SELECT i.* FROM inventory i
JOIN latest_orders lo ON i.sku_id = lo.sku_id

• Defem Autocare: 45 seconds → 8 seconds (82% faster)
• Federal Ministry of Works: 15+ minutes → 30 seconds (97% faster)
• Logical reads: 84,000 → 4,200 (94% reduction)
• Real-time reporting: Enabled for the first time

Always read the execution plan before writing fixes. The most expensive queries are often not the complex ones — they're simple queries running against poorly designed schemas.