sc/qa/unit/ucalc_formula.cxx | 2 +-
sc/source/core/tool/compiler.cxx | 35 +++++++++++++++++++++++++++++++++++
2 files changed, 36 insertions(+), 1 deletion(-)
New commits:
commit ba0ec4a5d2b025b675410cd18890d1cca3bc5a2f
Author: Balazs Varga <balazs.varga.ext...@allotropia.de>
AuthorDate: Wed Mar 20 18:32:44 2024 +0100
Commit: Balazs Varga <balazs.varga.ext...@allotropia.de>
CommitDate: Fri Mar 22 09:23:58 2024 +0100
tdf#159687 sc formula SUMPRODUCT performance fix: add more binary
operators which need to be checked if they are next to a trimmable
DoubleRef arguments or not.
Example:
=SUMPRODUCT(($D:$D>M47:M47)*($D:$D<M48:M48)*($I:$I=N$41)) -->
$D:$D and $I:$I columns are trimmable.
Recalculation of formulas with a lot of SUMPRODUCT where we comparing
full columns could take minutes during editing a sheet. With reducing
the size of the compared ranges to the actual data could significantly
speed up the recalculation.
This takes the recalculation time from ~50 sec to <1 sec on my machine.
Note: probabaly the same could be applied to the SUM function.
Change-Id: I758660d0b638ef7255bd5a41a96755289b5a2b41
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/165074
Tested-by: Jenkins
Reviewed-by: Noel Grandin <noel.gran...@collabora.co.uk>
Reviewed-by: Balazs Varga <balazs.varga.ext...@allotropia.de>
diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index 105c7e4a772d..adbc9c121042 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -1464,7 +1464,7 @@ CPPUNIT_TEST_FIXTURE(TestFormula,
testFormulaAnnotateTrimOnDoubleRefs)
{
"=SUMPRODUCT(A:A=$C$1; 1-(A:A=$C$1))",
- ScRange(-1, -1, -1, -1, -1, -1), // Has no trimmable
double-ref.
+ ScRange(0, 0, 0, 0, 1048575, 0),
0.0,
false // Not in matrix mode.
},
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index 5e0258b3fd21..152a75f949b1 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -6564,6 +6564,8 @@ void ScCompiler::AnnotateTrimOnDoubleRefs()
// such that one of the operands of ocEqual is a double-ref.
// Examples of formula that matches this are:
// SUMPRODUCT(IF($A:$A=$L12;$D:$D*G:G))
+ // Also in case of DoubleRef arguments around other Binary operators
can be trimmable:
+ // SUMPRODUCT(($D:$D>M47:M47)*($D:$D<M48:M48)*($I:$I=N$41))
bool bTillClose = true;
bool bCloseTillIf = false;
sal_Int16 nToksTillIf = 0;
@@ -6597,6 +6599,39 @@ void ScCompiler::AnnotateTrimOnDoubleRefs()
}
}
break;
+ case ocEqual:
+ case ocAdd:
+ case ocSub:
+ case ocAmpersand:
+ case ocPow:
+ case ocNotEqual:
+ case ocLess:
+ case ocGreater:
+ case ocLessEqual:
+ case ocGreaterEqual:
+ case ocAnd:
+ case ocOr:
+ case ocXor:
+ case ocIntersect:
+ case ocUnion:
+ case ocRange:
+ {
+ if (!pTok->IsInForceArray())
+ break;
+ FormulaToken* pLHS = *(ppTok - 1);
+ FormulaToken* pRHS = *(ppTok - 2);
+ StackVar lhsType = pLHS->GetType();
+ StackVar rhsType = pRHS->GetType();
+ if (lhsType == svDoubleRef && (rhsType == svSingleRef
|| rhsType == svDoubleRef))
+ {
+ pLHS->GetDoubleRef()->SetTrimToData(true);
+ }
+ if (rhsType == svDoubleRef && (lhsType == svSingleRef
|| lhsType == svDoubleRef))
+ {
+ pRHS->GetDoubleRef()->SetTrimToData(true);
+ }
+ }
+ break;
case ocPush:
break;
case ocClose:
