Why SQL Beats Formulas Once the Data Gets Large and Slow

Everyone has seen the workbook that takes a minute to open and ten seconds to respond to a keystroke. The cause is almost always formulas over a lot of rows. The usual advice is to move the data to a database, and it is right for reasons that go beyond speed. This post explains where the time goes in a spreadsheet, why a database avoids most of it, and what you gain besides performance. It makes no timing claims, because the numbers depend on the workbook, the machine and the engine, and I have not benchmarked a specific case. It is a spoke of translating Excel formulas into SQL.

Where a spreadsheet spends its time

A workbook is a graph. Every formula cell depends on other cells, and when an input changes, Excel recalculates the cells that depend on it, and the cells that depend on those. Four things make that expensive as data grows.

Lookups scan. A VLOOKUP or MATCH with exact match looks for the key by walking the lookup range until it finds it. Put 100,000 lookups against a table of 100,000 rows and the worst case is 100,000 times 100,000, which is ten billion comparisons (10,000,000,000). The average is about half that. Excel has added optimizations for lookups over the years, caching sorted ranges for some functions, and they help a great deal in some cases and not in others, so the observed cost varies. The shape of the work, rows times rows, is what to remember.

Everything is in memory. A workbook loads every sheet, every cell and every formula into memory when it opens. A million cells with formulas and formats is hundreds of megabytes. The 32-bit version of Excel is limited to a couple of gigabytes of address space, and large workbooks hit that limit with an out-of-memory message.

Recalculation is wide. Changing one cell near the top of a chain, such as a running balance that depends on the row above, recalculates everything below it. Volatile functions (NOW, TODAY, RAND, OFFSET, INDIRECT) recalculate on every change anywhere, so a single volatile function at the head of a long chain makes the whole chain recalculate on every keystroke.

The grid has limits. A sheet holds 1,048,576 rows (Excel row and column limits), and a workbook that outgrows it has to be split, with the lookups between the pieces.

What a database does instead

A database does not treat a calculation as a grid of cells. It receives a statement of what you want and plans how to get it.

Joins use hash tables or indexes. For a join between two tables, the planner can build a hash table of the smaller side and probe it once for each row of the larger side, so the work is roughly the sum of the sizes (100,000 plus 100,000 operations, in the example above, not ten billion), or it can use an index on the key. A lookup that was a scan per row becomes a probe per row.

Only the needed data is touched. A query that sums one column reads that column. Columnar engines such as DuckDB read only the columns named, and row engines use indexes to skip rows. The data stays on disk and is pulled through memory in pieces, so the size of the table is not bounded by RAM in the same way.

Aggregation is one pass. A GROUP BY over a million rows reads them once and accumulates the groups. A thousand SUMIF formulas over the same rows read the range a thousand times.

The work is parallel. Modern engines spread a large scan across cores. Excel can use multiple threads for recalculation, with the dependency structure limiting how much parallelism it finds.

None of this makes a database faster for 200 rows. Starting a query has overhead, and a spreadsheet responds instantly to a small change. The crossover is somewhere in the tens of thousands of rows for lookup-heavy workbooks, and depends entirely on the formulas.

An illustration with numbers you can check

Suppose 50,000 orders each look up a customer in a table of 5,000 customers with an exact-match lookup. In the worst case that is 50,000 times 5,000, or 250 million comparisons. A hash join does about 5,000 operations to build the table and 50,000 to probe it: 55,000, a ratio of more than 4,500 to 1 in operation count. The real ratio in time is smaller, because a comparison in a tight loop is cheap and the hash join has its own overheads, and nobody should quote the ratio as a speedup. The point is how the two grow: double both tables, and the scan quadruples while the join doubles.

Beyond speed

Performance gets the attention, and four other reasons usually matter more.

Repeatability. A query runs the same way every time on whatever data is there. A workbook is run by a person who may paste data in the wrong place, forget to extend a range or leave a filter on. The first time a quarter-end number is wrong because a formula range stopped one row short, you will want the query.

Auditability. SQL can be read top to bottom, diffed between versions and reviewed in a pull request. A formula spread across a thousand cells with a few overrides pasted as values cannot be reviewed; the logic is invisible in the grid.

Shared access. A database serves many readers and writers with permissions and transactions. A workbook on a shared drive has one writer at a time and the last save wins.

Integration. The output of SQL feeds other systems directly: reports, APIs, dashboards. A workbook is an endpoint that someone has to export.

Testing. Queries can have tests with known inputs and expected outputs. A workbook can too, in principle, and almost none do.

These are the reasons to translate a workbook even when it is not slow. Speed is the symptom that makes people notice.

Where formulas still win

A fair comparison includes the cases where the spreadsheet is right.

Small data. With a few hundred rows, a spreadsheet is faster to build, easier to see and good enough.

Exploration. Trying an idea, dragging a column, coloring cells and charting a pattern is faster in a grid. People should explore in Excel and then move the logic that survives.

Interactive models. What-if analysis with inputs that a person changes and watches is the spreadsheet's design center. Goal seek, scenario tables and data tables have no good SQL equivalent.

Presentation. A formatted statement or a chart with annotations is a document. Produce the numbers in SQL and place them.

Users who live in it. A finance team that works in Excel all day and checks every number there will trust a workbook they can click through. Meet them with a workbook fed by a query (sending database results to Excel properly every time).

A practical threshold

You do not need a number to decide, but symptoms help. Consider moving the logic to SQL when any of these is true:

• The workbook takes longer than a few seconds to recalculate after a change.

• The data no longer fits in a sheet, or is split across sheets to fit.

• More than one person needs the numbers, or the same numbers are produced every period.

• A mistake in the numbers would cost real money.

• The logic needs an owner and a history.

When two of those are true, the case is strong. Start with the slowest or most important formula, translate it with a method that checks the result (translating Excel formulas into SQL), and keep the workbook as a viewer fed by the database or retire it.

How to find out for your workbook

Do not take any of this on faith. To see where a workbook spends its time, open it with calculation set to manual, change one input and press F9 while watching the status bar, or use Excel's formula auditing to see the dependency chain. Count the lookups and their ranges. Disable volatile functions temporarily and see whether the sluggishness disappears. If you load the data into DuckDB or a database and run the equivalent query (loading Excel files with DuckDB is a one-statement way to try), compare. Then you have a measurement for your case, which is worth more than any general claim.

When formulas are still the better choice

The data is small and will stay small. The cost of a database exceeds the benefit.

The audience edits the logic. If analysts change the formulas weekly, SQL puts an engineer in their way.

The output is a document. A formatted workbook is the product.

You have no database and no one to run it. A tool nobody can maintain is worse than a slow workbook that people understand.

Microsoft describes calculation performance in its guidance on improving calculation performance, and PostgreSQL describes how it plans joins in the documentation on the planner. To check a small extract as JSON, use the CSV to JSON converter.