Posted 7 February 2023, 3:47 am EST
Hi,
We experience poor performance in setting multiple cell formulas. Consider the following benchmark code (where we test against a competing product):
using System;
using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Jobs;
using GCExcel = GrapeCity.Documents.Excel;
using SSG = <competing product>;
namespace Benchmark
{
[MemoryDiagnoser]
[SimpleJob(RuntimeMoniker.Net472, baseline: true)]
public class FormulaTest
{
private SSG.IWorksheet _wsSSG;
private GCExcel.IWorksheet _wsGC;
[Params(10, 100)]
public int Size { get; set; }
[GlobalSetup]
public void Setup()
{
GCExcel.Workbook.SetLicenseKey("----------------");
var wbSet = SSG.Factory.GetWorkbookSet();
wbSet.Calculation = SSG.Calculation.Manual;
var wb = wbSet.Workbooks.OpenFromMemory(Array.Empty<byte>());
_wsSSG = (SSG.IWorksheet)wb.ActiveSheet;
var wbGC = new GCExcel.Workbook { EnableCalculation = false };
_wsGC = wbGC.ActiveSheet;
}
[Benchmark]
public void ValueSSG()
{
for (var c = 1; c <= Size; c++)
_wsSSG.Cells[1, c].Value = c;
}
[Benchmark]
public void ValueGC()
{
for (var c = 1; c <= Size; c++)
_wsGC.Cells[1, c].Value = c;
}
[Benchmark]
public void FormulaSSG()
{
for (var c = 1; c <= Size; c++)
_wsSSG.Cells[1, c].Formula = $"={c}";
}
[Benchmark]
public void FormulaGC()
{
for (var c = 1; c <= Size; c++)
_wsGC.Cells[1, c].Formula = $"={c}";
}
}
}
This is the results:
BenchmarkDotNet=v0.13.4, OS=Windows 11 (10.0.22621.1194)
12th Gen Intel Core i7-1260P, 1 CPU, 16 logical and 12 physical cores
[Host] : .NET Framework 4.8.1 (4.8.9105.0), X86 LegacyJIT
.NET Framework 4.7.2 : .NET Framework 4.8.1 (4.8.9105.0), X86 LegacyJIT
Job=.NET Framework 4.7.2 Runtime=.NET Framework 4.7.2
| Method | Size | Mean | Error | StdDev | Ratio | Gen0 | Gen1 | Allocated | Alloc Ratio |
|----------- |----- |-------------:|------------:|------------:|------:|--------:|-------:|----------:|------------:|
| ValueSSG | 10 | 723.9 ns | 14.03 ns | 19.67 ns | 1.00 | 0.1297 | - | 681 B | 1.00 |
| | | | | | | | | | |
| ValueGC | 10 | 3,542.8 ns | 67.53 ns | 69.34 ns | 1.00 | 1.8959 | 0.0076 | 9944 B | 1.00 |
| | | | | | | | | | |
| FormulaSSG | 10 | 5,130.3 ns | 99.69 ns | 88.38 ns | 1.00 | 0.2518 | - | 1326 B | 1.00 |
| | | | | | | | | | |
| FormulaGC | 10 | 43,306.0 ns | 824.20 ns | 846.39 ns | 1.00 | 9.6436 | - | 50739 B | 1.00 |
| | | | | | | | | | |
| ValueSSG | 100 | 7,529.7 ns | 148.71 ns | 293.53 ns | 1.00 | 1.2970 | - | 6810 B | 1.00 |
| | | | | | | | | | |
| ValueGC | 100 | 31,144.1 ns | 531.78 ns | 444.06 ns | 1.00 | 17.8833 | 0.5493 | 93931 B | 1.00 |
| | | | | | | | | | |
| FormulaSSG | 100 | 54,702.9 ns | 1,087.24 ns | 1,017.01 ns | 1.00 | 2.5635 | - | 13589 B | 1.00 |
| | | | | | | | | | |
| FormulaGC | 100 | 436,664.9 ns | 3,405.25 ns | 3,185.27 ns | 1.00 | 96.6797 | - | 509354 B | 1.00 |
As you can see, the GrapeCity Documents for Excel (ValueGC and FormulaGC methods) are up to 8 times slower than the competing product.
Memory consumption is anywhere from 15-40(!) times higher that the competing product.
Is there any recommendations you have to improve performance?
