HyperFormula (v3.4.0)

View as Markdown

# Performance

We implemented various techniques to boost the performance of HyperFormula. In some cases, turning them on or off might increase the performance of your app. Below we provide a number of tips on how to speed it up.

# VLOOKUP/MATCH

If you are planning to use VLOOKUP or MATCH heavily in your app, you may consider enabling the useColumnIndex flag in the HyperFormula configuration. It will increase memory usage but can significantly improve the performance of these two functions, especially when running on unsorted or very large data sets. The column index will not be used despite the option useColumnIndex enabled when using wildcards or regular expressions.

Leaving this option disabled will cause the engine to use binary search when dealing with sorted data, and the naive approach otherwise.

# Address mapping strategies

HyperFormula uses two approaches to store the mapping of cell addresses in order to optimize memory usage. The choice of the strategy is made independently for each sheet. The chooseAddressMappingPolicy option allows for changing the way the strategy will be chosen.

You may use one of three built-in policies:

  • AlwaysDense – uses dense mapping for each sheet. This policy is particularly useful when the spreadsheet is a densely filled rectangle.
  • AlwaysSparse – uses sparse mapping for each sheet. This approach is useful when in your spreadsheet/dataset there are relatively few cells filled, but located very far from each other.
  • DenseSparseChooseBasedOnThreshold – the choice is made based on the fill ratio of the sheet. Let the engine choose the best strategy for you.

# Lazy transformation cleanup

Structural operations (adding/removing rows/columns, moving cells) create transformations that are applied lazily to formulas. Over time, these transformations accumulate in memory. HyperFormula automatically flushes them when their count reaches the maxPendingLazyTransformations threshold (default: 50).

You can tune this setting to balance memory usage and CPU overhead:

  • Lower values (e.g., 10) reduce peak memory usage but trigger cleanup more frequently, adding slight CPU overhead per flush.
  • Higher values (e.g., 200) reduce the frequency of cleanup but allow more memory to accumulate between flushes.
  • The default of 50 works well for most use cases.
const hf = HyperFormula.buildEmpty({
  licenseKey: 'gpl-v3',
  maxPendingLazyTransformations: 100,
})

# Parser cache size

HyperFormula reuses parsed formulas to avoid repeating parsing work. The maxParserCacheSize option limits this cache to 10,000 entries by default. Once full, the cache discards the least recently used entries as new formulas are parsed.

For long-running instances that process many distinct formulas, a smaller limit reduces retained cache memory at the cost of more parsing work. Set the limit to 0 to disable parser caching. Formula calculation, undo/redo, and clipboard operations continue to work with any supported cache limit.

The limit counts cache entries, not bytes or cells. Formulas still needed by the workbook, undo/redo history, or clipboard remain in memory independently of the cache. Use undoLimit to control the length of undo history separately.

# Suspending automatic recalculations

By default, HyperFormula recalculates formulas after every change. However, due to the fact that we store the graph of dependencies between cells in the sheet, we recalculate only the cells affected by the update.

Sometimes, a simple change can cause recalculation of a large part of the sheet, e.g., when the modified cell is at the very beginning of the dependency chain or when there are many volatile functions in the worksheet. In such a case you may want to postpone the recalculation.

The first option is to call suspendEvaluation before making changes and resumeEvaluation at a convenient moment.

The second option is to pass the callback function with multiple operations to a batch function. Recalculation will be suspended before performing operations and resumed after them. In cases where you perform operations which may not cause a recalculation but only change the shape of the worksheet, like addRows, removeRows, or moveColumns , we do not recommend suspending recalculation, as this may have a slightly negative impact on performance.

← Known limitations →