A benchmark used during testing of revamped CalcEngine

ClosedXml-CalcEngine-Benchamark.cs

using System.Collections.Generic;
using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Running;
using ClosedXML.Excel;
using NUnit.Framework;

namespace ClosedXML.Sandbox
{
    [TestFixture]
    [RPlotExporter]
    [MemoryDiagnoser]
    public class FormulaBenchmark
    {
        private const string SingleCellCalculationFormula = "1 * 2 / (\"7\" + 8 - (1 + 6 * ( 2 * 3)))";
        private const string SingleCellFunctionFormula = "SUM(1,2,3, IF(4 = 4, 5, 6))";
        private readonly XLWorkbook _wb;
        private IXLWorksheet _ws;
        private XLCell _cellSingleCalc;
        private XLCell _cellSingleFormula;
        private XLCell _cellSingleRangeFormula;
        private XLWorkbook _parseWb;
        private XLWorksheet _parseWs;
        private XLCell _cellDoubleRangeFormula;


        private const int TestIterations = 1;

        // Expression cache is using weak references, but the references are not stored anywhere else, so when GC runs, it collects them.
        private List<object> keepCacheAlive = new();

        public FormulaBenchmark()
        {
            _wb = new XLWorkbook();
            _ws = _wb.AddWorksheet();
            _cellSingleCalc = (XLCell)_ws.Cell("AA1");
            _cellSingleCalc.FormulaA1 = SingleCellCalculationFormula;

            keepCacheAlive.Add(_wb.CalcEngine._cache[SingleCellCalculationFormula]);
            _ = _cellSingleCalc.Value;

            _cellSingleFormula = (XLCell)_ws.Cell("AA2");
            _cellSingleFormula.FormulaA1 = SingleCellFunctionFormula;
            keepCacheAlive.Add(_wb.CalcEngine._cache[SingleCellFunctionFormula]);
            _ = _cellSingleFormula.Value;

            _ws.Cells("A1:Z100").Value = 1;
            _cellSingleRangeFormula = (XLCell)_ws.Cell("AA3");
            var singleRangeFormula = "SUM(A1:Z100)";
            _cellSingleRangeFormula.FormulaA1 = singleRangeFormula;
            keepCacheAlive.Add(_wb.CalcEngine._cache[SingleCellFunctionFormula]);
            _ = _cellSingleRangeFormula.Value;

            _ws.Cells("A101:Z200").FormulaA1 = "SUM(1,2)";
            _cellDoubleRangeFormula = (XLCell)_ws.Cell("AA4");
            var doubleRangeFormula = "SUM(A101:Z200)";
            keepCacheAlive.Add(_wb.CalcEngine._cache[doubleRangeFormula]);
            _cellDoubleRangeFormula.FormulaA1 = doubleRangeFormula;
            _ = _cellDoubleRangeFormula.Value;


            _parseWb = new XLWorkbook();
            _parseWs = (XLWorksheet)_parseWb.AddWorksheet("Empty");
            //_parseWs.CalcEngine.CacheExpressions = true;
        }
        
        [Test]
        [Benchmark]
        public void SingleCellCalculationEvaluation()
        {
            for (var i = 0; i < TestIterations; ++i)
            {
                _cellSingleCalc.NeedsRecalculation = true;
                _ = _cellSingleCalc.Value;
            }
        }

        [Test]
        [Benchmark]
        public void SingleCellFunctionEvaluation()
        {
            for (var i = 0; i < TestIterations; ++i)
            {
                _cellSingleFormula.NeedsRecalculation = true;
                _ = _cellSingleFormula.Value;
            }
        }

        [Test]
        [Benchmark]
        public void SingleCellRangeEvaluation()
        {
            for (var i = 0; i < TestIterations; ++i)
            {
                _cellSingleRangeFormula.NeedsRecalculation = true;
                _ = _cellSingleRangeFormula.Value;
            }
        }

        [Test]
        [Benchmark]
        public void DoubleCellRangeEvaluation()
        {
            for (var i = 0; i < TestIterations; ++i)
            {
                _cellDoubleRangeFormula.NeedsRecalculation = true;
                for (var row = 101; row <= 200; ++row)
                    for (var col = 1; col <= 26; ++col)
                        ((XLCell)_ws.Cell(row, col)).NeedsRecalculation = true;

                _ = _cellDoubleRangeFormula.Value;
            }
        }

        [Test]
        [Benchmark]
        public void SingleCellCalculationParsing()
        {
            for (var i = 0; i < TestIterations; ++i)
                _ = _parseWs.CalcEngine.Parse(SingleCellCalculationFormula);
        }

        [Test]
        [Benchmark]
        public void SingleCellFunctionParsing()
        {
            for (var i = 0; i < TestIterations; ++i)
                _ = _parseWs.CalcEngine.Parse(SingleCellFunctionFormula);
        }

    }

    internal static class Program
    {
        private static void Main(string[] args)
        {
#if true
            var summary = BenchmarkRunner.Run<FormulaBenchmark>();
#else
            var benchmark = new FormulaBenchmark();
            for (var i = 0; i < 100; ++i)
            {
                benchmark.SingleCellCalculationEvaluation();
                benchmark.SingleCellFunctionEvaluation();
                benchmark.SingleCellRangeEvaluation();
                benchmark.DoubleCellRangeEvaluation();
            }
            //benchmark.SingleCellCalculationParsing();
            //benchmark.SingleCellFunctionParsing();
#endif
        }
    }
}