Fix families: JOIN, IN, preload, and DataLoader

You have confirmed an N+1 in the query log: 50 customer queries for one orders page. There are four ways to fix it, and they have different tradeoffs. Picking the wrong one for your data shape can make things worse.

The four fix families

1. JOIN — one query, one trip

Fetch parent and child in a single SQL query using a JOIN:

SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.user_id = 42
LIMIT 50;

One round-trip. The database returns everything merged into one result set.

The tradeoff: for one-to-many relationships, the result set duplicates parent columns for every child row. Fifty orders with five line items each produces 250 rows where 200 repeat the same order columns. Network bandwidth and ORM parsing cost can offset the round-trip savings. JOIN works best for one-to-one or small one-to-many (1–5 children per parent).

2. IN — two queries, no duplication

Two round-trips: fetch parents first, then fetch all children using WHERE id IN (...):

-- Query 1: get all orders
SELECT * FROM orders WHERE user_id = 42 LIMIT 50;

-- Query 2: get all customers for those orders
SELECT * FROM customers WHERE id IN (1, 2, 3, ..., 50);

No row duplication. The parent result is 50 rows; the children result is up to 50 rows — each row of data appears exactly once.

The tradeoff: the IN list has practical limits. Postgres handles 10 000+ items with growing planner cost; MySQL has a hard limit at max_allowed_packet. For most pagination (10–1000 items), IN is the right default for one-to-many.

3. Preload / eager loading — ORM directive

All major ORMs ship a directive that declares up front what relationships to load. The ORM picks JOIN or IN internally:

# Rails — .includes picks strategy
Order.where(user_id: current_user).includes(:customer).limit(50)

# Django
Order.objects.filter(user=request.user).select_related('customer')[:50]

# SQLAlchemy
session.query(Order).options(selectinload(Order.customer)).filter_by(...).limit(50)

# Prisma
prisma.order.findMany({ where: {...}, include: { customer: true } })

All of these produce 2 queries instead of 51. You declare intent; the ORM optimises tactics.

The tradeoff: if you preload too aggressively — loading relationships the request never uses — you waste I/O. Declare only what the current request needs.

4. DataLoader — request-scoped batching

DataLoader (Facebook, 2015) queues lookup IDs across the entire request and fires one batched query when the event loop yields to its next tick:

// Create once per request
const userLoader = new DataLoader(async (ids) => {
  const users = await db.user.findMany({ where: { id: { in: ids } } });
  return ids.map(id => users.find(u => u.id === id));
});

// Call from anywhere in the request — each call queues the id
const user = await userLoader.load(orderId);

DataLoader provides three properties: (1) automatic batching — many load(id) calls become one query, (2) automatic caching — repeated load(id) within the same request returns the cached result without a second query, (3) request scope — the cache is scoped to the request, so stale data does not leak.

The tradeoff: DataLoader requires an async / event-loop runtime. It adds caching complexity and a small batch-window latency (typically under 1 ms). It is the right tool when data needs are scattered across many unrelated code paths in the same request — the canonical case being GraphQL resolvers.

Choosing by cardinality

The rule of thumb:

• One-to-one → JOIN. No duplication. One trip.
• One-to-many (small fan-out) → preload. ORM picks JOIN internally. Simple to write.
• One-to-many (heavy fan-out) → IN-based selectinload. Avoids result-set bloat.
• Multi-source or GraphQL → DataLoader. Batches across unrelated code paths.

Practical example: a nested eager load

# Rails nested eager load:
User.find(42).projects.includes(tasks: :comments).limit(50)
# Produces:
#   SELECT * FROM projects WHERE user_id = 42 LIMIT 50
#   SELECT * FROM tasks WHERE project_id IN (1, 2, ..., 50)
#   SELECT * FROM comments WHERE task_id IN (...)
# 3 queries instead of 1 + 50 + 250 = 301

# Django equivalent:
Project.objects.filter(user=user).prefetch_related('tasks__comments')[:50]

# SQLAlchemy:
session.query(Project).options(
    selectinload(Project.tasks).selectinload(Task.comments)
).filter_by(user_id=42).limit(50)