# How I Cut SQL Query Time from 45 Seconds to 8 Seconds

When I joined the data team at an automotive parts company, the inventory query everyone relied on took 45 seconds to run. Real-time reporting was impossible.

This is how I brought that query down to 8 seconds.

The Problem

The database had an inventory table with 2.3M+ rows. The query was doing full table scans on every execution.

The Fix

1. Read the execution plan - Found correlated subqueries running 500+ times

2. Replaced subquery with CTE:

sql
- 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 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