Back — 03 Software Engineering
Ipsos — rebuilding the Excel export engine
A financial export that crashed past 50,000 rows, rewritten as pure streaming: from 3–4 GB of memory down to 100–200 MB.
Context
An internal financial portal in C# / ASP.NET, hosted on IIS with SQL Server, used by management controllers in more than 90 countries. Team spread across Europe, North America and India; work in English, Jira, agile cadence.
The Excel export engine threw OutOfMemoryException as soon as a report
went past 50,000 rows. Users worked around it by splitting exports by hand — when they
remembered to.
Diagnosis
V1 loaded the entire SQL result set into a DataTable, then let EPPlus
assemble the workbook in memory. The data therefore existed twice, and the large string
arrays went straight onto the Large Object Heap, which fragments and is not
compacted.
3 to 4 GB of memory consumed for 50 MB of actual data. Crash at 45 s on the largest reports.
The rewrite
Rewritten as pure streaming: the data set is never fully materialised in memory.
- Open XML SDK in SAX mode — rows are written as they come instead of being assembled into a tree.
IDataReaderinSequentialAccess— data is read column by column from SQL Server, with no intermediate buffer.- Direct
FileStreamwrites — the file is built on disk, not in RAM. - A single indexed stylesheet — formats are declared once and referenced, rather than recreated per cell.
- No-allocation parsers on the hot paths, to avoid producing millions of temporary objects.
- Two-pass predictive auto-fit — column widths are estimated during streaming, without re-reading the file.
- Recompression of the
.xlsxon output.
Outcome
Memory flat at 100–200 MB, whatever the report size.
1 million rows exported in ~2 minutes, where V1 crashed after 45 seconds.
OOM incidents disappeared from the production logs.
Files 20–30% smaller after recompression.
Second workstream
Refactored a Python forecasting pipeline: robustness against degraded input data, structured logs usable in production, and encapsulation behind an API to decouple the pipeline from its callers.
What I took from it
The win did not come from micro-optimisation but from a change of model: stop materialising. And working in English across three time zones teaches you to write tickets and commits that stand on their own, because nobody will be around to explain them.