Excel sheet takes a long time (7 seconds) for workbook.calculateFormula() in Linux with Java 21 (maybe earlier Java versions)

Aspose.Cells Product Family
1

Would love to know what’s taking the time. Attempting to reproduce this with Java 21 in Windows the calculation takes a little over a second. Maybe a little long but reasonable. On Linux this extends to 7 seconds, which seems like something is wrong.

Is it possible to attach the worksheet?

2

@dberkman

It sounds like you’re experiencing a significant performance difference when using workbook.calculateFormula() on Linux compared to Windows. This issue can stem from various factors, including differences in Java versions, system resources, and the complexity of formulas. Here’s a structured approach to diagnose and improve the performance:

  1. Cause: The performance discrepancy may be due to differences in Java versions, system resources, or the complexity of the formulas in your workbook.

  2. Solution:

    • Check Java Version: Ensure that you are using the same version of Java on both Linux and Windows, as performance can vary between versions.
    • Enable Calculation Chain: If your workbook contains many formulas, enabling the calculation chain can improve performance for subsequent calculations. Set workbook.Settings.FormulaSettings.EnableCalculationChain to true. If you only need to calculate once, consider keeping it disabled.
      workbook.Settings.FormulaSettings.EnableCalculationChain = true; // or false based on your needs
    • Profile the Calculation: Use a profiler to identify which formulas or operations are taking the most time during the calculation. This can help pinpoint specific issues.
    • Optimize Formulas: Review and simplify complex or inefficient formulas in your workbook. Breaking them into smaller parts can enhance performance.
    • System Resources: Check the system resources on your Linux machine to ensure it has sufficient CPU and memory available, as resource constraints can lead to slower performance.
    • Testing with Sample Workbooks: Create a simplified version of your workbook with fewer formulas to see if the performance issue persists. This can help determine if the problem is related to specific formulas or the overall workbook structure.

  3. Next Steps: Implement the suggested solutions and monitor the performance. If the issue persists, consider providing specific details about the formulas or the workbook structure for further analysis.

Please let me know if these steps help or if you need further assistance!

3

@dberkman

Please archive the template file as zip and then attach it here so we can perform some tests and try to figure the issue out. Thank you.

4

badmodel.zip (2.6 MB)

Attached. Thank you.

5

@dberkman

There are many complicated formulas(such as shared formulas with recursive references between each other) which require deep stacks during the formula calculation. Would you please try to set smaller stack size for formula calculation(CalcStackSize) to see whether you can get better performance for linux environment:

                ...
                CalculationOptions copts = new CalculationOptions();
                copts.CalcStackSize = 50;
                workbook.CalculateFormula(copts);
6

Thank you for your help. Yes, setting the calculation stack size to 50 worked like a charm. Would it be possible to explain the consequences of that? I did not write the excel sheet causing the issue, but I am responsible for the runtime environment, and the expectations of the author are that it runs as it would in excel. I am limiting something, and I suspect part of what I must be limiting is fidelity?

7

@dberkman,

The CalcStackSize parameter determines the maximum depth of the evaluation stack used for formula calculations in Aspose. Unlike the Java call stack, this is an internal mechanism specific to Aspose for handling nested formulas and their dependencies. While Excel itself does not explicitly define a stack depth limit, it supports deep recursive and circular references.

Predicting the appropriate stack size can be challenging as it depends on the complexity and structure of formulas in each workbook. The drawback can be Formulas requiring evaluation depths exceeding 50 levels may not calculate correctly. To address this and be on safer side, you can set a relatively high value for CalcStackSize, then monitor performance and results using Aspose’s debugging or logging tools. If performance issues arise or results are unsatisfactory, gradually reduce the value and re-evaluate until optimal results are achieved.