The N+1 query problem happens when an ORM runs one query to load a list of objects and then runs one more query for each object as code reads a related attribute. The result is a page or job that looks fine in a small test but issues hundreds of statements in production. The fix is usually to tell the ORM, before the loop starts, which related rows you need. It is not to force every request down to a single SQL statement.
What is the N+1 query problem?
The name describes the count. You fetch a collection of N parent objects, then access a lazy-loaded relationship on each one. The first query returns the parents. Each relationship access then emits its own SELECT, so the total is 1 + N statements. The SQLAlchemy 2.1 documentation, in its section “Relationship Loading Techniques,” describes this as one of the most common issues in object relational mapping: “for any N objects loaded, accessing their lazy-loaded attributes means there will be N+1 SELECT statements emitted.”
As an Amazon Associate I earn from qualifying purchases.
The problem is the repeated access, not the lazy loading itself. Lazy loading is a sensible default. If your code loads a user and never touches the user’s addresses, the ORM never fetches them, and that is a saving. The trouble starts when a loop, a serializer, or a template walks a whole result set and touches the same relationship on every row. The nplusone project, a Python library for detecting this pattern, makes the same distinction between lazy access that is never used and lazy access that repeats across a collection.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →The SQL pattern to recognize
Consider a simple model where each user has many email addresses. The mapping below uses SQLAlchemy 2.0-style declarative syntax, which SQLAlchemy 2.1 continues to use.
#1 Best Overall
class User(Base):
__tablename__ = "user_account"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
addresses: Mapped[list["Address"]] = relationship(back_populates="user")
class Address(Base):
__tablename__ = "address"
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
email: Mapped[str]
user: Mapped["User"] = relationship(back_populates="addresses")The code below looks harmless, and it is the usual source of the problem:
users = session.scalars(select(User)).all()
for u in users:
print(u.name, [a.email for a in u.addresses])With 200 users, the log shows the following pattern (simplified here, with bind parameters omitted):
SELECT user_account.id, user_account.name FROM user_account
SELECT address.id, address.user_id, address.email FROM address WHERE :param = address.user_id
SELECT address.id, address.user_id, address.email FROM address WHERE :param = address.user_id
... repeated 200 times ...The line that gives it away is a single table repeated with the same shape and a different key each time. Every repeat is a round trip to the database, and each one adds network latency and parsing cost on top of the work the database does.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →How do I detect N+1 queries?
Detection is a matter of counting statements for one realistic path, then tracing the repeats back to code. Use this order:
- Reproduce the path with realistic data. A list of three rows will hide the problem. Use a result set size close to what production sees, or a seeded copy of the table.
- Count or log the statements. Turn on SQL logging or attach a counter (both are described below). The SQLAlchemy 1.4 performance FAQ notes that logging can reveal dozens or hundreds of queries that could often be organized into fewer.
- Find the code that triggers the repeats. Look for a relationship attribute read inside a loop, a list comprehension, a serializer, or a template that iterates over parent objects. This is an inference from how lazy loading works, so confirm it in your own code path; a burst of queries is not proof of N+1 on its own.
- Record the baseline. Note the statement count and response time for the path before you change anything.
Turn on SQL logging
SQLAlchemy can echo every statement it sends. Pass echo=True to create_engine() for a quick look, or configure the logger in production-like test environments:
import logging
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)Read the output for the same table name appearing many times in a row. Those repeats are the signature you are looking for.
Count statements in a test
For a regression check, count executions rather than reading logs by eye. This listener increments a counter for each statement the engine executes:
from sqlalchemy import event
statement_count = 0
@event.listens_for(engine, "before_cursor_execute")
def count_statements(conn, cursor, statement, parameters, context, executemany):
global statement_count
statement_count += 1Reset the counter at the start of the code path, run it, and assert on the total. Keep the assertion loose enough to survive normal changes, but tight enough to fail when the count grows with the number of rows.
Use a detection library where the framework supports it
The nplusone project provides detection for Python ORMs, including SQLAlchemy, and reports lazy loads that happen inside a loop. Check its current maintenance status and supported versions before adopting it, since the project’s claims describe the project itself rather than an independent benchmark.
How do I fix N+1 queries?
The fix is eager loading: you tell the ORM which relationships the operation needs, and it fetches them as part of the same operation. In SQLAlchemy, that is an option on the query. The strategy you choose changes the statement count and the shape of the SQL, so pick it from the relationship type and the data you will read.
Selectin loading for collections
For a one-to-many or many-to-many collection, the SQLAlchemy 2.1 documentation says selectin loading is generally the simplest and most efficient strategy. It runs a second query that fetches all related rows for the loaded parents in a batch:
from sqlalchemy import select
from sqlalchemy.orm import selectinload
users = session.scalars(
select(User).options(selectinload(User.addresses))
).all()
for u in users:
print(u.name, [a.email for a in u.addresses])The loop now reads from memory. The log shows two statements: one for user_account and one for address with an IN list of user IDs (simplified here). The count no longer depends on how many users you load.
Rank #3
Joined loading for many-to-one references
For a many-to-one reference, the same documentation says joined loading is generally the most general-purpose strategy. It adds a JOIN to the parent query so the related row comes back with it:
from sqlalchemy.orm import joinedload
addresses = session.scalars(
select(Address).options(joinedload(Address.user))
).all()A JOIN keeps this to one statement, but it repeats the parent’s columns once per child row when the relationship is a collection. Use it for many-to-one references, and check the generated SQL before using it on a collection.
Eager loading in Hibernate (older example)
The Hibernate ORM 5.1 best-practices guide makes the same point in its own terms. If a JPQL query does not JOIN FETCH an eager association, the ORM can issue secondary statements and reproduce the N+1 pattern. The example below is from that 5.1 guide and shows the idea, not current Hibernate syntax:
Free tools Windows power users keep installed
One-click scans. No signup required.
select p from Parent p join fetch p.childrenConfirm the current Hibernate documentation for your version before copying this into a codebase.
Compare the strategies
| Approach | Statements for N parents | Where it fits (per SQLAlchemy 2.1 guidance) | Trade-off |
|---|---|---|---|
| Default lazy loading | 1 + N (one per parent whose relationship is read) | Relationships that are rarely read | Repeated round trips when a loop reads the relationship |
| Selectin loading | 2 for a single parent query (the child query is batched) | One-to-many and many-to-many collections | An extra query; the batched IN list can be large with many parents |
| Joined loading | 1 (related rows joined into the parent query) | Many-to-one references | Parent data repeats per child row for collections; SQL is more complex |
| Raiseload (guard) | Not a loading strategy; it fetches nothing | Development and test paths that must not lazy-load | Code that legitimately needs the attribute must load it explicitly |
The statement counts in this table describe the query pattern. Measure real latency and row counts on your own data before deciding; the documentation does not publish benchmarks for these choices.
Check the backend limitation for composite keys
The SQLAlchemy 2.1 loading guide documents a limitation: selectin loading against a composite primary key needs tuple IN support, and some backends, including SQL Server, do not provide it. On those backends, use joined loading or a different query. Check the current guide and your database version before assuming selectin works for every mapping.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Guard against regressions with raiseload
Fixing one endpoint does not stop the next developer from reintroducing the loop. SQLAlchemy’s raiseload() option makes an unloaded attribute raise an informative error when it is accessed, instead of quietly emitting a SELECT:
Recommended Free Tools
from sqlalchemy.orm import raiseload
users = session.scalars(
select(User).options(raiseload(User.addresses))
).all()
for u in users:
print(u.addresses) # raises instead of issuing a queryUse it in development or test paths where the code is supposed to load everything it reads. Do not enable it on production paths that legitimately render lazy data, because it turns a slow request into a failed one.
When one query is the wrong goal
Eliminating every extra statement is not the target. A single JOIN can be slower than two batched queries when it duplicates large parent rows or produces a result set the database must sort and filter. A paginated list that reads a relationship on only the first few rows may be better served by lazy loading those rows, not by fetching every child of every parent. Some administrative jobs process one row at a time on purpose to keep memory bounded.
A workable rule: fix N+1 when the statement count grows with the number of rows and the relationship is read for most of them. Leave lazy loading alone when the relationship is rarely used, when the result set is small and bounded, or when a deliberate per-row query keeps memory or locking under control. In each case, compare the query count, the generated SQL, the fetched data, and the response time before and after the change.
- Count statements for the real path with realistic row counts.
- Inspect the generated SQL for each eager strategy.
- Measure latency and memory, not just statement counts.
- Add a regression check that fails when the count scales with rows.
The silent part of this problem is that nothing fails. The database answers each query correctly, and the code works. The cost shows up as latency that grows with data, which is why counting statements on a realistic path is the most reliable early warning.
Framework details in this article reflect the SQLAlchemy 2.1 documentation, the SQLAlchemy 1.4 performance FAQ, and the Hibernate ORM 5.1 guide; check the current documentation for your version before applying version-specific syntax.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
