Bug 150618 - Serious performance regression: it takes 5min+ for SUMIFS formula recalculation when switching sheet (while it only takes 30s in 7.3)
Summary: Serious performance regression: it takes 5min+ for SUMIFS formula recalculati...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.4.0.0 alpha0+
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:7.5.0 target:7.4.1
Keywords: bibisected, bisected, perf, regression
Depends on:
Blocks:
 
Reported: 2022-08-26 08:23 UTC by Kevin Suo
Modified: 2022-09-18 17:54 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Cost Calculation Worksheet - Public Redacted.ods (4.04 MB, application/vnd.oasis.opendocument.spreadsheet)
2022-08-26 08:23 UTC, Kevin Suo
Details
Flamegraph (18.37 KB, application/x-bzip)
2022-08-26 12:16 UTC, Julien Nabet
Details
Flamegraph (70.61 KB, application/x-bzip)
2022-08-29 20:10 UTC, Julien Nabet
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2022-08-26 08:23:29 UTC
Created attachment 182038 [details]
Cost Calculation Worksheet - Public Redacted.ods

Attached is a redacted version of the Cost Calculation Worksheet I have prepared in one of our recent projects, for cost accounting purpose. The worksheet contains many SUMIFS formulas to summaries the cost data at different levels, generate the Finished Goods inventory movement schedule, Raw Materials inventory movement schedule, as well as calculating the cost of finished goods inventory-in on monthly basis.

Today when I am working on this worksheet using LibreOffice 7.4, I found that there is a serious performance regression. The following operation takes more than 5 minutes for LibrOffice 7.4 to respond, while it only took around 30s in 7.3 branch.

Steps to Reproduce:
1. Open the attached ODS file.
2. Right-click on tab "FG_Production_Inventory_In_Summary", then "Insert Sheets", select "After the current sheet" at the Location section of the Insert Sheets dialog.
--> A new sheet is inserted between tabs "FG_Production_Inventory_In_Summary" and "FG_Inventory_Movement". This is good.
3. Switch to tab "FG_Inventory_Movement".

Current Result:
It hangs for 5min for step 3 to respond. I loose patient and killed the process.

Expected Result:
Less than 30s as in LibreOffice 7.3.

Bad in:
Version: 7.4.1.0.0+ / LibreOffice Community
Build ID: 710ea67c100b1e81453258e65d16412f1e98a8f2
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: zh-CN (zh_CN.UTF-8); UI: zh-CN
Calc: threaded
Comment 1 Kevin Suo 2022-08-26 09:13:15 UTC
@Luboš Luňák I think you may be interested in this perf issue.
Comment 2 Kevin Suo 2022-08-26 11:38:27 UTC Comment hidden (obsolete)
Comment 3 Telesto 2022-08-26 11:54:40 UTC
Confirm
Version: 7.5.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 1c1647e6ee252fe68d7406d01043e88f1721590f
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win
Locale: nl-NL (nl_NL); UI: nl-NL
Calc: CL
Comment 4 Julien Nabet 2022-08-26 12:16:56 UTC
Created attachment 182039 [details]
Flamegraph

Here's a Flamegraph retrieved on pc Debian x86-64 with master sources updated today + gen rendering.
Comment 5 Kevin Suo 2022-08-26 12:21:13 UTC Comment hidden (obsolete)
Comment 6 Kevin Suo 2022-08-26 12:32:06 UTC
First bad commit:

commit 7674399aac661eb503d7badc53b9a4d68bd9839d
Author: Luboš Luňák
Date:   Fri May 27 19:51:40 2022 +0200

    try to range-reduce even COUNTIFS if not matching empty cells

17 without this commit, and 6 minutes 20 seconds with this commit.

Luboš would you please take a look? Thanks.
Comment 7 Kevin Suo 2022-08-27 03:55:44 UTC
34s with the patch on:
https://gerrit.libreoffice.org/c/core/+/138880
Comment 8 Commit Notification 2022-08-29 08:14:12 UTC
Luboš Luňák committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/690458889fd875a7885b692d3204c45c5016b925

fix undoing range reducing to apply only to COUNTIFS (tdf#150618)

It will be available in 7.5.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Kevin Suo 2022-08-29 17:32:20 UTC
Verified fixed on master, although there is still room for improvements - 30s waiting is too long provided that inserting a tab and then switching to another is a common operation.
Comment 10 Julien Nabet 2022-08-29 20:10:47 UTC
Created attachment 182079 [details]
Flamegraph

Here's an updated Flamegraph if we can do a bit more than just come back to the initial 30s which is indeed a lot of time.
(I just noticed this log when generating the svg:
dso__load_sym_internal: failed to find program header for symbol: _etext st_value: 0x25371)
Comment 11 Luboš Luňák 2022-08-30 08:09:33 UTC
This most probably takes 30s because the document uses <> operator in lookup operations, which is currently not optimized with ScSortedRangeCache because it needs two ranges in the sorted data (each side of the matching data) rather than just one as the other operators. This would need implementing that in queryiter.cxx in Calc. I personally have no plans to do that as I think that's actually not a common case.
Comment 12 Kevin Suo 2022-08-30 10:34:02 UTC
(In reply to Luboš Luňák from comment #11)

> This most probably takes 30s because the document uses <> operator in lookup operations...

Maybe off-topic, but do you have any idea what settings should I apply to avoid the use of <> operator in lookup operations, so that I can try to optimize my formula?
Comment 13 Julien Nabet 2022-08-30 10:44:41 UTC
(In reply to Luboš Luňák from comment #11)
> This most probably takes 30s because the document uses <> operator in lookup
> operations, which is currently not optimized with ScSortedRangeCache because
> it needs two ranges in the sorted data (each side of the matching data)
> rather than just one as the other operators. This would need implementing
> that in queryiter.cxx in Calc. I personally have no plans to do that as I
> think that's actually not a common case.

Thank you for the detailed feedback.
So perhaps it could be interesting to create an enhancement bug for the optimization of <> operator.
Comment 14 Luboš Luňák 2022-08-30 14:53:29 UTC
(In reply to Kevin Suo from comment #12)
> (In reply to Luboš Luňák from comment #11)
> 
> > This most probably takes 30s because the document uses <> operator in lookup operations...
> 
> Maybe off-topic, but do you have any idea what settings should I apply to
> avoid the use of <> operator in lookup operations, so that I can try to
> optimize my formula?

The way to stop a formula from using <> is to modify the formula, there's no option for that. Look e.g. at the SUMIFS formula in columns M and N in sheet FG Cost Calculation 2020-02.
Comment 15 Commit Notification 2022-09-01 11:11:27 UTC
Luboš Luňák committed a patch related to this issue.
It has been pushed to "libreoffice-7-4":

https://git.libreoffice.org/core/commit/e20d52afe5a33ae9490e238e7b5833d3df68f004

fix undoing range reducing to apply only to COUNTIFS (tdf#150618)

It will be available in 7.4.2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2022-09-09 13:38:31 UTC
Luboš Luňák committed a patch related to this issue.
It has been pushed to "libreoffice-7-4-1":

https://git.libreoffice.org/core/commit/276037edb6f98ec1fa64e8740958668a1b30cef2

fix undoing range reducing to apply only to COUNTIFS (tdf#150618)

It will be available in 7.4.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.