Skip to content

Instantly share code, notes, and snippets.

@jahav
Created September 4, 2022 17:02
Show Gist options
  • Save jahav/5716ab8cd590bc05d42d1991b7e231a7 to your computer and use it in GitHub Desktop.
Save jahav/5716ab8cd590bc05d42d1991b7e231a7 to your computer and use it in GitHub Desktop.
A benchmark used during testing of revamped CalcEngine
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
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment