Performance and Limitations in Windows Forms Calculation Engine

26 Apr 20215 minutes to read

This section explains about the performance and limitations of Essential Calculate.

To improve the performance

By default, CalcEngine calculates the formulas quickly. But to improve the performance in Essential Calculate, user need to set the following
properties of CalcEngine.

AllowShortCircuitIFs

If the application contains more nested if formulas for calculation, then setting AllowShortCircuitIFs property to true computes the formulas
quickly.

CalculatingSuspended

If there are more number of dependent cells in the formulas for calculation, then user can set CalculatingSuspended property to true,
to suspend calculations while a series of changes are made to dependent cells either by the user or programmatically.

UseFormulaValues

To avoid the repeated calculations for a formula cell which contains more dependency cells, set UseFormulaValues property to true to
return the formula value stored in FormulaInfoTable.

Parse Formula and Compute Formula

ParseFormula method in CalcEngine parses the formula in the cell and user can
reset the value of variables or dependent cell values of formula cell at runtime and get the updated result by computing the parsed formula using
ComputeFormula method which avoids the repeated parsing of formula.

For Example:

//Class derived from ICalcData,
CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

//Set values to the variables
calcData.SetValueRowCol(100, 1, 1); 
calcData.SetValueRowCol(200, 1, 2);
calcData.SetValueRowCol(140, 2, 2);
calcData.SetValueRowCol(120, 3, 2);
calcData.SetValueRowCol(100, 4, 2);  

//Parsing the formula,
var parsedFormula = engine.ParseFormula("=SUM(A1:E4)"); 

//Computing the value of parsed formula,
string result = engine.ComputeFormula(parsedFormula);

//Computes the nested IF formulas quickly,
engine.AllowShortCircuitIFs = true;

//To avoid the repeated calculations,
engine.UseFormulaValues = true;

// Turn off calculations,
engine.CalculatingSuspended = true;

// Makes multiple updates to cells involved in calculation
for (int i = 0; i < 5000; i++)
{
    for (int j = 0; j < 5000; j++)
    {
        calcData.SetValueRowCol(random.Next(5) + 1,i,j);
    }
}

// Turn on calculations,
engine.CalculatingSuspended = false;

//Again computing the value of parsed formula,
result = engine.ComputeFormula(parsedFormula);

To avoid stack overflow exception

To avoid the stack overflow exception while computing the formulas iteratively exceeding the maximum capacity, user need to set the following properties of CalcEngine,

ThrowCircularException

Setting this property to true will throw an exception when the circular exception is encountered and also
avoids the recursive looping.

IterationMaxCount

CalcEngine provides an option to set iterative calculations and maximum iteration count to calculate the formula having circular references.
The default value is 0 indicating that iterative calculation support is turned off. ThrowCircularException property will be automatically set
to true when you set a non-zero value to IterationMaxCount.

MaximumRecursiveCalls

CalcEngine have MaximumRecursiveCalls property for which value is 100 by default. So, if the recursive call during calculations exceeds more than 100,
stack overflow exception occurs which can be avoided by setting this property to required limit at the sample level.

MaxStackDepth

CalcEngine have MaxStackDepth property for which value is 50 by default. So, if the stack size is exceeding more than 50, stack overflow exception
occurs which can be avoided by setting this property to required limit at the sample level.

//Class derived from ICalcData,
CalcData calcData = new CalcData();

CalcEngine engine = new CalcEngine(calcData);

CalcEngine.MaxStackDepth = 10000;   
engine.MaximumRecursiveCalls = 10000;
engine.IterationMaxCount = 10000;

If the stack overflow exception occurs even after setting these properties, user need to calculate the value in the thread by
specifying the maximum stack size (Default stack size is 1 Mb. Throws exception if exceeds).

private string calValue1;
CalcEngine engine;  
private void Main()  
{   
    //Class derived from ICalcData,
    CalcData calcData = new CalcData();

    engine = new CalcEngine(calcData);
    calValue1 = String.Empty;   
    var maxSize = 10000000; 

    var thread = new Thread(GetCalculatedValue, maxSize);   
    thread.Start();   
    thread.Join();   

    MessageBox.Show("val:" + calValue1);   
}   
   
private void GetCalculatedValue()   
{        
    engine.UseFormulaValues = true;   
    engine.MaximumRecursiveCalls = 10000;   
    CalcEngine.MaxStackDepth = 10000; 

    //Computing the value of formula cell "D1",
    calValue1 = engine.ParseAndComputeFormula("D1");   
}

Limitations

  • Essential Calculate is not a UI component, but it can be can be added to the user’s own business objects.

  • Essential Calculate does not support to localize the registered formulas in the Calculate API to any specific language other
    than English(en-US), which is by default. But we have support to compute the formulas in different region settings.

  • The parameters passed in any of the function library of Essential Calculate is of type string.