sc/source/core/tool/compiler.cxx | 225 +++++++++++++++++++++++++++------------
1 file changed, 157 insertions(+), 68 deletions(-)
New commits:
commit 18608dfa02000f1702eeb6f971001cacdbd662b3
Author: Noel Grandin <noel.gran...@collabora.co.uk>
AuthorDate: Wed Dec 28 17:30:12 2022 +0200
Commit: Thorsten Behrens <thorsten.behr...@allotropia.de>
CommitDate: Mon Feb 3 13:33:43 2025 +0100
optimise SUMPRODUCT(IF..) where we have entire column/row ranges
following the same pattern as for SUM, set those ranges to shrink to
available data.
Takes it from 3s to <500ms on my test spreadsheet.
Change-Id: I53ada996393a063b12ef7dd0f9bff40c90ecc8be
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/144850
Tested-by: Jenkins
Reviewed-by: Noel Grandin <noel.gran...@collabora.co.uk>
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/180973
Reviewed-by: Thorsten Behrens <thorsten.behr...@allotropia.de>
Tested-by: allotropia jenkins <jenk...@allotropia.de>
Tested-by: Thorsten Behrens <thorsten.behr...@allotropia.de>
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index 17a1cce61cd0..1a6833204f0b 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -6267,89 +6267,178 @@ void ScCompiler::AnnotateTrimOnDoubleRefs()
// OpCode of the "root" operator (which is already in RPN array).
OpCode eOpCode = (*(pCode - 1))->GetOpCode();
// eOpCode can be some operator which does not change with operands with
or contains zero values.
- if (eOpCode != ocSum)
- return;
+ if (eOpCode == ocSum)
+ {
+ FormulaToken** ppTok = pCode - 2; // exclude the root operator.
+ // The following loop runs till a "pattern" is found or there is a
mismatch
+ // and marks the push DoubleRef arguments as trimmable when there is a
match.
+ // The pattern is
+ // SUM(IF(<reference|double>=<reference|double>, <then-clause>)<a some
operands with operators / or *>)
+ // such that one of the operands of ocEqual is a double-ref.
+ // Examples of formula that matches this are:
+ // SUM(IF(D:D=$A$1,F:F)*$H$1*2.3/$G$2)
+ // SUM((IF(D:D=$A$1,F:F)*$H$1*2.3/$G$2)*$H$2*5/$G$3)
+ // SUM(IF(E:E=16,F:F)*$H$1*100)
+ bool bTillClose = true;
+ bool bCloseTillIf = false;
+ sal_Int16 nToksTillIf = 0;
+ constexpr sal_Int16 MAXDIST_IF = 15;
+ while (*ppTok)
+ {
+ FormulaToken* pTok = *ppTok;
+ OpCode eCurrOp = pTok->GetOpCode();
+ ++nToksTillIf;
+
+ // TODO : Is there a better way to handle this ?
+ // ocIf is too far off from the sum opcode.
+ if (nToksTillIf > MAXDIST_IF)
+ return;
- FormulaToken** ppTok = pCode - 2; // exclude the root operator.
- // The following loop runs till a "pattern" is found or there is a mismatch
- // and marks the push DoubleRef arguments as trimmable when there is a
match.
- // The pattern is
- // SUM(IF(<reference|double>=<reference|double>, <then-clause>)<a some
operands with operators / or *>)
- // such that one of the operands of ocEqual is a double-ref.
- // Examples of formula that matches this are:
- // SUM(IF(D:D=$A$1,F:F)*$H$1*2.3/$G$2)
- // SUM((IF(D:D=$A$1,F:F)*$H$1*2.3/$G$2)*$H$2*5/$G$3)
- // SUM(IF(E:E=16,F:F)*$H$1*100)
- bool bTillClose = true;
- bool bCloseTillIf = false;
- sal_Int16 nToksTillIf = 0;
- constexpr sal_Int16 MAXDIST_IF = 15;
- while (*ppTok)
- {
- FormulaToken* pTok = *ppTok;
- OpCode eCurrOp = pTok->GetOpCode();
- ++nToksTillIf;
-
- // TODO : Is there a better way to handle this ?
- // ocIf is too far off from the sum opcode.
- if (nToksTillIf > MAXDIST_IF)
- return;
+ switch (eCurrOp)
+ {
+ case ocDiv:
+ case ocMul:
+ if (!bTillClose)
+ return;
+ break;
+ case ocPush:
- switch (eCurrOp)
- {
- case ocDiv:
- case ocMul:
- if (!bTillClose)
- return;
- break;
- case ocPush:
+ break;
+ case ocClose:
+ if (bTillClose)
+ {
+ bTillClose = false;
+ bCloseTillIf = true;
+ }
+ else
+ return;
+ break;
+ case ocIf:
+ {
+ if (!bCloseTillIf)
+ return;
- break;
- case ocClose:
- if (bTillClose)
- {
- bTillClose = false;
- bCloseTillIf = true;
- }
- else
+ if (!pTok->IsInForceArray())
+ return;
+
+ const short nJumpCount = pTok->GetJump()[0];
+ if (nJumpCount != 2) // Should have THEN but no ELSE.
+ return;
+
+ OpCode eCompOp = (*(ppTok - 1))->GetOpCode();
+ if (eCompOp != ocEqual)
+ return;
+
+ FormulaToken* pLHS = *(ppTok - 2);
+ FormulaToken* pRHS = *(ppTok - 3);
+ if (((pLHS->GetType() == svSingleRef ||
pLHS->GetType() == svDouble) && pRHS->GetType() == svDoubleRef) ||
+ ((pRHS->GetType() == svSingleRef ||
pRHS->GetType() == svDouble) && pLHS->GetType() == svDoubleRef))
+ {
+ if (pLHS->GetType() == svDoubleRef)
+ pLHS->GetDoubleRef()->SetTrimToData(true);
+ else
+ pRHS->GetDoubleRef()->SetTrimToData(true);
+ return;
+ }
+ }
+ break;
+ default:
return;
- break;
- case ocIf:
- {
- if (!bCloseTillIf)
- return;
+ }
+ --ppTok;
+ }
+ }
+ else if (eOpCode == ocSumProduct)
+ {
+ FormulaToken** ppTok = pCode - 2; // exclude the root operator.
+ // The following loop runs till a "pattern" is found or there is a
mismatch
+ // and marks the push DoubleRef arguments as trimmable when there is a
match.
+ // The pattern is
+ // SUMPRODUCT(IF(<reference|double>=<reference|double>,
<then-clause>)<a some operands with operators / or *>)
+ // 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))
+ bool bTillClose = true;
+ bool bCloseTillIf = false;
+ sal_Int16 nToksTillIf = 0;
+ constexpr sal_Int16 MAXDIST_IF = 15;
+ while (*ppTok)
+ {
+ FormulaToken* pTok = *ppTok;
+ OpCode eCurrOp = pTok->GetOpCode();
+ ++nToksTillIf;
+
+ // TODO : Is there a better way to handle this ?
+ // ocIf is too far off from the sum opcode.
+ if (nToksTillIf > MAXDIST_IF)
+ return;
- if (!pTok->IsInForceArray())
+ switch (eCurrOp)
+ {
+ case ocDiv:
+ case ocMul:
+ {
+ if (!pTok->IsInForceArray())
+ break;
+ FormulaToken* pLHS = *(ppTok - 1);
+ FormulaToken* pRHS = *(ppTok - 2);
+ StackVar lhsType = pLHS->GetType();
+ StackVar rhsType = pRHS->GetType();
+ if (lhsType == svDoubleRef && rhsType == svDoubleRef)
+ {
+ pLHS->GetDoubleRef()->SetTrimToData(true);
+ pRHS->GetDoubleRef()->SetTrimToData(true);
+ }
+ }
+ break;
+ case ocPush:
+ break;
+ case ocClose:
+ if (bTillClose)
+ {
+ bTillClose = false;
+ bCloseTillIf = true;
+ }
+ else
return;
+ break;
+ case ocIf:
+ {
+ if (!bCloseTillIf)
+ return;
- const short nJumpCount = pTok->GetJump()[0];
- if (nJumpCount != 2) // Should have THEN but no ELSE.
- return;
+ if (!pTok->IsInForceArray())
+ return;
- OpCode eCompOp = (*(ppTok - 1))->GetOpCode();
- if (eCompOp != ocEqual)
- return;
+ const short nJumpCount = pTok->GetJump()[0];
+ if (nJumpCount != 2) // Should have THEN but no ELSE.
+ return;
- FormulaToken* pLHS = *(ppTok - 2);
- FormulaToken* pRHS = *(ppTok - 3);
- if (((pLHS->GetType() == svSingleRef || pLHS->GetType() ==
svDouble) && pRHS->GetType() == svDoubleRef) ||
- ((pRHS->GetType() == svSingleRef || pRHS->GetType() ==
svDouble) && pLHS->GetType() == svDoubleRef))
- {
- if (pLHS->GetType() == svDoubleRef)
+ OpCode eCompOp = (*(ppTok - 1))->GetOpCode();
+ if (eCompOp != ocEqual)
+ return;
+
+ FormulaToken* pLHS = *(ppTok - 2);
+ FormulaToken* pRHS = *(ppTok - 3);
+ StackVar lhsType = pLHS->GetType();
+ StackVar rhsType = pRHS->GetType();
+ if (lhsType == svDoubleRef && (rhsType == svSingleRef
|| rhsType == svDouble))
+ {
pLHS->GetDoubleRef()->SetTrimToData(true);
- else
+ }
+ if ((lhsType == svSingleRef || lhsType == svDouble) &&
rhsType == svDoubleRef)
+ {
pRHS->GetDoubleRef()->SetTrimToData(true);
+ }
return;
}
- }
- break;
- default:
- return;
+ break;
+ default:
+ return;
+ }
+ --ppTok;
}
- --ppTok;
}
-
- return;
}
/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
