Skip to content

Add calculation cache to improve CalcCellValue performance #2144

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
wants to merge 2 commits into
base: master
Choose a base branch
from
Open

Add calculation cache to improve CalcCellValue performance #2144

wants to merge 2 commits into from

Conversation

DengY11
Copy link
Contributor

@DengY11 DengY11 commented May 25, 2025

PR Details

Add calculation cache to improve CalcCellValue performance

Description

Implements a calculation cache mechanism for CalcCellValue to significantly improve performance when calculating formulas repeatedly. The cache stores calculated results and automatically invalidates when cells are modified.

Related Issue

Fixes #2057 - Performance enhancement - cache calculated values

Motivation and Context

As described in #2057, Excelize doesn't cache calculated values from CalcCellValue, causing expensive formulas to be recalculated every time they are referenced by other cells. This leads to poor performance when dealing with complex formula dependencies.

Example scenario:

A1: 40
A2: =VERYEXPENSIVEFUNCTION()  // takes 100ms
A3: =A1+A2                    // takes 100ms (recalculates A2)

Solution implemented:

  • Cache calculated results in thread-safe sync.Map
  • Clear entire cache when any SetCellX() function is called
  • Cache key format: "sheet!cell"

Performance improvement:

  • First calculation: ~150µs (calculated and cached)
  • Second calculation: ~600ns (from cache, 250x faster)

How Has This Been Tested

go test -v -run "TestCalc.*Cache|TestSetFunctionsClearCache"
go test ./...

Test coverage includes:

  • ☑️ Basic cache hit/miss scenarios
  • ☑️ Cache invalidation on cell modifications
  • ☑️ Multi-cell formula dependencies
  • ☑️ All 12 Set functions properly clear cache
  • ☑️ Thread safety
  • ☑️ 3 new comprehensive test functions added

Types of changes

☑️ New feature (non-breaking change which adds functionality)
☐ Docs change / refactoring / dependency upgrade
☐ Bug fix (non-breaking change which fixes an issue)
☐ Breaking change (fix or feature that would cause existing functionality to change)

Checklist

☑️ My code follows the code style of this project.
☐ My change requires a change to the documentation.
☐ I have updated the documentation accordingly.
☑️ I have read the CONTRIBUTING document.
☑️ I have added tests to cover my changes.
☑️ All new and existing tests passed.

Implementation Details

Modified Files:

  • excelize.go: Added cache fields (calcCache sync.Map, calcCacheMu sync.RWMutex) to File struct
  • calc.go: Implemented caching logic in CalcCellValue() + added clearCalcCache() helper
  • cell.go: Added cache clearing to all 12 Set functions
  • calc_test.go: Added 3 comprehensive cache test functions

@xuri xuri added the size/L Denotes a PR that changes 100-499 lines, ignoring generated files. label May 25, 2025
@codecov Codecov
Copy link

codecov bot commented May 25, 2025 •
edited
Loading

Codecov Report

All modified and coverable lines are covered by tests ✅

Project coverage is 99.23%. Comparing base (b65c16b) to head (2512d3c).

Additional details and impacted files 
@@           Coverage Diff           @@
##           master    #2144   +/-   ##
=======================================
  Coverage   99.23%   99.23%           
=======================================
  Files          32       32           
  Lines       30276    30317   +41     
=======================================
+ Hits        30045    30086   +41     
  Misses        153      153           
  Partials       78       78
Flag Coverage Δ
unittests 99.23% <100.00%> (+<0.01%) ⬆️

Flags with carried forward coverage won't be shown. Click here to find out more.

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.

🚀 New features to boost your workflow:
  • ❄️ Test Analytics: Detect flaky tests, report on failures, and find test suite problems.

xuri
xuri requested changes May 26, 2025
View reviewed changes
Copy link
Member

@xuri xuri left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Thanks for your PR. We need add any cache cautiously, after these changes we also need remove cache in following scenario:

  • RemoveCol
  • RemoveRow
  • DuplicateRow
  • DuplicateRowTo
  • SetSheetCol

And update or delete cache when:

  • SetSheetName
  • DeleteSheet

Consider if other scenario needed.

@DengY11
Copy link
Contributor Author

DengY11 commented May 27, 2025 •
edited
Loading

Thanks for your PR. We need add any cache cautiously, after these changes we also need remove cache in following scenario:

  • RemoveCol
  • RemoveRow
  • DuplicateRow
  • DuplicateRowTo
  • SetSheetCol

And update or delete cache when:

  • SetSheetName
  • DeleteSheet

Consider if other scenario needed.

Thanks for your quick respond!
sorry for missing a few spots where we needed to clear the CalcCellValue cache in my first commit. After taking another look and with some great input, I think we've got all a_bases covered now.
Here’s a quick rundown of where we’ve added the f.clearCalcCache() calls:
These are the functions we sorted out earlier, based on what was initially asked for and our first round of checks:
RemoveCol ✅
RemoveRow ✅
DuplicateRow ✅
DuplicateRowTo ✅
SetSheetCol ✅
SetSheetName ✅
DeleteSheet ✅
SetCellValue ✅
SetCellInt ✅
SetCellUint ✅
SetCellBool ✅
SetCellFloat ✅
SetCellStr ✅
SetCellDefault ✅
SetCellFormula ✅
SetCellRichText ✅
SetCellHyperLink ✅

New additions in this commit:
While double-checking things more recently, we found a few more functions that also mess with the sheet in ways that would need the cache to be cleared. So, I've added the cache clearing to these in the latest changes:
MergeCell ✅
UnmergeCell ✅
InsertCols ✅
InsertRows ✅
CopySheet ✅
MoveSheet ✅

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@xuri xuri xuri requested changes

Assignees
No one assigned
Labels
size/L Denotes a PR that changes 100-499 lines, ignoring generated files.
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

Performance enhancement - cache calculated values
2 participants
@DengY11 @xuri