How to improve time consumed by HiperFormula.buildFromSheets?

[rackuka]

I have excel workbook which I parse and provide to HyperFormula as dictionary (Record<string, string[][]>) of array of arrays. It takes 30-35 seconds for the buildFromSheets to complete loading. Profiling shows that the most of the time is consumed by buildGraph.

This time is not acceptable at all for my case where hyperformula is supposed to serve as calculation engine in server environment with the workbook be the calculation model. Latency for processing calculation request should exceed 1 second (including network latencies). So appears that loading the workbook every time is not an option (35 seconds to load).

I would appreciate for the advise as to how to improve overall time of buildFromSheets (if possible at all).

[AMBudnik]

Hi @rackuka

Sorry for keeping you waiting and thank you for the feedback. I've gathered some tips from my colleagues that I wanted to share with you.

1. try to avoid including empty/unused cells in the ranges if they are irrelevant. For example, even if you know that there are no cells below the 100-th row and SUM(A1:A100) gives the same result as SUM(A1:A200), we need to inspect the other cells and be prepared that they might change in the future, in particular, references to the whole columns (e.g. SUM(A:B)/VLOOKUP(A:B,) are very costly and avoid them if possible.
2. use dateFormats: [], timeFormats: [] if you don't need time and date coercions, this may also slightly speed up
3. for bulk data change you can also benefit from suspending the execution of some of the scripts as described in the following tutorial https://handsontable.github.io/hyperformula/guide/performance.html#suspending-automatic-recalculations

this.hf.suspendEvaluation()
for (let i = 0; i < this.outputRows * sampleRatio; i++) {
  this.hf.setCellContents({ sheet: this.outputsSheetId, row: i, col: 0 }, [[Math.ceil(Math.random() * this.outputRows)]])
}
this.hf.resumeEvaluation()

We are working on a tutorial that helps optimize the calculation engine's overall performance. But I think that those points are essentials.