N+1 Query Problem (SQL)

The classic N+1 query problem is one query to find a parent list, followed by N more queries for related rows. This demo assumes the order ids are already known: there is no extra user or parent fetch. If the app wants 10 orders and asks for them one by one, it performs 10 database round trips.

The repeated trips, query execution, and result transfers usually cost more than one larger query the database can optimize. When the ids are already known, the fix is often to ask for all of them in one WHERE id IN (...) query before shaping them into the structure the application wants.¹

Round trips for 10 known order ids

The database access pattern can impact application latency

Order IDs:[101, 102, 103, …, 110]

Individual lookups cost 25ms while a batch lookup costs 35ms; the rows are identical but batching avoids round-trip latency and database overhead

At a high level, the inefficient version moves the loop across the database boundary:

def n_plus_one(db, order_ids):
    orders = []

    for order_id in order_ids:
        order = db.query_one(
            "SELECT id, total FROM orders WHERE id = ?",
            [order_id],
        )
        orders.append(order)

    return orders

The batched version keeps the same output shape, but asks for all 10 orders in one query:

def batch_query(db, order_ids):
    return db.query(
        "SELECT id, total FROM orders WHERE id IN (?)",
        [order_ids],
    )

See PlanetScale's What is the N+1 Query Problem and How to Solve it?. ↩

N+1 Query Problem (SQL)

Footnotes