The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
The N+1 query problem happens when an ORM loads a list of N parent records with one query, then issues another SELECT for each parent as the code touches a lazy-loaded relationship. A page that lists 100 authors and their books can quietly become 101 round trips to the database. The usual fix is eager loading, but the goal is not “one query at any cost.” The goal is a number of round trips, SQL shape, and data volume that your workload can afford.
What is the N+1 query problem?
The pattern has two steps. First, the application fetches a collection of N parent objects. Second, it accesses a lazy relationship on each of those objects. The ORM emits the initial query and then one additional SELECT per object, as each relationship is touched for the first time. That is where the name comes from: one query plus N more.
The SQLAlchemy 2.1 documentation, in its “Relationship Loading Techniques” section, describes this directly:
“The
lazyload()strategy produces an effect that is one of the most common issues referred to in object relational mapping; the N plus one problem, which states that for any N objects loaded, accessing their lazy-loaded attributes means there will be N+1 SELECT statements emitted.”
A simplified illustration of the SQL pattern, with authors and books, looks like this:
SELECT id, name FROM authors;
SELECT id, author_id, title FROM books WHERE author_id = 1;
SELECT id, author_id, title FROM books WHERE author_id = 2;
SELECT id, author_id, title FROM books WHERE author_id = 3;
-- ...one more statement for every remaining authorThe giveaway is the shape: the same SELECT repeats, differing only in a foreign-key value, and the count grows with the number of parent rows.
Lazy loading is not the bug by itself
Lazy loading can be the right behavior. If a relationship is never read, not loading it avoids fetching data nobody uses. The problem appears when code repeatedly reads a relationship across a whole result set. The nplusone project, a Python library devoted to this issue, makes the same distinction between lazy loads that are harmless and lazy loads that repeat across many objects. Treat the relationship access pattern, not the mapping setting alone, as the thing to fix.
Free tools Windows power users keep installed
One-click scans. No signup required.
How do I detect N+1 queries?
Detection is a measurement exercise. The steps below work for any ORM that can log SQL, and the SQLAlchemy-specific commands are shown where they apply.
- Reproduce with realistic data. A list of three test rows hides the problem, because three extra queries look like nothing. Use a result set with dozens or hundreds of parents, similar to production.
- Turn on SQL logging. In SQLAlchemy, create the engine with
echo=True, or set thesqlalchemy.enginelogger toINFOin your logging configuration. The SQLAlchemy performance FAQ in the 1.4 documentation notes that logging can reveal dozens or hundreds of queries that could be organized into fewer queries. - Look for repeated statements. Group the log by statement text with the parameter values removed. An N+1 pattern shows one statement appearing many times with a different key each time.
- Trace the repeated SELECTs back to code. The usual location is a relationship access inside a loop, a serializer, a template, or another object traversal. This is a practical inference from how lazy loading works, so confirm it in your own application. Not every burst of queries is N+1; some are unrelated per-row lookups or genuinely different statements.
- Measure before and after. Record the query count and response time for the same request with representative data, then repeat after the change. Do not claim a performance gain without that comparison.
For automated checks, the nplusone project can flag lazy loads during test runs, which catches regressions that manual log reading would miss.
How do I fix N+1 queries?
Eager loading tells the ORM which relationships to fetch as part of the original operation. It does this either by joining related rows into the main result or by issuing a separate batched SELECT. Eager loading does not always mean exactly one query.
Selectin loading: batched follow-up query
Selectin loading runs the parent query, then one additional SELECT that fetches the children for all loaded parents at once. According to the SQLAlchemy 2.1 documentation, selectin loading is generally the simplest and most efficient strategy for one-to-many and many-to-many collections.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →from sqlalchemy import select
from sqlalchemy.orm import selectinload
stmt = select(Author).options(selectinload(Author.books))
authors = session.scalars(stmt).all()Expected SQL: one SELECT for the authors, then one SELECT for the books whose author_id is in the set of loaded authors. The total stays fixed regardless of how many authors are returned.
Rank #3
One documented limitation: selectin loading with composite primary keys requires tuple IN support, which some backends lack, including SQL Server. Check the current SQLAlchemy guide and your database version before relying on it for such models.
Joined loading: JOIN in the main query
Joined loading pulls the related rows through a JOIN in the main statement. SQLAlchemy 2.1 describes it as generally the most general-purpose strategy for many-to-one references.
from sqlalchemy import select
from sqlalchemy.orm import joinedload
stmt = select(Book).options(joinedload(Book.author))
books = session.scalars(stmt).all()Expected SQL: a single statement containing a JOIN to the authors table. The trade-off is that parent data repeats across rows and the SQL becomes more complex. If you use joinedload on a collection, SQLAlchemy 2.x requires calling .unique() on the result before collecting it.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteComparing the strategies
| Strategy | SQL shape | Documented fit | Main cost or limit |
|---|---|---|---|
| Lazy loading (default) | One SELECT per parent, issued when the attribute is accessed | Relationships that are rarely read | N+1 when iterated across many parents |
Selectin loading (selectinload) |
Parent query plus one batched SELECT for the relationship | One-to-many and many-to-many collections (SQLAlchemy 2.1: generally simplest and most efficient) | Composite primary keys need tuple IN support; not available on some backends, including SQL Server |
Joined loading (joinedload) |
One statement with a JOIN | Many-to-one references (SQLAlchemy 2.1: generally the most general-purpose) | Parent data repeats per row; SQL is more complex; collections need .unique() |
Raise on access (raiseload) |
No load; accessing the attribute raises an error | Guarding development and test paths | Code must load everything it reads |
Compare strategies on relationship cardinality, number of SQL executions, row duplication and total data fetched, SQL complexity, backend support, and latency with representative data. The SQLAlchemy documentation supports reasoning about query count, query complexity, fetched data, and the composite-key limitation; latency has to be measured in your own environment.
Hibernate: JOIN FETCH in JPQL
The same failure mode appears in Hibernate. The Hibernate ORM 5.1 best-practices guide, an older version used here only as an example, warns that failing to JOIN FETCH an eager association in a JPQL query can lead to secondary statements and N+1 issues:
select a from Author a join fetch a.booksConfirm the syntax and recommendations against current Hibernate documentation before applying them to a newer version.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.When “one query” is the wrong goal
Collapsing everything into one statement is not always better. A JOIN that multiplies parent rows, or a batched load that pulls thousands of children to display three, can cost more than the extra round trips it removes. Work through these questions in order:
- Is the relationship read for every parent? If not, leave it lazy. Eager loading an unused relationship adds cost without benefit.
- Do you need the whole collection? If the page shows a count, a summary, or a limited number of children, write a query for exactly that. Loading full objects just to count them is a common cause of slow pages.
- Is the relationship a reference or a collection? For many-to-one references, joined loading is the usual first choice. For collections, selectin loading is the usual first choice, unless composite keys or your backend rule it out.
- Does the chosen strategy still look right after measuring? Compare the query count, generated SQL, and response time. Keep the change only if the measured path improves.
Guard against regressions with raiseload
Once a path is fixed, it is easy for a later change to reintroduce a lazy access inside a loop. SQLAlchemy’s raiseload option makes an unloaded attribute raise an informative error instead of silently issuing a query:
from sqlalchemy import select
from sqlalchemy.orm import raiseload
stmt = select(Author).options(raiseload(Author.books))
authors = session.scalars(stmt).all()
# Accessing author.books now raises an error instead of emitting a SELECTUse it in tests or development code paths where you want unexpected lazy access to fail loudly. It is less suitable for production code paths where a missing relationship would break a request, unless you have deliberately loaded everything the path needs.
The fix is complete when the query count for the measured path stays flat as the result set grows, and when the code that loads the data is the same code that reads it.
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.
Recommended Free Tools
