Performance

[gilidio8]

Hi friend, how are you? I ended up finding a performance problem in the formulas when I reference another cell, I'll send you the code I used as an example:

protected override void OnAfterRender(bool firstRender)
{   
    Sheet = new Sheet(900, 100);
    Sheet.Commands.PauseHistory();
    Sheet.BatchUpdates();

    for (int row = 0; row <= 900; row++)
    {
        
        for (int col = 0; col <= 100; col++)
        {  
            if(row < 14)
                Sheet.Cells.SetFormula(row, col, $"=(({row}+{col}*{3})/100)");

            if (row >= 14 )
            {
                Sheet.Cells.SetFormula(row, col, $"=(B{row} + 5)");
            }
        }
    }
    
    Sheet.EndBatchUpdates();
    Sheet.Commands.ResumeHistory();
    base.OnAfterRender(firstRender);
}

I used it in Formula.razor If you run it without the if (row < 14 ) just with Sheet.Cells.SetFormula(row, col, $"=(({row}+{col}*{3})/100)") it will be super fast!

If you use Sheet.Cells.SetFormula(row, col, $"=(B{row} + 5)") it will take 5 minutes. Do you know what it could be?

[anmcgrath]

Hi @gilidio8 thanks for the details. I haven't spent much time optimizing the formula engine at this stage. The slow method here uses cell references which is causing the speed issues.

Each time we're setting a formula here it's looking at the cells that it references and updating dependencies. The implementation of GetVerticesInRegion currently searches through all formula vertices naively which gets pretty large in your code.

[Edi0306]

Hello, friend. How are you? Do you have an estimate of when you will release a new version with the performance adjustment mentioned above? Thank you.

[anmcgrath]

Hi @Edi0306 no reason I couldn't release this, so I have as 0.7.2

[Edi0306]

@anmcgrath . Thank you very much.

[anmcgrath]

Are you happy for me to close this issue now? I imagine there are still some performance issues with formulae but nothing specific at this stage?

[Edi0306]

@anmcgrath . Hello, my friend. Good morning! I apologize for the delay in responding. The time has improved a lot, as you said, and there is still room for improvement, especially in terms of memory consumption. I think it is worth keeping this situation on the radar for future improvements.