In a data-access layer, how does a cursor-backed streaming read differ from one that materialises the whole result?
answer
- buffered whole result versus rows on demand
- memory traded for an open boundary
- fetch size sets round trips against buffer
- streaming alone does not stop tracked-set growth
basics
~20 sA materialising read buffers the whole result first, so memory scales with the result. A streaming read pulls rows in blocks from an open cursor as you consume them, keeping memory near one block — but the transaction stays open throughout.
solid answer
~50 sA materialising read collects every row of the result, builds the objects or transfer models, and hands you a complete collection; peak memory is proportional to the result and the first row arrives only after the last one has been fetched. A streaming read keeps a **cursor** open on the server and pulls rows in blocks sized by the **fetch size**, so you process row one while the rest are still in the database. The costs are different rather than smaller: the query's transaction and connection stay open for the whole consumption, the result is consumable once, and if the layer tracks what it materialises the tracked set still grows to the size of the result. A streaming read is therefore usually paired with an untracked or projection read, and with releasing each row as you finish it.
go deeper
Know the difference in one line: a materialising read loads the whole result into memory first, a streaming read pulls rows as you consume them.
Explain the trade concretely — memory proportional to the result versus a transaction and connection held open — and describe what the fetch size controls between round trips and buffer size.
Show that you also close the tracked-set hole: untracked or projection reads, releasing rows as they are processed, and keeping slow downstream work out of the consumption loop so the connection is not held longer than necessary.
Think in terms of shared capacity: long-lived cursors consume connections and force the engine to retain read-consistency state. Decide which workloads are allowed to hold a boundary that long and what the alternative is for the rest.
## Two ways a result reaches you **Materialising** is the default nearly everywhere: the layer runs the query, drains the whole result, constructs an object or transfer model per row, and returns a collection. It is simple and safe for bounded results. Its cost is that peak memory is proportional to the result size — twice over, in fact, since the raw rows and the constructed objects can coexist during construction — and no work starts until the last row has arrived. **Streaming** keeps a **cursor** open: a server-side position in the result that the client advances. The client asks for a block of rows, processes them, asks for the next block. The block size is the **fetch size**, and it is the main tuning knob: - a fetch size of 1 means a network round trip per row, which is usually the slowest thing you can do; - a very large fetch size approaches materialising, since a block that big must fit in memory; - somewhere in between — hundreds to a few thousand rows, depending on row width — you get a small buffer and few round trips. ## What streaming actually costs | | Materialised result | Streaming read | |---|---|---| | Peak client memory | proportional to the result | roughly one block, plus whatever you retain | | Time to first row | after the last row is fetched | after the first block | | Transaction / connection | can end as soon as the result is built | must stay open for the whole consumption | | Re-iteration | the collection can be traversed again | consumable once | | Other work on the same connection | unconstrained | restricted in many layers while the cursor is open | | Tracked objects | one per row | one per row *unless* the read is untracked | The two rows that catch people out are the open boundary and the tracked set. **The open boundary.** A cursor is server-side state inside a transaction. Consuming ten million rows slowly means holding that transaction — and the pooled connection behind it — for as long as the consumption takes. Whatever the engine must retain so the read stays consistent is retained for that whole window too. A stream that is fast to start and slow to finish can occupy a connection for an hour, which is a capacity problem for everyone else long before it is a problem for the job itself. **The tracked set.** Streaming solves the *driver's* buffering. It does nothing about the layer materialising each row into a tracked object and keeping it in the identity map so that it can be dirty-checked later. Memory then grows exactly as it would have with a materialised list, only more slowly and more confusingly. The remedies are to read untracked, to select a projection instead of a mapped object, or to release each object once processed. ## The default for a read-only path Where nothing will be written back, an **untracked read** is the better default whatever the size: no snapshot is taken, no identity map entry is made, and the objects become ordinary values the garbage collector can reclaim the moment you drop them. Combined with streaming, memory then stays flat regardless of how many rows pass through. Combined with a materialising read of a bounded result, it still saves the snapshot memory and the flush-time comparison. ## Choosing between them Stream when the result does not comfortably fit in memory, or when you want to start work before the query finishes. Materialise when the result is bounded and you would rather return the connection quickly than hold it — a small result buffered in one go frees the transaction immediately, which is usually worth more than the memory you would save. And be explicit about what "bounded" means. A result that is small today because the table is small is not bounded; a result constrained by the query — a predicate on a narrow key range, or an explicit row limit — is. The read that goes wrong in production is almost always the one that was implicitly bounded by how little data existed when it was written. ## What to demonstrate Say that streaming moves memory pressure from the client into an open transaction, name the fetch size as the round-trip/buffer trade, and add that streaming alone is not enough — the read must also avoid accumulating tracked objects, or nothing has been fixed.
- Why can a streaming read still exhaust memory?Because two buffers exist. Streaming addresses the driver's: rows arrive a block at a time. If the layer materialises each row into a tracked object, the identity map retains every one of them for dirty checking, so memory grows with the result anyway. Read untracked, select a projection, or release each object as you finish it.
- What makes the fetch size a correctness concern rather than only a tuning knob?It sets how much is buffered at once. Left at a value large enough to hold the whole result, streaming degenerates into materialising and the memory problem returns; set to one row it turns the read into a round trip per row. Neither extreme is a performance nuance — each defeats the reason for streaming.
- Why is a materialised read sometimes the better choice for a small result?Because it frees the transaction and its connection as soon as the rows are drained, while a stream holds both for as long as consumption takes. For a bounded result the memory saved by streaming is negligible and the connection held is not, so buffering is the cheaper trade overall.
saying these in an interview costs you the question
- Thinks streaming alone bounds memory even when every row is tracked
- Believes a streamed result can be iterated more than once
- Assumes the transaction can be committed while the cursor is still being consumed
- Sets fetch size to one row and calls it streaming
- Streams a small result and holds a pooled connection for no benefit