Application Integration & Schema Evolution

N+1 and Data-Access Shape

N+1 is a dataflow mismatch: one query discovers N parents and application traversal issues another query per parent. Fix the access shape for the use case with a join, batched key lookup, aggregate, or deliberate streaming—not a universal eager-loading switch.

Trace round trips, not just SQL duration.
Object traversal can hide I/O.
A join changes cardinality.
Batch fetching bounds query count.
Projection often beats entity loading.
Eager loading is not universally safe.

N+1 trace for five orders

Q1 SELECT order_id, customer_id, placed_at FROM orders
   WHERE customer_id = ? ORDER BY placed_at DESC FETCH FIRST 5 ROWS ONLY
Q2 SELECT ... FROM order_items WHERE order_id = 501
Q3 SELECT ... FROM order_items WHERE order_id = 498
Q4 SELECT ... FROM order_items WHERE order_id = 490
Q5 SELECT ... FROM order_items WHERE order_id = 477
Q6 SELECT ... FROM order_items WHERE order_id = 462

One request, six round trips; N=500 means 501 unless another strategy intervenes.

Set-oriented detail for an already bounded parent page

WITH selected_orders AS (
  SELECT order_id, customer_id, placed_at, status
  FROM orders
  WHERE customer_id = :customer_id
  ORDER BY placed_at DESC, order_id DESC
  FETCH FIRST 20 ROWS ONLY
)
SELECT o.order_id, o.placed_at, o.status,
       i.line_no, i.product_id, i.quantity, i.unit_price
FROM selected_orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id
ORDER BY o.placed_at DESC, o.order_id DESC, i.line_no;

Project an aggregate when lines are not needed

SELECT o.order_id, o.placed_at, o.status,
       COUNT(i.line_no) AS line_count,
       COALESCE(SUM(i.quantity * i.unit_price), 0) AS order_total
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.customer_id = :customer_id
GROUP BY o.order_id, o.placed_at, o.status
ORDER BY o.placed_at DESC, o.order_id DESC
FETCH FIRST 20 ROWS ONLY;

Choose shape by required result
Need	Candidate	Watch
small parent+child page	bounded parent CTE then join	row multiplication
many parents, selective children	parent query + batched child query	list limits and snapshot
counts/totals	database aggregate projection	correct grouping grain
huge export	ordered streaming/set query	fetch size and backpressure
occasionally used association	explicit lazy load	query budget and closed session