skip to content

A projected list query still runs slowly after it stopped returning mapped objects - what did projecting not fix?

level: seniorimportance: should knowfreq 53%

answer

  1. materialisation, not row source
  2. same joins, same rows, same plan
  3. a to-many join still multiplies rows
  4. count statements and rows, not fields

basics

~20 s

Projecting changes what each row is materialised into, not which rows the engine must produce. The same joins, filter, sort and row count remain, and a projection reaching across a to-many link still multiplies rows.

solid answer

~40 s

A projection shrinks the per-row work the data-access layer does: no snapshot, no identity-map entry, no stand-ins. It does not touch the row source. The statement still joins the same tables, still scans whatever the filter and sort require, and still returns the same number of rows - so if the read was slow because it produced fifty thousand rows, or because the sort had no supporting index, projecting changes nothing measurable. Worse, a projection that reaches across a link still faces the same join-or-second-statement decision as a fetch: joining a to-many duplicates the parent columns, which inflates counts and sums and breaks page sizes. And if the transfer type is built by calling back into the layer once per row, the per-row statement loop is still there, now hidden inside a mapping function.

code

sql · 8 lines
sql
SELECT c.id,
       c.name,
       SUM(o.total) AS lifetime_value,
       COUNT(p.id)  AS phone_count
FROM customer c
JOIN orders o ON o.customer_id = c.id
JOIN phone  p ON p.customer_id = c.id
GROUP BY c.id, c.name

go deeper

for a junior

Take away the headline: asking for fewer fields does not mean the database does less searching. The number of rows and the joins behind them decide most of the time.

for a middle

Be able to separate the two costs - what the layer builds per row versus what the engine must produce - and explain why a to-many join repeats the parent's columns.

for a senior

Diagnose from evidence: statements per request, rows returned versus rows shown, the plan against the filter and sort. Then name the repair that matches the actual cause.

for a principal

Set the expectation for the team: projecting is a materialisation optimisation with a memory payoff, and reporting it as a latency fix is how the same read gets rewritten twice.

## What projecting changes, and what it leaves alone | Cost | Changed by projecting? | |---|---| | Snapshot kept per row for change detection | Removed | | Identity-map entry per row | Removed | | Stand-ins installed for unfetched links | Removed | | Bytes per row on the wire | Reduced, by however many columns you dropped | | Rows the engine must scan and join | **Unchanged** | | Whether the filter and sort are supported by an index | **Unchanged** | | Number of rows returned | **Unchanged** | | Join-versus-second-statement decision across a link | **Unchanged** | The left column is real work and dropping it matters, especially at high row counts where snapshots dominate memory. But it is per-row overhead on top of a plan. If the plan is the problem, projecting is decorating it. ## Fan-out: the trap specific to projecting across a link A projection that needs a field from the other side of a to-many link has the same two options a fetch has: join, or issue a second statement keyed by the parent identifiers. Joining a to-many returns **one row per child**, with the parent's columns repeated on each. Three consequences follow, and all three are correctness bugs, not slowness: 1. **Aggregates inflate.** Two to-many joins in one statement multiply each other: each side's rows are repeated once per row of the other, so a sum counts the same values many times over. 2. **Page sizes lie.** Asking for twenty rows returns twenty *joined* rows, which may be six parents. Pagination over a fan-out has to page the parents in their own statement. 3. **Counts lie.** A count over the joined result counts children, not parents, unless it counts distinct parent keys. The repairs are the ordinary ones: aggregate the sides in separate statements, or restrict the join to a single fan-out and fetch the rest with a second statement keyed by the parent identifiers you just retrieved. ## The per-row loop that survives projecting A transfer type is only as cheap as the code that builds it. If the mapping function takes an identifier and calls back into the data-access layer to look something up, then one statement has become one statement plus one per row, and the projection has hidden it inside what looks like pure mapping code. The tell is that the count of statements grows with the size of the result. Building a projection should consume columns the statement already returned and nothing else. ## How to tell where the time actually went - **Count the statements** for the request, before and after. A projection that did not change the count and did not change the rows returned has not changed the shape of the problem. - **Compare rows returned against rows displayed.** A screen showing twenty rows out of fifty thousand returned is not a projection problem; it is a missing filter, missing pagination, or a fan-out. - **Read the plan against the filter and the sort.** The plan is chosen by the `WHERE` and `ORDER BY`, not by the shape of the result type. A narrow select list can help when it lets the engine satisfy the query from an index it already has, but that is a property of the columns, not of the return type. - **Check whether the result is bounded at all.** An unbounded read that got narrower is still unbounded, and it will fail later at a larger size. - **Watch the memory profile separately.** Projecting genuinely helps peak memory and the growth of the tracked set; if that was the symptom, the fix worked even when wall-clock time barely moved. ## What to actually change when the row source is the problem - Add or fix the predicate so fewer rows qualify, and make sure it is supported. - Paginate on the parent, deterministically, with a key that breaks ties. - Split a multi-fan-out statement into one per collection and stitch the results by key. - Push the aggregation into the statement where the ratio of underlying rows to result rows is large. - Only then reach for a narrower select list. ## The framing to give in an interview Projecting is a **materialisation** optimisation. Row-source problems - too many rows, an unsupported sort, a fan-out, a per-row loop - are **plan and shape** problems. They live in different layers, and confusing them is why a team can rewrite every read to a transfer type and see the same latency graph afterwards.

  • When does a narrower select list genuinely speed a statement up?
    When the dropped columns were expensive to read - large text or binary values stored away from the row - or when the remaining columns let the engine answer from an index without touching the table. Both are properties of which columns you kept, not of whether the result is a transfer type.
  • How would you confirm a fan-out is behind wrong totals rather than bad data?
    Run the statement without the grouping and look at the raw rows: if the parent's columns repeat and the child rows are duplicated across the second join, each side is being multiplied by the other. Aggregating the sides in separate statements restores the correct numbers.
  • Is projecting still worth doing if it does not change the response time?
    Often yes - it removes snapshots and identity-map entries, so peak memory and the growth of the tracked set fall, and it removes the risk of a stray write or a statement fired during rendering. Just report it as what it is rather than as a latency fix.

saying these in an interview costs you the question

  • Believes fewer selected columns must mean a faster plan
  • Joins two to-many links and trusts the resulting sums
  • Paginates over a fan-out and wonders why pages are short
  • Builds the transfer type by querying once per row
  • Declares a read fixed without recounting statements or rows