Bug 159687 - Cutting rows from specific file with whole column references in formula is slow
Summary: Cutting rows from specific file with whole column references in formula is slow
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.0.3 release
Hardware: All All
: medium normal
Assignee: Balázs Varga (allotropia)
URL:
Whiteboard: target:24.8.0
Keywords: perf
Depends on:
Blocks: Cell-Formula
  Show dependency tree / graph
 
Reported: 2024-02-12 09:36 UTC by Gabor Kelemen (allotropia)
Modified: 2024-04-10 22:36 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
Example file from Calc (29.24 KB, application/vnd.oasis.opendocument.spreadsheet)
2024-02-12 09:36 UTC, Gabor Kelemen (allotropia) 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen (allotropia) 2024-02-12 09:36:31 UTC 
Created attachment 192513 [details]
Example file from Calc

Attached file contains lots of formula referencing whole columns, such as 
=SUMPRODUCT(($F:$F=$M2)*($I:$I=N$1))
When a row is deleted, the recalculating takes a long time and Calc becomes irresponsible for about 40 seconds on my Windows virtual machine.

0. To make this more visible, disable Options - Calc - Calculate - Enable multi-threaded calculation, restart LO
1. Open attached file
2. On the FEHLER tab right click on any row header, choose Delete row
-> Calc recalculates for a long time

Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: aa6d11771d085803cdb811579f47debc30c4d94b
CPU threads: 15; OS: Windows 10.0 Build 19045; UI render: default; VCL: win
Locale: hu-HU (hu_HU); UI: en-US
Calc: default

This seems to have started with 

https://git.libreoffice.org/core/+/0c8778ce1df92ca3bc2a8dd2f64568fb257e9e39

author	Eike Rathke <erack@redhat.com>	Fri Jun 26 15:12:46 2015 +0200
committer	Eike Rathke <erack@redhat.com>	Fri Jun 26 15:19:52 2015 +0200

tdf#44419 allow A:A and 1:1 references also in Calc A1 and ODF syntax

so it's not a real regression, just a downside of a new feature.
Comment 1 Werner Tietz 2024-02-12 10:13:37 UTC 
seems … changing EVERY Formular similar to:

=SUMPRODUCT(($F:$F=$M2)*($I:$I=N$1))

to

=COUNTIFS($F:$F;$M2;$I:$I;N$1)

solves the issue?
Comment 2 m_a_riosv 2024-02-15 12:49:21 UTC 
I think the issue should be with SUMPRODUCT that does not shortcut the calculation to the last row with data while other functions does, like COUNTIF or SUM.
Comment 3 Commit Notification 2024-03-22 08:24:12 UTC 
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

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

tdf#159687 sc formula SUMPRODUCT performance fix: add more binary

It will be available in 24.8.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 4 m_a_riosv 2024-04-10 15:10:55 UTC 
Reported tdf#160616, about wrong calculations