The N+1 Trap

The same loop, three loading strategies, and a hundredfold difference in cost.

Teach with MAGE
ECE 30861 · Act III
Data Management

Engineering problem How does the loading strategy decide how many queries a loop becomes?

A three-line loop prints each reservation's room name. The lines never change. What changes is the loading strategy the data-access layer applies and whether a cache answers a repeated lookup — and between them they decide whether the page costs one database operation or two hundred and one.

Background

A database holds information and an application reaches it with queries. Between the two sits a data-access layer — in most services an object-relational mapper, which presents stored rows as ordinary objects. The convenience is real and so is the cost: once a row is an object, reading one of its attributes looks exactly like reading any other attribute, and the source code stops saying whether that read reaches the network.

A mapper has to decide when to fetch a related row, and there are three common answers. Lazy loading fetches it at the moment the attribute is read, so each reservation's room costs a query of its own. Eager loading fetches the reservations and their rooms together, in one join. Batched loading fetches the reservations, collects the room identifiers the result will need, and asks for all of them in a single second query. All three return the same rows. The strategy is configuration — an annotation, a mapper default, one argument to a query builder — and it is usually set a long way from the loop it governs.

Mappers also keep an identity map: a record, within one request, of the rows already loaded. With one in place a repeated room lookup is answered from memory instead of the network, so the query count follows the number of distinct rooms rather than the number of reservations. Two hundred reservations drawn from ten rooms cost eleven queries. The same two hundred drawn from two hundred different rooms cost two hundred and one, and no cache can help.

What makes a query expensive here is not the work the database does. It is the round trip: a request leaves the process, crosses a network, and the process waits. Under lazy loading those waits cannot overlap, because the loop cannot ask for a room it has not reached yet. So the elapsed time is the query count multiplied by one round-trip latency, and the count is the term that grows with the size of the result.

That division decides where an engineer spends. Round-trip latency is a property of the deployment — the network, the region, the pool — and it is bought rather than written. The query count is a property of the access pattern, and it is the one the engineer changes. A tenfold faster network and a change of loading strategy can cut the same response time; only one of them is in the source.

None of this is a correctness question. The loop passes its tests under every configuration on this page: the rows returned are identical and the printed output is the same. A program can be right and still be expensive, which is why a query log or a mapper's echo mode — rather than a code review — is the evidence that finds this defect.

Model

Figure 1: what the same three lines become under four loading configurations At the top, three lines of program: load the reservations, loop over them, and print each reservation's room name. An arrow marks the third line as the one a lazy loader turns into a query, noting that it reads like a field access and may be a round trip, and a line above states that all four configurations below return the same rows. Below, four panels compare what the loop becomes. The first, lazy loading with the cache off, shows one query that fetches the reservations and then a fan of separate queries, one per reservation, drawn in the failure colour; it totals one plus N queries, 201 at two hundred reservations. The second, lazy loading with the cache on, shows the same first query and a fan of only two solid room queries beside two dashed outlines marking the lookups the identity map answered from memory; it totals one plus D queries, eleven at ten distinct rooms. The third, eager loading, shows a single wide query that selects the reservations joined to the rooms; it totals one query and sends the widest rows. The fourth, batched loading, shows one query for the reservations and one further query that fetches every needed room at once by a list of identifiers; it totals two queries and sends each room once. reservations = load_reservations() for r in reservations: print(r.room.name) All four configurations return the same rows. The room access reads like a field access, and may be a round trip Lazy · cache off SELECT resv ··· one query per reservation 1 + N queries 201 at N = 200 Lazy · cache on SELECT resv ··· one query per distinct room; dashed = memory 1 + D queries 11 at D = 10 Eager SELECT resv JOIN rooms the rooms arrive with the reservations, and repeat on every row 1 query the widest rows Batched SELECT resv SELECT room WHERE id IN (…) every needed room, asked for just once 2 queries
Figure 1. The same loop under four configurations of the data-access layer. N is the number of reservations the loop iterates over and D the number of distinct rooms they reference; the counts beneath each panel are the model's formulas, and the apparatus below evaluates them for the N and D you set. The two worked numbers are the lecture's configuration: 200 reservations over 10 rooms.

Table 1 — Model parameters

Table 1: the five parameters a student sets, with their ranges and defaults
ParameterWhat it is RangeDefault
Reservations N Rows the loop iterates over 1–1000 rows200
Distinct rooms D How many different rooms those rows reference 1–200 rooms10
Loading strategy How the data-access layer fetches the rooms Lazy · Eager · BatchedLazy
Database cache Whether an identity map answers a repeated room lookup Off · OnOff
Round-trip latency Network cost of one query — the one parameter that cannot change the count 1–100 ms/query10

Each control carries one sentence of help where it sits. The full parameter reference, and every quantity the model computes beyond the three in Table 2, are under Further investigation.

Table 2 — Observed quantities

Table 2: the three quantities the apparatus reports, and whether each is exact or modelled
QuantityWhat it counts Exact or modelled
Database queries Operations the data-access layer sends for one request exact
Total response time Round-trip overhead plus assumed database processing modelled
Served from the cache Room accesses answered from memory instead of a query exact

The query count is arithmetic, so it is the quantity to argue from. The milliseconds come from the published constants and would differ on a real engine.

Model assumptions

  • Query counts are derived, not timed. They come from the model's arithmetic over the five parameters — no database is contacted and nothing on this page is measured.
  • One serial round trip per query. Elapsed time is the query count times the latency; server-side execution, connection setup and result size are out of the model.
  • The cache is an identity map within one request. The first access to a room queries; later accesses to the same room are served from memory. It starts empty on every page load and never changes which rooms the loop needs.
  • All four configurations return the same rows. The program is correct under every one of them; only the number of operations differs.

Illustrative model; not calibrated to any database engine. A real mapper may batch, prefetch or cache differently from the three strategies here, so read the comparison as a ranking of access patterns rather than a prediction about your own stack.

Experiment

Predict before you run. Each experiment below names a baseline, the one parameter it changes, what it holds constant, the prediction to write down, the measurements to compare, and the engineering question the comparison answers. All three start from the same baseline — the configuration the lecture describes — and each one loads it for you. Allow five to ten minutes for the three.

  1. Which strategy decides the count?

    Baseline
    200 reservations over 10 distinct rooms, 10 ms round-trip latency, lazy loading, cache off.
    Change
    Loading strategy: Lazy → Eager → Batched.
    Hold constant
    Reservations, distinct rooms, round-trip latency, cache off.
    Predict
    Write three query counts before you touch the control. Which strategy issues fewest? Does any of the three return different rows?
    Run & measure
    Database queries and total response time at each setting, and the SQL the mapper emits under each.
    Explain
    Three configurations return identical data at 201, 1 and 2 queries. So what is an engineer actually choosing when they choose a loading strategy — and why does the source code not record the choice?

  2. When does lazy loading not cost N+1?

    Baseline
    The same: 200 reservations over 10 distinct rooms, 10 ms latency, lazy, cache off — 201 queries.
    Change
    Database cache Off → On; then distinct rooms 10 → 200.
    Hold constant
    Reservations, round-trip latency, and the loading strategy — lazy throughout.
    Predict
    With the cache on and 200 reservations sharing 10 rooms, how many queries? Then with 200 reservations in 200 different rooms?
    Run & measure
    Database queries and “served from the cache” in all three states.
    Explain
    Lazy loading never changed. So is N+1 a property of lazy loading, or of a configuration — and which part of the configuration decides it?

  3. The one control that cannot change the count

    Baseline
    The same: 200 reservations over 10 distinct rooms, 10 ms latency, lazy, cache off.
    Change
    Round-trip latency 10 → 100 → 1 ms; then, at 100 ms, the strategy to Batched.
    Hold constant
    Reservations, distinct rooms, loading strategy and cache while the latency moves.
    Predict
    Which of the two headline quantities moves when the latency moves, and which cannot? A faster network divides the latency by ten — what does batching divide instead?
    Run & measure
    Database queries and total response time at 1, 10 and 100 ms; then the same two under batched loading at 100 ms.
    Explain
    A tenfold faster network and a change of loading strategy both cut the response time. Which of the two is yours to write, and which must you buy?

Interactive apparatus — controls

How many rows the loop iterates over. Under lazy loading with no cache it scales the query count one for one.
How much sharing there is among those rows. It sets the ceiling a cache can reach, and nothing else.
When the mapper fetches a room: on access, in one join, or once for the whole result. This is the independent variable.
An identity map within this request. It collapses repeat lookups, and cannot help when every row references a different room.
What one query costs on the network. It converts queries into elapsed time and never changes the count — Experiment 3 is about exactly that.
Download HTML

The application code

This is the whole program. It does not change when you change the strategy or the cache — there is one version of it, and it is the same text under every configuration.

reservations = load_reservations()
for r in reservations:
    print(r.room.name)

How many database operations does this code perform?

Predict the answer before you look. Then change the strategy or the cache and predict again.

What the mapper emits


      

Three visible source-code statements; between one and N + 1 database operations. The number is a property of the loading strategy and the cache state, not of the lines you can read.

Results from the cost model

Database queries — exact arithmetic, not an estimate
Total response time —
Served from the cache —

cheap

Table 3 — all four configurations, at the same N, D and latency

Table 3: database queries and total response time for each of the four configurations of the data-access layer, at the parameters currently set.
Configuration Database queries Total response time

All four return the same reservations and the same room names. Nothing here is a correctness difference. Eager wins on queries and loses on bytes: a join repeats the room columns on every reservation row, while the batched query sends each room once. Which of those you care about depends on where the constraint is — the byte counts are under Further investigation.

Query timeline

Reservations query Room query (one per miss) Batched rooms query Join — reservations and rooms together

Round-trip overhead dominates and is exact. It is queries × latency — the lazy loads run one after another, because the loop cannot ask for a room it has not reached yet. Database processing is the only estimated term in the response time; the query count is arithmetic.

Interpretation

The decision this laboratory supports: an engineer holds a page that is slow and a loop that is correct. Where should the next hour go?

  1. Which controls changed the query count, and which one only changed the elapsed time? Say what that difference means for a team deciding between a faster database tier and a change to the access pattern.
  2. You can read all three lines of the loop and still not know whether the page costs 1 query or 201. Name two pieces of evidence that would tell you, and say where each comes from.
  3. At D = 10 the cache took lazy loading from 201 queries to 11; at D = 200 it took it from 201 to 201. Describe a real feature whose access pattern looks like each case.
  4. Eager loading issues one query and sends the most bytes; batched issues two and sends each room once. Under what constraint would you prefer each — and what would you have to measure to know which constraint you are under?

Further investigation

Everything below is reachable, computed from the same model and wired to the same controls. It is collapsed because it is reference rather than prerequisite, and the printed handout drops it.

Explore further — the five classroom presets

The teaching sequence the lecture notes follow. The first is the baseline the three experiments load; each of the next three changes exactly one decision; the last is the counter-example — a cache that cannot help.

Every quantity the model computes
Round-trip overhead
—
Database processing (estimated)
—
Data transferred
—
Room accesses in the loop
—
Room queries issued
—
Distinct rooms used
—

Six further quantities, all live. They are here rather than beside the three headline measurements because a results panel that reports nine numbers does not say which one the experiment turns on.

Model reference — the access path, the formulas, the constants and the exclusions
Figure 2: the modelled access path — application, data-access layer, database Three boxes in a row, joined by arrows that run both ways. On the left, the application, holding the loop over reservations. In the middle, the ORM or data-access layer, which decides how many queries the loop becomes. On the right, the database. The arrow between the middle and right boxes is labelled one network round-trip per query, at the configured latency. A note reads that the application code is identical under every strategy: only the middle box's behaviour changes. Application the loop over N ORM / data access decides how many queries the loop becomes 1 round-trip per query Database latency ms each The application code is identical under every strategy — only the middle box behaves differently.
Figure 2. One application, one data-access layer, one database. Every query costs one network round trip, which is the assumption the constants below price.

Nothing on this page executes SQL. There is no database, no connection, no engine, and no measurement. Every number is computed from the constants below, which is why the same controls always produce the same answer. The query counts are exact arithmetic; the times and byte counts are a model, and a real engine would differ. The arithmetic is deterministic and carries no randomness, so there is no seed to fix and no sampling variability to average away: one run is the expected result.

Query counts — exact

StrategyCacheQueriesWhy
LazyOff1 + N One query for the reservations, then one per reservation. Nothing remembers that two reservations share a room.
LazyOn1 + Deff One query for the reservations, then one per distinct room. Later accesses to a room already loaded are served from the identity map.
Eagereither1 A single join returns reservations and their rooms together. The cache changes nothing: the room columns already arrived.
Batchedeither2 One query for the reservations, then one WHERE id IN (…) for the rooms the loop will need.

N+1 is a consequence of a configuration, not a property of lazy loading. Lazy loading executes N additional queries only when every room access misses. Set the cache on and point 200 reservations at 10 rooms and lazy loading issues 11 queries. Point them at 200 different rooms and the cache cannot help: 201 again. Experiment 2 is finding that boundary.

Deff — the clamp, and why there is one

A reservation references exactly one room, so N reservations can reference at most N distinct rooms. When the distinct rooms control exceeds the reservation count, the model uses Deff = min(D, N) and says so above the results rather than reporting a configuration that cannot exist. Your control keeps the value you set.

Reservations are assigned to rooms round-robin — reservation i takes room i mod Deff — so every distinct room is used and the assignment is deterministic. The assignment changes which accesses miss, and therefore where the misses sit on the timeline. It does not change how many there are: with the cache on, each distinct room is fetched exactly once however the reservations are spread.

Times and bytes — estimated

ConstantValueStands for
Round-trip latencyyour control Charged once per query, and the queries are sequential. This is the term that makes 200 individually cheap queries expensive.
Statement cost0.15 ms Parse, plan and dispatch, charged once per query regardless of size.
Row cost0.004 ms Per row returned.
Join row factor× 1.6 Extra per-row work in the eager join, matching reservation rows to room rows.
Per-query bytes200 B Statement text, result metadata and protocol framing, once per query.
Reservation row80 B
Room row120 B Repeated on every row of the eager join; sent once per room by the batched query.

What the model leaves out, stated so it is not mistaken for a benchmark: no connection-pool waiting, no lock contention, no index choice, no concurrency, no network variance, no server-side caching between requests, and no application CPU. Response time here is round-trip overhead plus database processing, nothing else. A real system has all of those, and this page would mislead anyone who read its milliseconds as a prediction about one.

The five parameters in full

ParameterMeaningValuesDefault What changing it does
Reservations NRows the loop iterates over 1–1000 rows200 Scales the lazy strategy's query count one for one
Distinct rooms DHow many different rooms those rows reference 1–200 rooms10 Sets the ceiling a cache can reach
Loading strategyHow the data-access layer fetches the rooms Lazy · Eager · BatchedLazy Changes the query count
Database cacheWhether a repeated room lookup is served from memory Off · OnOff Collapses repeat lookups; cannot help when every row is distinct
Round-trip latencyNetwork cost of one query 1–100 ms/query10 Converts queries into elapsed time; never changes the count

What the cache is. “Cache on” means an ORM identity map or application cache that remembers a room within this request: the first access to a room issues a query, and every later access to the same room is served from memory. It starts empty on every page load, so nothing is carried over between configurations. It never changes which rooms the loop needs — all four configurations return the same reservations and the same room names.

The model's self-tests

The engine is a pure module published as window.NPlusOne with no DOM dependency, so the test suite under tests/ extracts it from this file and exercises it directly. The tested text and the published text are the same bytes.