Bug 144159 - COUNTIFS() function performance regression
Summary: COUNTIFS() function performance regression
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.2.0.1 rc
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected, haveBacktrace, perf, regression
Depends on:
Blocks: Calc-Function Calc-Threaded
  Show dependency treegraph
 
Reported: 2021-08-29 14:21 UTC by adnanbaloch
Modified: 2021-09-29 15:30 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Excel file and two screenshots for version 7.1.5 and 7.2.0 (14.25 MB, application/zip)
2021-08-30 12:53 UTC, adnanbaloch
Details
Perf flamegraph (64.35 KB, image/svg+xml)
2021-09-15 07:37 UTC, Buovjaga
Details
Screenshots showing task manager CPU utilization for 7.1.6.2 and 7.2.0.4 (129.15 KB, application/zip)
2021-09-16 10:33 UTC, adnanbaloch
Details
7.2.0.4 performance with Hyperthreading disabled (205.22 KB, image/png)
2021-09-16 12:06 UTC, adnanbaloch
Details

Note You need to log in before you can comment on or make changes to this bug.
Description adnanbaloch 2021-08-29 14:21:51 UTC
Description:
I have an Excel sheet with data in 187,000 rows. I use this sheet to benchmark Calc to see if countifs() calculation speed has improved with a new version. I calculate countifs() with two criteria and fill 10,000 rows with the results. Previously, it would take around 350 seconds to complete. Now, even for 500 rows, the countifs() formula is not able to finish the calculation after more than 120 seconds. 

Steps to Reproduce:
1.Create large number of random data at least in at two columns with 187,000 rows.
2.Run countifs with two criteria on the data to return result in 10,000 rows.

Actual Results:
countifs() keeps on running and does not return any result even after more than 6 minutes.

Expected Results:
It used to return the results within 350 seconds on my i7-4770. Also tried it on a newer laptop with Core i3-115G4 CPU. Calc 7.1.5 completes the calculation in 326 seconds on the i3. Calc 7.2.0.4 doesn't complete even after 6 minutes.


Reproducible: Always


User Profile Reset: Yes


OpenGL enabled: Yes

Additional Info:
OpenGL version 3.0 to 4.3 features are enabled.
Comment 1 Roman Kuznetsov 2021-08-29 20:16:18 UTC
Please attach an example file
Comment 2 m.a.riosv 2021-08-29 21:26:30 UTC
And please past here the information in Menu/About LibreOffice (there is a button to do it).

With version working fine and version working slow.
Comment 3 adnanbaloch 2021-08-30 12:53:04 UTC
Created attachment 174642 [details]
Excel file and two screenshots for version 7.1.5 and 7.2.0

Excel file and two screenshots for version 7.1.5 and 7.2.0
Comment 4 adnanbaloch 2021-08-30 12:56:26 UTC
The screenshots show the time taken for pasting the formula from cell H2 to cells H3 to H101. Version information is given below:

(Working fine)
Version: 7.1.5.2 (x64) / LibreOffice Community
Build ID: 85f04e9f809797b8199d13c421bd8a2b025d52b5
CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: en-AE (en_AE); UI: en-US
Calc: CL


(Working slow)
Version: 7.2.0.4 (x64) / LibreOffice Community
Build ID: 9a9c6381e3f7a62afc1329bd359cc48accb6435b
CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: en-AE (en_AE); UI: en-US
Calc: CL
Comment 5 Xisco Faulí 2021-08-30 14:32:57 UTC
it takes 20 seconds in

Version: 7.3.0.0.alpha0+ / LibreOffice Community
Build ID: 6669c1a06bcc3ce31c9c17c714fdf2c2e0e25360
CPU threads: 4; OS: Linux 5.10; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

while it takes 5 seconds in

Version: 7.1.0.0.alpha1+
Build ID: 738bcf5e9a8c443d60c29c3a8068e8c16c72638a
CPU threads: 4; OS: Linux 5.10; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
Comment 6 Buovjaga 2021-09-15 07:37:59 UTC
Created attachment 175027 [details]
Perf flamegraph

Filled down from H2 to H101 in countifs.xlsx

Version: 7.3.0.0.alpha0+ / LibreOffice Community
Build ID: e6a8d312d3d7e5d81c56d5ccc0508116dd283f1f
CPU threads: 8; OS: Linux 5.14; UI render: default; VCL: kf5 (cairo+xcb)
Locale: fi-FI (fi_FI.UTF-8); UI: en-US
Calc: threaded
Comment 7 Roman Kuznetsov 2021-09-15 19:07:31 UTC
Noel, could you please look at it? I mean may be you can find a root of the perf problem? even without regression user should wait for 5 minutes for formula inserting
Comment 8 adnanbaloch 2021-09-16 10:33:11 UTC
Created attachment 175061 [details]
Screenshots showing task manager CPU utilization for 7.1.6.2 and 7.2.0.4

The screenshots show different CPU utilization behavior by the different versions. 7.1.6.2 seems to be using all eight cores whereas 7.2.0.4 is maximally using the physical cores and minimally using the logical cores. Maybe some change was introduced that conflicts with hyperthreading?
Comment 9 adnanbaloch 2021-09-16 12:06:21 UTC
Created attachment 175070 [details]
7.2.0.4 performance with Hyperthreading disabled

Performance improved by 10 seconds when HT is disabled. With HT enabled, it takes about 23 seconds.
Comment 10 adnanbaloch 2021-09-16 12:23:34 UTC
With HT disabled, 7.1.6.2 completes calculation in 4 seconds whereas 7.2.0.4 needs 13 seconds to complete the same calculation. Was there a change in the compiler version or compiler options that may have led to this discrepancy?
Comment 11 Buovjaga 2021-09-16 14:28:51 UTC
Bibisected with linux-64-7.2 to
https://git.libreoffice.org/core/commit/3069df790cca2917e5aedd87bac1af65f9605d51
tdf#142910 sc filter: fix "greater than" or "smaller than" etc

Adding Cc: to Balazs Varga
Comment 12 Eike Rathke 2021-09-27 13:26:46 UTC
Can likely be fixed along with bug 144740.
Comment 13 Eike Rathke 2021-09-28 00:35:23 UTC
The fix for bug 144740 removes massively obtaining the number formats and the RoundValueAsShown() calls in this scenario, that also show up in the flamegraph.

I don't have a non-debug performance measurement build at hand, but in the debug build I don't see a significant improvement, so the cause may still be something else. Please test in a daily build with the fix from https://bugs.documentfoundation.org/show_bug.cgi?id=144740#c1

Unassigning myself here.
Comment 14 Buovjaga 2021-09-28 07:01:08 UTC
I verify the perf problem is gone

Version: 7.3.0.0.alpha0+ / LibreOffice Community
Build ID: 4dd6af856d574ad66ebb4b822a36ba70af9945e2
CPU threads: 8; OS: Linux 5.14; UI render: default; VCL: kf5 (cairo+xcb)
Locale: fi-FI (fi_FI.UTF-8); UI: en-US
Calc: threaded
Comment 15 adnanbaloch 2021-09-28 16:51:35 UTC
Issue is fixed in the daily build. Thank you.

Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: b60b6bfaafa1315e07108dba50f016975b619c59
CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: en-AE (en_AE); UI: en-US
Calc: CL
Comment 16 Xisco Faulí 2021-09-29 15:30:49 UTC
*** Bug 144777 has been marked as a duplicate of this bug. ***