The N+1 Problem
26 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)N+1 is the most common speed problem in ORM code. It looks innocent: a short loop. But each pass can send a query, and one hundred customers become one hundred and one queries. You can count them and prove it.
One query, then one more for every row
N+1 problem is the pattern where code runs one query to load N parent rows and then one more query for each parent to load its related rows, N+1 queries in all.
First you load a list of parents with one query. Then you loop, and each parent's lazy relationship runs its own query. That is 1 query plus N queries for N parents.
customers = session.scalars(select(Customer)).all()for customer in customers: print(customer.name, len(customer.orders))With 5 customers this sends 1 + 5 = 6 queries. With 5000 customers it sends 5001. Each query also costs a round trip to the database server.
Count the statements with an event listener
Event listener is a function you attach to a SQLAlchemy event, such as before_cursor_execute, so that SQLAlchemy calls it every time that event happens.
Do not guess. SQLAlchemy can call your function just before every statement it sends. A small helper collects them, so you can count them or read them.
@contextmanagerdef count_queries(engine): statements = [] def before(conn, cursor, statement, parameters, context, executemany): statements.append(statement) event.listen(engine, "before_cursor_execute", before) try: yield statements finally: event.remove(engine, "before_cursor_execute", before)Example 1 · Prove N+1 by counting
from contextlib import contextmanagerfrom sqlalchemy import event, create_engine, select, func, inspect, String, ForeignKeyfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session, selectinload, joinedload, raiseload
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) orders: Mapped[list["Order"]] = relationship( back_populates="customer", cascade="all, delete-orphan" )
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) total: Mapped[int] customer: Mapped["Customer"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def seed(): with Session(engine) as s: for i, name in enumerate(["Asha", "Ravi", "Meena", "John", "Sara"]): s.add(Customer(name=name, orders=[Order(total=100 * (i + 1)), Order(total=50)])) s.commit()
seed()
@contextmanagerdef count_queries(engine): statements = []
def before(conn, cursor, statement, parameters, context, executemany): statements.append(statement)
event.listen(engine, "before_cursor_execute", before) try: yield statements finally: event.remove(engine, "before_cursor_execute", before)
with Session(engine) as s, count_queries(engine) as statements: for c in s.scalars(select(Customer)).all(): len(c.orders)print(len(statements))print(sum("FROM orders" in sql for sql in statements))Output
6 5
What this code does
- The helper collected 6 statements: one for the customers and five for orders.
- Five of them read the
orderstable, one for each customer.
Use it as with count_queries(engine) as statements: and read len(statements) after the block. The listener is removed at the end, so it does not slow down other code.
Fix it, then keep it fixed
Query budget is the maximum number of SQL statements an endpoint is allowed to send. A test that asserts the count keeps N+1 from coming back.
Ask for the related rows up front. selectinload makes 2 queries for any number of parents, joinedload makes 1, and an aggregate query makes 1 when you only need a number. Then assert the count in a test.
stmt = select(Customer).options(selectinload(Customer.orders))with count_queries(engine) as statements: for c in session.scalars(stmt): len(c.orders)assert len(statements) == 2Example 2 · Fix with selectinload: 2 queries
from contextlib import contextmanagerfrom sqlalchemy import event, create_engine, select, func, inspect, String, ForeignKeyfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session, selectinload, joinedload, raiseload
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) orders: Mapped[list["Order"]] = relationship( back_populates="customer", cascade="all, delete-orphan" )
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) total: Mapped[int] customer: Mapped["Customer"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def seed(): with Session(engine) as s: for i, name in enumerate(["Asha", "Ravi", "Meena", "John", "Sara"]): s.add(Customer(name=name, orders=[Order(total=100 * (i + 1)), Order(total=50)])) s.commit()
seed()
@contextmanagerdef count_queries(engine): statements = []
def before(conn, cursor, statement, parameters, context, executemany): statements.append(statement)
event.listen(engine, "before_cursor_execute", before) try: yield statements finally: event.remove(engine, "before_cursor_execute", before)
with Session(engine) as s, count_queries(engine) as statements: stmt = select(Customer).options(selectinload(Customer.orders)).order_by(Customer.id) for c in s.scalars(stmt): print(c.name, len(c.orders))print(len(statements))Output
Asha 2 Ravi 2 Meena 2 John 2 Sara 2 2
What this code does
- All orders came in one extra query with an IN list of customer ids.
- The count stays 2 even if there were 5000 customers.
Example 3 · Fix with joinedload: 1 query
from contextlib import contextmanagerfrom sqlalchemy import event, create_engine, select, func, inspect, String, ForeignKeyfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session, selectinload, joinedload, raiseload
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) orders: Mapped[list["Order"]] = relationship( back_populates="customer", cascade="all, delete-orphan" )
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) total: Mapped[int] customer: Mapped["Customer"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def seed(): with Session(engine) as s: for i, name in enumerate(["Asha", "Ravi", "Meena", "John", "Sara"]): s.add(Customer(name=name, orders=[Order(total=100 * (i + 1)), Order(total=50)])) s.commit()
seed()
@contextmanagerdef count_queries(engine): statements = []
def before(conn, cursor, statement, parameters, context, executemany): statements.append(statement)
event.listen(engine, "before_cursor_execute", before) try: yield statements finally: event.remove(engine, "before_cursor_execute", before)
with Session(engine) as s, count_queries(engine) as statements: stmt = select(Order).options(joinedload(Order.customer)).order_by(Order.id).limit(4) print([(o.id, o.customer.name) for o in s.scalars(stmt)])print(len(statements))Output
[(1, 'Asha'), (2, 'Asha'), (3, 'Ravi'), (4, 'Ravi')] 1
What this code does
- Each order has one customer, so a join works well and needs no
unique(). - One statement loaded orders and customers together.
Example 4 · One aggregate query and a query budget
from contextlib import contextmanagerfrom sqlalchemy import event, create_engine, select, func, inspect, String, ForeignKeyfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session, selectinload, joinedload, raiseload
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) orders: Mapped[list["Order"]] = relationship( back_populates="customer", cascade="all, delete-orphan" )
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) total: Mapped[int] customer: Mapped["Customer"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def seed(): with Session(engine) as s: for i, name in enumerate(["Asha", "Ravi", "Meena", "John", "Sara"]): s.add(Customer(name=name, orders=[Order(total=100 * (i + 1)), Order(total=50)])) s.commit()
seed()
@contextmanagerdef count_queries(engine): statements = []
def before(conn, cursor, statement, parameters, context, executemany): statements.append(statement)
event.listen(engine, "before_cursor_execute", before) try: yield statements finally: event.remove(engine, "before_cursor_execute", before)
def order_totals(session): stmt = ( select(Customer.name, func.sum(Order.total)) .join(Customer.orders) .group_by(Customer.id, Customer.name) .order_by(Customer.id) ) return session.execute(stmt).all()
with Session(engine) as s, count_queries(engine) as statements: rows = order_totals(s)assert len(statements) <= 1, "too many queries"print(rows[:3])print("within budget:", len(statements))Output
[('Asha', 150), ('Ravi', 250), ('Meena', 350)]
within budget: 1What this code does
- The database added up the totals, so no order objects were created.
- The
assertis the query budget. It would fail if a lazy loop was added.
If someone later removes the option, the test fails at once. An aggregate such as func.count() with group_by is best when the page only shows totals, because no order objects are loaded at all.
Next step
Check your understanding
11 quick questions