Read-performance regression vs v0.10.4 on large worksheets

Status: For investigation after the Charts work lands). Not blocking.

Background

Reported in #414 and again in #460 on a large .xlsm (worksheet XML ~120 MB, readtable(file, 2, "A:CF"; first_row=5)):

Version	Time	Allocations	Memory
0.10.4	11.1 s	91 M	4.0 GB
0.11.11	57.2 s	620 M	30.6 GB
0.12.3	17.3 s	9 M	1.0 GB

0.12 fixed 0.11's allocation problem but is still ~55% slower than 0.10 while allocating ~10× less. That points to CPU-bound tokenising rather than GC.

Profile (0.12.3, flat, `mincount=200`)

readtable's stack is ~9,180 samples. File loading on worker threads (process_file) adds ~2,700, and the main task waits on it.

Cost	Samples	Location
`splitNode` → `XML.splicetext` → `sourcetext`	~2,900	`read.jl` `process_file` (1,896) + again in `eachrow` after cache fill
Four attribute `get`s per cell (`r`, `t`, `s`, `cm`)	~1,850	`cell.jl` `Cell(::LazyNode, …)`
`eachchildnode` + `is_simple_value` per cell	~1,180	`cell.jl` `Cell(::LazyNode, …)`
Unzip (inflate + copy)	~1,100	unavoidable; same in 0.10
Lazy SST load	~800	first `getdata` in `eachtablerow`
`TableRow` construction + column pushes	~1,400	`table.jl` `TableRow`, `gettable(::TableRowIterator)`

Findings

splitNode tokenises the whole sheet with the slow tokenizer, twice.
skip_element! finds the end of <sheetData> via the fast _skip_element_raw (~340 samples). But XML.splicetext → sourcespan → sourcetext(::LazyNode) re-tokenises the entire element with the full tokenizer to find the same end position (~2,200 samples). This runs once at load in process_file and again in eachrow after first_cache_fill!, where it rebuilds the stub that was overwritten when the raw XML was swapped in.
Four separate attribute scans per <c>.
get(c, "r"), get(c, "t"), get(c, "s") and get(c, "cm") each re-scan the start tag. cm is rarely present, so its lookup always scans to the end of the tag. v0.12.1 used a single XML.foreach_attr pass. This regressed in 0.12.2.
Each cell is tokenised more than once.
first_cache_fill! calls skip_element!(c2), then Cell re-tokenises the same bytes through eachchildnode and is_simple_value.
Per-row Vector{Any} in TableRow, then pushes into per-column Vector{Any}s. Minor.

Proposed work (in priority order)

XML.jl: make sourcetext(::LazyNode) for elements use _skip_element_raw to find the end. This fixes splicetext/sourcespan for all callers. (Separate XML.jl PR.)
XLSX.jl: keep the splitNode stub from process_file and restore it after first_cache_fill! rather than recomputing it. Alternatively, splice using cursor positions directly.
XLSX.jl: restore the single-pass attribute read in Cell(::LazyNode, …).
XLSX.jl: consider walking <c> children from the cursor in one pass (collecting <v>, <f>, <is>) instead of skip_element! followed by a re-parse.
XLSX.jl (minor): build table columns directly instead of per-row Vector{Any}s.
Re-profile after the first three items. Expected saving is ~4,000–4,500 samples (~40%), which should bring 0.12 to around or below 0.10's time.

Benchmark

Need a large synthetic fixture that doesn't depend on the reporter's file. For example, a generated sheet with ~1M rows × 80 columns mixing shared strings, numbers and t="str" formula cells. Keep it out of the test suite and put it in benchmark/.

Out of scope

The missing-value discrepancy in #460 (0 vs 1,385 in one column) is a separate issue.

Update

Groundwork done ahead of the main investigation.

Upstream. Raised JuliaData/XML.jl#157 for the sourcetext(::LazyNode)
item (item 1 above), framed as a suggestion rather than a defect — the current
implementation is correct, and the difference only shows on very large elements.
Awaiting a response there.

New direction: skip cell materialisation in readtable entirely.
readtable only ever takes a file path or an IO, builds an XLSXFile
internally and discards it on return. So the cache it fills is consumed solely
by the loop that immediately converts each cell to a value and throws it away.
Nothing downstream can observe it. That makes the cache pure overhead for this
entry point, on top of the tokenising costs already listed.

A value-only sequential reader would need r, t, s and <v> from a single
pass — plus the SST, lazily, only if a t="s" cell appears — and no Cell
structs, no per-row Dict, no WorksheetCache, no formula cache. Rough
estimate: 35–45% off readtable's time (17s → ~8–10s) and roughly an order of
magnitude off the 1 GB, with inflate, the load-time splitNode and the SST as
the remaining floor.

Constraints: internal-only, non-breaking, all kwargs keeping identical
behaviour. enable_cache stays in the signature and becomes a no-op on this
path (no observable change today either way), but must still be honoured by the
table_name and range variants, which stay on the existing path initially.
process_tv and the datatype/style decisions stay shared rather than
reimplemented — #460 is a good illustration of where a second implementation
would quietly diverge.

Differential harness. Built one that compares readtable against
gettable(sheet) on an open file — the latter stays on the existing path, so
it works as an oracle with no test-only flags. Covers every sheet of every file
in test/data across 24 kwarg combinations (header, stop_in_empty_row,
keep_empty_rows, first_row, infer_eltypes), comparing column names,
eltypes, values (with isequal, so missing matches missing) and exception
types. Currently reports All agree, so it's a known-good baseline to rewrite
against.

Bug found while baselining. The first run showed 112 mismatches, all Strict
OOXML files, all readtable throwing KeyError where the oracle succeeded.
convert_strict_to_transitional! loops over keys(xf.files) and indexes
xf.data[filename], but when target_sheet is set only the target worksheet is
decompressed, so every other sheet's key is absent. This breaks readtable on
any multi-sheet Strict file, and affects the other target_sheet callers too.
Fixed with a haskey(xf.data, filename) || continue guard; raised separately as part of
#461 with a regression test. Unrelated to performance, but it's why the
harness is worth keeping.

Next steps, when this comes off the back burner: do the single-pass
attribute read first (small, self-contained, measurable on its own), then the
value-only iterator, re-running the harness after each.

Read cell attributes in a single decoded pass (added to #461)

Cell(::LazyNode, ...) read r, t, s and cm with four get calls,
each re-scanning the start tag. cm is almost never present, so its lookup
always scanned to the end of the tag.

Use XML.eachattribute instead: one pass, same decoding as get (character
references and §3.3.3 whitespace normalisation), works for any LazyNode
rather than LazyNode{String} only, and a plain loop so nothing is boxed.

This is deliberately not a revert to the earlier foreach_attr version:
XMLTokenizer.attr_value performs no reference resolution or whitespace
normalisation, and the closure there boxed its captured variables.

426ns -> 172ns per cell in isolation; ~8.7% off readtable on a 4M-cell
benchmark, allocations unchanged. Differential check against gettable
across test/data passes unchanged.

The discarded first worksheet-stub scan is removed for cached read-only opens in merged #469. In its included compressed numeric benchmark (20,000 rows × eight columns, Julia 1.13.1/M3, 14 worker threads and bounds checking), the median open time falls from 34.42 ms to 5.72 ms; allocations are essentially unchanged. Full Julia 1.10.12/1.13.1 suites and all applicable head/main CI jobs pass.

The original large XLSM file has not been retested, and the value-only reader work remains separate. Keeping this issue open for the remaining work.

AI disclosure: This work was prepared with assistance from OpenAI Codex.

@nilshg

I've now merged a PR (#471) that addresses every one of the proposed actions above. In my benchmarks, the code on #master is now consistently faster than v0.10.4 and, specifically in a synthetic spreadsheet built to match the private example that triggered this issue, it is much faster.

Fixtures modelled on the workbook in #462 - readtable(file, "Data", "A:CF"; first_row=5):

	0.10.4	0.12.0	0.13.0	this PR
Julia 1.13	5.23 s	3.84 s	5.14 s	2.04 s
Julia 1.10	7.00 s	5.64 s	—	2.76 s
allocations	4.97 GB	1.68 GB	1.16 GB	0.61 GB

Against v0.13.0, across the full suite:

readxlsx/open: 0.32–0.47x;
writetable: 0.56–0.81x;
"rw" open: 0.67–0.85x;
eachrow: unchanged.

@nilshg, it would be great if you were able to test this on your pathological file.

Fix read-performance regression (#462) (#471)

Navigation Menu

Repository navigation

Read-performance regression vs v0.10.4 on large worksheets #462