skip to content

What happens when one statement fetches a parent together with two of its child collections at once?

level: middleimportance: should knowfreq 57%

answer

  1. independent collections, no join between them
  2. product, not sum
  3. each collection inflated by the other's size
  4. a set hides the inflation, not the cost
  5. one collection per statement

basics

~20 s

The two collections are crossed: each parent yields the product of the two child counts in rows, so four of one and three of the other give twelve rows. Each collection's contents are multiplied by the other's size.

solid answer

~50 s

Joining two independent collections in one statement pairs every row of the first with every row of the second, so a parent with four of one child and three of the other produces twelve rows instead of seven. The damage is not only volume: each collection now sees its own items repeated once per item of the other collection. Whether you *see* those repeats depends on the mapping — a set silently collapses them, so the sizes look right while the transport cost was still paid, whereas a collection that permits duplicates and carries no index reports four times the real number and inflates any sum over it. Layers differ here: some refuse a second collection outright with an error, others return the cross product silently. The fix is at most one collection per statement.

go deeper

for a junior

Hold on to the arithmetic: two collections in one fetch multiply, so four and three make twelve rows rather than seven. One collection at a time is the safe habit.

for a middle

Explain why there is no join condition between the two collections, and what each collection then holds — duplicates that a set collapses and a duplicate-permitting collection exposes.

for a senior

Show that you catch this from row counts before it reaches production, and that you know it degrades with the product of collection sizes, so the worst account fails long before the average one does.

for a principal

Treat unbounded collection fetches as a design constraint, not a review comment: decide which reads may fetch collections at all, and where a summary or projection replaces the graph.

## The arithmetic Two child collections hanging off the same parent are **independent** — there is no join condition between them. Ask for both in one statement and the database has no choice but to pair every row of one with every row of the other: - 4 of collection **A** and 3 of collection **B** gives **12** rows for that parent, not 7. - Add a third collection with 2 rows and it becomes **24**. - Across 50 parents with those cardinalities, one statement returns 1,200 rows to deliver 350 child records. The growth is multiplicative in the number of collections and in each one's size, which is what makes it dangerous: the statement is fine in a test fixture with one child each, and catastrophic on the account that has hundreds. ## What the caller sees depends on the collection kind Within the parent, each item of A now arrives once per item of B, and each item of B arrives once per item of A. What survives into the object depends on how the collection is mapped: | Mapped as | What the collection holds | Visible? | What it costs | |---|---|---|---| | A set | Each child once — duplicates collapse on identity | No, sizes look correct | Rows and bytes were still fetched | | A collection that permits duplicates and carries no index | Each child once per row of the other collection | Yes, loudly — sizes and sums are wrong | Rows, bytes, and wrong answers | | An indexed or ordered collection | Depends on how the index is derived; a positional index over duplicated rows is inconsistent | Sometimes | Rows, bytes, possible mapping errors | Note what *no* mapping choice can hide: the parent still appears once per row in the top-level result list. The collection kind governs duplicates **inside** the collection only. ## Layers differ, deliberately Data-access layers do not agree on how to handle a second collection in one fetch. Some detect it while building the statement and refuse it outright with an explicit error, on the grounds that the result is almost never what the developer meant. Others emit the statement and return the cross product silently, leaving the developer to notice the row count. Both positions are defensible — refusing protects the unwary and blocks the rare case where the product is genuinely small and one round trip is genuinely wanted. Know which behaviour you are working with, because the failure modes are opposite: a loud error at development time, or a slow endpoint discovered in production. ## Why the silent case is the worse one When both collections are mapped as sets, everything *looks* right. Sizes are correct, no duplicate appears in any list, no test fails. The cost is entirely in the volume: - The database produced and sorted a multiplied result. - Every byte of it crossed the wire, including the parent's columns repeated on all twelve rows. - The layer allocated and de-duplicated all of it. - The endpoint's latency grows with the *product* of the collection sizes, so it degrades non-linearly as data accumulates. This is the version that survives review and shows up months later as "the page is slow for our biggest customers." ## What to do instead 1. **Fetch at most one collection per statement.** This is the whole rule. One collection fans out linearly, which is usually acceptable. 2. **Bring the other collections in separate statements**, so each fan-out stays linear and the layer stitches them onto the already-loaded parents. 3. **Ask whether the screen needs both collections at all.** Very often one of them is only counted or summarised, and a projection or an aggregate is cheaper than the objects. 4. **Measure rows, not objects.** Log the row count the statement returned; a product shows up immediately there and nowhere else. ## The interview-ready summary One collection multiplies parents. **Two collections multiply each other**, so the row count is a product rather than a sum, the contents of each collection are inflated by the size of the other, and a set mapping hides that inflation without avoiding any of its cost. Layers disagree about whether to allow it at all. The safe habit is one collection per statement.

  • Why does mapping both collections as sets not solve the problem?
    A set collapses duplicate items on identity, so the assembled collections have the right sizes. But the collapse happens after the database produced the multiplied rows and after every one of them crossed the wire. You have removed the visible symptom and kept the entire cost — which is why the silent case usually surfaces later, as latency on the largest accounts.
  • Is a cross product ever acceptable?
    When both collections are small and bounded by design — a few addresses and a few phone numbers — the product is tiny and one round trip may beat two. The test is whether an upper bound exists and holds for the worst-case row, not whether typical data is small today.
  • How does the row count change if one of the two collections is empty?
    Under inner joins the product collapses to zero rows and the parent disappears entirely. Under outer joins the empty side contributes nulls, so the parent still yields the other collection's row count. This is a common surprise: adding a second collection can make parents vanish from a list that used to include them.

It is a menu where every starter must be paired with every dessert: three starters and four desserts print twelve lines, though the kitchen only cooks seven dishes.

saying these in an interview costs you the question

  • Expects the row counts of two collections to add, not multiply
  • Thinks a set mapping avoids the cost as well as the duplicates
  • Believes every layer allows two collections in one fetch
  • Sums a duplicate-permitting collection filled from a cross product
  • Judges the fetch safe because test data has one child each
  • Counts objects instead of rows when checking the damage