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
@Luboš Luňák I think you may be interested in this perf issue.
I bibisected using the repo "linux-64-7.4-CN" from: https://go.suokunlong.cn:88/dl/libreoffice/bibisect/ I set a stopwatch of 1 min, and mark the commits of response time > 1 min as "bisect bad". (Actually those >1min would seem to take several minutes to respond, so I killed the process. Those <1min responded within 50s most of the time) The bisect result points to the following range: 9896f330c..bcd27039e Which includes one source commit: commit bcd27039eec660fce2abd7604cdb8cd36ae4cdba Author: Noel Grandin <noelgrandin@gmail.com> Date: Sat May 28 13:36:41 2022 +0200 use the new SfxWhichIter::GetItemState feature to speed up other places that are iterating over SfxItemSets Adding cc Noel Grandin: could you please take a look? Thanks.
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
Created attachment 182039 [details] Flamegraph Here's a Flamegraph retrieved on pc Debian x86-64 with master sources updated today + gen rendering.
(In reply to Kevin Suo from comment #2) Ops... That seems to be an unsuccessful bibisect. Please ignore. Re-testing...
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.
34s with the patch on: https://gerrit.libreoffice.org/c/core/+/138880
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.
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.
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)
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.
(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?
(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.
(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.
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.
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.