skip to content

Arithmetic over a 5,000,000-row column takes milliseconds while the same-shaped whole-column text normalisation takes half a minute — why?

level: seniorimportance: must knowfreq 64%

answer

  1. the surface is not the execution
  2. what is underneath the column?
  3. packed bytes against boxed objects
  4. one compiled pass, or one call per value
  5. text is variable-length; offsets get rebuilt

basics

~20 s

A column-shaped text surface is a way of writing the job, not a promise about how it runs. Where text is held as packed bytes with an offset per row, compiled code walks it once; where each value is a separate language object, the same-looking call is one call per value — the loop you thought you had removed.

solid answer

~50 s

Two claims get conflated here. The surface claim is that you said the job once for the whole column, and it is true of both cases. The execution claim is that it runs as one compiled pass over packed bytes, and that depends entirely on how the text is stored. Packed storage — every value's bytes end to end plus one offset per row — lets a compiled kernel walk the column once, and the cost lands within a small factor of numeric work. Boxed storage — the column as an array of pointers to language objects — leaves the surface looping per value: a pointer chase, a crossing back into the host language's text routines, and a freshly allocated object per result. Inspect the column's stored representation first, opt into a packed text representation if the tool offers one, and reduce the number of text passes rather than assuming the column form fixed it.

code

pseudocode · 16 lines
pseudocode
# One column-shaped statement. Two possible executions underneath.
cleaned = normalise_case(text_column)

# (a) values packed: all bytes in one buffer, one offset per row
#     the loop below lives inside compiled code and never enters your runtime
allocate out_bytes, out_offsets
for i in 0 .. n-1:
    span = in_bytes[in_offsets[i] .. in_offsets[i+1]]
    write_normalised(out_bytes, out_offsets, i, span)

# (b) values boxed: the column is an array of pointers to language objects
#     the loop below lives in the runtime and pays per value
for i in 0 .. n-1:
    value  = follow_pointer(column[i])      # scattered read
    result = host_text_routine(value)       # crossing out and back
    out[i] = allocate_object(result)        # one new object per row

go deeper

for a junior

Recall the distinction itself: writing a job once for the whole column says how you wrote it, not how it runs. Text and numbers can look identical on the page and differ enormously in cost.

for a middle

Explain the two storage layouts and what one call becomes in each: a compiled walk over packed bytes and offsets, or a per-value pointer chase, crossing and allocation.

for a senior

Diagnose it with evidence — the stored representation, cost per row against the numeric baseline, and the response to longer values — then act on the representation and on the number of passes.

for a principal

The standing decision is whether text columns are declared into a packed representation at the boundary as a rule. It removes a whole class of surprise at the cost of a declaration everyone must maintain.

## Two different claims that sound like one When someone says a text clean-up is "done for the whole column", they may mean either of two things, and in this family the two come apart: - **the surface claim** — the job is *written* once for the whole column rather than once per record. This is true of every tool here, for text as much as for arithmetic. - **the execution claim** — the job *runs* as one compiled pass over a packed buffer of a known representation. This is true of arithmetic almost everywhere, and true of text only sometimes. The gap between milliseconds and half a minute is exactly the gap between those two claims. Your program issued one statement in both cases. What differs is what happened below it. ## What is actually under the column | | Packed text | Boxed text | |---|---|---| | Layout | Every value's bytes laid end to end in one buffer, plus one offset per row saying where each value starts | An array of pointers, each leading to a separate language object holding one value | | What one whole-column call becomes | A compiled loop over the bytes, writing a new byte buffer and a new offset array | A loop that, per row, follows a pointer and calls a text routine in the host language | | Cost paid per row | Reading and writing bytes | A pointer chase, a crossing into the host language, one object allocated for the result | | Memory locality | Sequential over one buffer | Scattered across wherever each object happens to live | | Feels like | The arithmetic case | The record loop you were trying to avoid | The boxed case is the answer to the question as asked, most of the time. It is not a defect of the tool; it is what you get when a column of text is, underneath, a column of general language objects. And it is genuinely invisible at the surface — the statement you wrote is identical. ## Why even the packed case costs more than arithmetic Do not expect parity even when it is a compiled pass. Text is **variable-length**, and that has consequences a fixed-width numeric column never pays: - The size of the output is not known in advance, so the pass either measures first or grows a buffer as it goes. - Every step rebuilds both the bytes and the offsets, so a chain of six clean-up steps rewrites the entire text of the column six times, where six numeric steps rewrite six fixed-width buffers of a size you can compute exactly. - The work per value is proportional to the length of the value, not constant, so one long value costs more than one short one and the total tracks total bytes rather than row count. So the honest expectation is: packed text is within a small factor of arithmetic; boxed text is orders away from it. ## How to tell which one you are on 1. **Look at the column's stored representation.** The tool will tell you whether the column is held as a dedicated text representation or as general objects. This is the single check that answers the question. 2. **Compute the cost per row and compare it against the numeric baseline** you already measured on the same table. A ratio in the small single digits says compiled; a ratio in the hundreds or thousands says per-value calling. 3. **Vary the row count.** Both cases scale about linearly, so scaling alone does not separate them — but the per-row cost at two sizes tells you whether you are looking at a fixed setup cost or a genuine per-value price. 4. **Vary the value length at a fixed row count.** A compiled pass over bytes tracks total bytes; a per-value calling loop is dominated by the per-value fixed cost and is comparatively insensitive to length. ## What to do about it - **Opt into a packed text representation** if the tool offers one. This is usually the largest single change available and it costs one declaration. - **Cut the number of text passes.** Fold several clean-up steps into fewer, and do the narrowing selection before the clean-up rather than after, so fewer values are touched at all. - **Work over the distinct values when there are far fewer of them than rows.** Fifty thousand distinct values behind five million rows means the expensive per-value work can be done fifty thousand times and the answers matched back. - **Normalise once, early.** Repeated clean-up at each point of use multiplies the most expensive kind of pass you have. ## Credit the speed-up to the right thing Two attributions are commonly wrong and an interviewer will probe both. The first is crediting the column form itself: the form is a way of writing, and where the values are boxed it buys nothing at run time. The second is crediting the machine's cores: several designs in this family run a single-threaded compiled pass, and the whole win is removing per-value dispatch and unwrapping rather than parallelism. Some engines do additionally split the column across threads, and that is a second and separate multiplier — worth naming as such rather than folding into the first.

  • Even on packed storage, why should you not expect text work to match arithmetic on the same row count?
    Because values are variable-length. The output size is not known before the pass, each step rebuilds both the bytes and the offsets, and the work per value tracks its length rather than being constant. A fixed-width numeric pass knows its output size exactly and touches a predictable number of bytes per row, so a small factor between the two is the honest expectation.
  • A colleague says the whole-column form is fast because it uses all the cores. What is wrong with that?
    It credits the wrong mechanism. The classic packed-buffer designs run a single-threaded compiled loop, and the entire gain over a record loop comes from removing per-value dispatch, unwrapping and re-wrapping. Some engines do split a column across threads, but that is a second, separate multiplier layered on top — and on a boxed column neither effect is available.
  • When does working over the distinct values instead of the rows actually help?
    When the distinct count is far below the row count and the per-value work is the dominant cost, which is exactly the boxed case. Doing the expensive work fifty thousand times instead of five million and matching the answers back turns a per-value price into a much smaller one. It buys little when nearly every value is unique, or when the pass is already compiled and cheap per byte.
  • How would you confirm the diagnosis rather than assuming it?
    Check the column's stored representation directly — that answers it outright. Then corroborate with two measurements: the cost per row against the numeric baseline on the same table, and the cost at a fixed row count with much longer values. A compiled pass tracks total bytes; a per-value calling loop is dominated by the count of values.

Two warehouses hold the same ten thousand parcels and receive the same one-sentence instruction: relabel everything. In the first, the parcels sit end to end along a single belt, so the worker walks the belt once. In the second, each parcel is in its own numbered locker somewhere in the building, so the same sentence becomes ten thousand separate walks. The instruction is identical in both places; the building decides what it costs.

saying these in an interview costs you the question

  • Says a column-shaped text call is always one compiled pass
  • Explains the gap as text simply being slow, with no mechanism
  • Assumes whole-column operations use every core of the machine
  • Believes a hand-written record loop would recover the numeric speed
  • Ignores that each text step rebuilds the whole buffer and its offsets
  • Treats the statement's shape as evidence about its execution