The N+1 problem happens when code loads a set of parent records and then runs one more query for each parent to fetch related data. One query becomes N+1 queries. ORMs make this easy to write by hiding the extra queries behind ordinary attribute access, so the code looks harmless while the database receives a steady stream of small statements. Repeated per-parent queries are a problem worth fixing when they grow with your data, but the best fix depends on the ORM, the shape of the relationship, and the database. Fewer queries is not always faster.
How the pattern hides in ORM code
SQLAlchemy describes the failure directly: lazy access to a relationship across N loaded objects can emit N+1 SELECT statements, one for the original objects and one for each object’s unloaded relationship. Entity Framework Core documents the same behavior. After parent records are loaded, accessing related data lazily can issue another query for each parent, which the EF Core guidance identifies as a potential performance problem. In both cases the extra queries are not visible in the loop that causes them.
As an Amazon Associate I earn from qualifying purchases.
authors = session.scalars(select(Author).limit(40)).all()
for author in authors:
print(author.name, len(author.books)) # lazy load: one SELECT per authorNothing in that loop says “query.” The line author.books reads like a field lookup, but on an unloaded relationship it runs a database statement the first time it is touched for each author.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Count statements before you change anything
The reliable diagnosis is to compare the number of statements with the number of parent records. Work through it in this order:
#1 Best Overall
- Identify the request, endpoint, or function that renders or processes the related data. Diagnose at this level, not at the level of a single model method.
- Turn on statement logging. In SQLAlchemy,
create_engine(..., echo=True)writes emitted SQL to the log. In EF Core, enable the logging of executed database commands so each generated statement is visible during the request. - Run the same operation with two different parent set sizes, for example 10 and 40 authors.
- Record the statement count and the number of rows returned for each run. If the statement count rises by roughly one per parent, you have the N+1 pattern.
Using the 40-author page as an illustration: one query loads the authors, and one lazy query loads each author’s books. That is 41 statements. This is arithmetic on the documented pattern, not a measurement of any real application. Your numbers will differ, and the count is only the first signal.
Fix options compared
Three loading approaches cover most real cases. The table uses the 40-author example to show how their statement counts differ; the data-volume column is where the trade-offs appear.
| Approach | Statements for the 40-author example | What the database returns | Main risk |
|---|---|---|---|
| Lazy loading (the default in many ORMs) | 41 (one per author, plus the parent query) | Only the rows that are actually touched | A round trip for every parent, which multiplies latency |
| Joined eager loading | 1 (a single SQL statement with a join) | Parent columns repeated on every child row | Result volume grows, and unneeded relations get pulled in |
| Batched or select-in loading | 2 (the parent query, then one query for the related rows of all authors) | Each related row once, with no parent repetition | Composite-key limits on some backends; more statements than a join |
Joined eager loading
A join combines parents and children in one statement, so the query count drops to one. The cost is that each parent’s columns repeat on every child row. For an author with 300 books, the author’s name and biography are returned 300 times. A join is often a good choice when the relation is small, always needed, and returns a bounded number of children. It is a poor default for large collections or for relations the request never reads.
Batched and select-in loading
Batched loading first fetches the parents, then fetches the related rows for the whole set of parents in one additional query. Total statements stay constant as the parent count grows, and each related row is returned once. The trade-off is one more round trip than a join. In SQLAlchemy’s select-in strategy, composite primary keys have a documented limitation on backends that do not support tuple IN expressions; the SQLAlchemy relationship-loading page names SQL Server among them. Check that page for the backend you use before relying on select-in with composite keys.
Raising on unexpected lazy loads
SQLAlchemy’s raiseload option turns an unexpected lazy load into an error. Use it as a guardrail in code paths where a relation must be loaded explicitly. It does not fix anything by itself; it makes the next accidental N+1 fail in testing instead of in production.
Loading less
Sometimes the best fix is to stop loading the relation. If the page shows only the author’s name and a count, a query that returns that count directly, or a relation that is never accessed, removes the problem entirely. Loading only the columns and relations the request needs is the first thing to check, before choosing any eager strategy.
Why query count alone does not pick the winner
A lower statement count can hide a heavier workload. Joined loading may cut statements to one while multiplying row volume. Batched loading may add a statement while returning far less data. Eager loading can also fetch relations that the operation never uses; the TypeORM performance guidance warns that eager loading complex or unnecessary relations can create performance problems. Choose a strategy by comparing these five axes against your workload:
Recommended Free Tools
- SQL statement count and round trips.
- Complexity of the generated SQL, including joins and the query plan the database picks.
- Total rows and bytes fetched.
- Whether the relation is needed on this path at all.
- ORM and database constraints, such as composite keys and tuple IN support.
The weights depend on your data. A join that returns 40 small rows is cheap; the same join returning 40 parents with thousands of children each is not. Relative costs also depend on latency, so measure instead of assuming.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.When many small queries are acceptable
The SQLite project argues that many small queries can be efficient in its embedded architecture, because the database runs in the same process as the application and there is no network hop per statement. Client/server databases behave differently: each SQL statement involves a message round trip between the application and the server. A pattern that costs microseconds in an embedded database can cost milliseconds per statement across a network. That is why N+1 is best treated as a pattern to measure rather than a universal latency multiplier. Its impact depends on where the database runs and how far away it is.
Detection tools and guardrails
Beyond manual logging, a detector can catch the pattern during development. The nplusone project, for Python ORM integrations, detects potential lazy-load N+1 issues and also warns about eager loads whose data is never used. Before adopting it, check the project’s maintenance activity and confirm it supports your ORM version, because integrations are specific to the libraries they support. Pair any detector with the request-level counting described above, and with raiseload in code paths where lazy loads are never intended.
Measure the change
- Run the same operation at the same two parent sizes used for the baseline.
- Confirm the statement count is now constant, or grows far more slowly than the parent count.
- Compare rows returned. If a join multiplied the rows, the total data may be worse even with fewer statements.
- For the generated SQL that matters most, check the execution plan in your database (for example with EXPLAIN) to confirm the query is not now doing a costly scan.
- Compare response time under realistic latency between the application and database, not only on a local machine.
Loader behavior, logging options, and guardrail APIs can change between ORM major versions, so confirm the exact option names in the documentation for the version you run. The current references are the SQLAlchemy relationship loading guide, the EF Core efficient querying guide, and the SQLite note on many small queries. For detection, see the nplusone project, and for eager-loading cautions, the TypeORM performance guide.
Free tools Windows power users keep installed
One-click scans. No signup required.
Repeated per-parent queries are worth fixing when they grow with your data. Count the statements, pick the loading strategy that matches the relation’s shape and size, and let the measured result, not the statement count alone, decide.
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.
