Fixing N+1 Queries in a Live Multi-Store System

N+1 is one of those problems that is invisible at small scale and obvious in production. The system had a report endpoint that worked fine in testing. In production with 100 stores, it was the slowest endpoint on the server.

Problem

The original code fetched the list of active stores in one query. Then it looped through the result and ran a separate aggregation query for each store to get that store's totals.

One query to get 100 stores. One hundred queries to get their totals. 101 round trips per page load.

At 30ms average query time, that is 3 seconds of serial database time per request, not counting network and processing overhead. Under concurrent load it was worse. Multiple managers opening the report at the same time means 303 or 404 database queries from a single endpoint in a few seconds.

I found it by logging query count per request. Seeing 101 in the log for one page load made the problem obvious. Without that log it would have just looked like the report is slow.

Solution

Rewrote the endpoint as a single JOIN query with GROUP BY.

The stores table joins to transactions, inventory, and deliveries. GROUP BY store_id aggregates the totals in a single pass. The database returns one row per store with all aggregated values included.

One query instead of 101. The database handles the aggregation, which is what databases are for.

Added the composite indexes at the same time:

• (store_id, transaction_date) on transactions for the GROUP BY aggregation
• (store_id, is_active) on stores for the initial filter

Without the indexes, the JOIN query would do full table scans. With them, it uses the indexed access path and runs efficiently.

Result

The report endpoint went from 101 queries per page load to 1.

Load time dropped proportionally. At 30ms average, 101 queries is 3+ seconds. One aggregation query for 100 stores is 30 to 40ms.

Concurrent load handling improved significantly. 10 managers opening the same report simultaneously now generates 10 database queries instead of 1010.

If your report endpoint response time does not match the apparent query complexity, check the query count first. N+1 is the most common cause.