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 occurs when an application runs one query to fetch a set of records, then runs another query for each record to load related data. The fix is to make relationship loading intentional: fetch what the operation needs up front, or select only the fields it needs, then inspect the generated SQL and measure the result. Reducing query count helps only when it also improves the work your database and application actually perform.
What is the N+1 query problem?
Suppose an application loads a list of blogs, then reads each blog’s posts in a loop. If the ORM lazily loads posts when the property is accessed, the application first runs one query for the blogs and then one more query per blog. With N blogs, that is one initial query plus N follow-up queries—the source of the name.
The code may look like ordinary property access, but each access can trigger a database roundtrip. Microsoft’s EF Core documentation describes this pattern and warns that it can cause very significant performance issues: Efficient Querying – EF Core. How much it matters depends on the number of records, database latency, relationship shape, and the work performed by each query; N+1 is a query-count pattern, not a universal benchmark.
Free tools Windows power users keep installed
One-click scans. No signup required.
Why is my ORM making so many database queries?
Many ORMs support lazy loading: related data is fetched only when code accesses a relationship. That can be convenient when a relationship is rarely needed. But when code accesses the relationship for every item in a collection, the ORM may quietly issue a query for each item.
#1 Best Overall
Other patterns can also produce a high query count, so do not assume every many-query trace is N+1. Check whether a query repeats with different parent identifiers as a loop processes records. Database command logging, ORM diagnostics, and a profiler can reveal the SQL statements and their parameters. EF Core’s documentation cautions that lazy loading can lead to unnecessary roundtrips: Lazy Loading of Related Data – EF Core.
How do I fix N+1 queries?
- Find the repeated relationship access. Trace the code that processes the parent records and note which related properties or collections it reads.
- Choose the data the operation actually needs. If the response or calculation needs related records for the whole parent set, load them deliberately. If it needs only a few fields, project those fields rather than loading complete objects.
- Choose a loading strategy that fits the relationship. A join, a separate batched query, or a projection may be appropriate depending on cardinality and the ORM.
- Inspect the generated SQL and result shape. Confirm the per-parent query pattern is gone, and check how many statements, rows, and columns are returned.
- Measure with representative data and workload. Compare elapsed time, database execution plans, memory use, and consistency requirements—not query count alone.
EF Core: Include or project the needed fields
EF Core distinguishes eager loading, explicit loading, and lazy loading. Eager loading requests related data as part of the initial query; explicit loading fetches it later through a separately issued query; lazy loading fetches it when a navigation property is accessed. For a known response shape, use eager loading with Include or project the fields required by the operation. Microsoft’s guidance explains these approaches and the risks of lazy loading: Loading Related Data – EF Core and Efficient Querying – EF Core.
Loading multiple collections through joins can repeat parent columns across many result rows. EF Core split queries fetch collections through separate SQL statements, which may reduce that duplication, but they add roundtrips. Depending on the query and provider, buffering and changes made between statements can also matter. Compare the options for your workload rather than treating either as the default winner: Single vs. Split Queries – EF Core. Exact API behavior can vary by EF Core version and database provider.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteSQLAlchemy: selectinload or joinedload
SQLAlchemy’s 2.1 relationship-loading guide describes lazy loading as a common cause of N+1 SELECTs. selectinload() issues additional SELECT statements that load related rows using parent identifiers in an IN clause; it is not necessarily one SQL statement, but it can replace one query per parent with a controlled batched load. joinedload() uses a JOIN in the main statement. The guide presents select-in loading as a generally simple, efficient option for collections and joined loading as a general-purpose option for many-to-one relationships. Composite primary keys and backend support can affect whether select-in loading is applicable. Relationship Loading Techniques — SQLAlchemy 2.1 Documentation.
Rank #3
SQLAlchemy also provides raiseload(), which makes an unexpected lazy relationship access raise an error. This can help expose accidental loads during development or testing instead of allowing them to hide in a request path. Choose the loader strategy for the relationship and query shape, then verify the emitted SQL.
Django: select_related or prefetch_related
Django’s select_related() joins related fields into the SQL SELECT. prefetch_related() performs separate relationship lookups and combines the results in Python. They therefore use different loading patterns; select the one suited to the relationships involved and inspect the resulting queries. See the Django QuerySet API reference.
Hibernate: use an association-fetching strategy deliberately
Hibernate’s guide describes N+1 as a query for a list followed by N queries for associated instances, and discusses association-fetching strategies to avoid the pattern. The precise choice and API depend on the Hibernate version and mapping, so consult the guide for the version in your application rather than copying a strategy from a different release: A Short Guide to Hibernate 7.1.
One query is not always faster
A JOIN can reduce roundtrips, but it can also return repeated parent data. Joining several collections at once may multiply rows: each combination of related items can appear in the result, creating a cartesian expansion. Separate or split queries can limit that duplication, but require extra statements and roundtrips. Multiple statements may also have consistency implications if the data changes between them, and some query shapes require buffering.
Best Value
Compare the strategies across the factors that affect your application:
- Statements and roundtrips: Does the strategy eliminate per-parent queries, and how many statements remain?
- Rows and duplicated data: Do joins repeat parent columns or expand combinations across collections?
- Data fetched: Are you loading only the relationships and columns the operation uses?
- Database work: Is the SQL manageable, and does the execution plan use appropriate indexes?
- Application resources: How much memory does materializing or buffering the result require?
- Correctness: Can related records change between statements, and does the operation require a consistent view?
- Relationship and backend limits: Does cardinality, a composite key, or database support rule out a strategy?
Measure with representative data and realistic database latency. A lower query count is a useful signal, not proof of a faster or safer design; no single loading strategy is best for every workload.
Quick Recap
How to prevent N+1 regressions
- Make relationship access visible in code review, especially inside loops over database results.
- For endpoints and jobs with a known data shape, define the relationships or projected fields they need instead of relying on incidental lazy loads.
- Use ORM logging or query-count checks in tests to catch repeated statements, while avoiding brittle assertions that assume a specific count if the ORM or query plan legitimately changes.
- Recheck generated SQL after changes to mappings, ORM versions, providers, or data shapes.
- Test with enough parent and related records to expose query patterns that a tiny fixture can conceal.
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.
