Bug 84763 - Poor performance entering data with conditional formatting
Summary: Poor performance entering data with conditional formatting
Status: RESOLVED INSUFFICIENTDATA
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.2.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Conditional-Formatting
  Show dependency tree / graph
 
Reported: 2014-10-07 17:17 UTC by rlk
Modified: 2024-02-03 03:15 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
Spreadsheet demonstrating poor performance entering text with conditional formatting applied (2.30 MB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-07 17:17 UTC, rlk 		Details
Simpler spreadsheet not demonstrating the problem (597.47 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-07 17:20 UTC, rlk 		Details
Simpler spreadsheet demonstrating problem to a lesser degree (1.30 MB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-07 17:34 UTC, rlk 		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 rlk 2014-10-07 17:17:04 UTC 
Created attachment 107511 [details]
Spreadsheet demonstrating poor performance entering text with conditional formatting applied

Entering data into a spreadsheet with conditional formatting (or at least the region with conditional formatting rules in effect) is much slower than when conditional formatting is not in effect.

With the attached spreadsheet rowing-tmp-o-6.ods, it takes about 2 seconds to enter data in to cell o1862 (try entering 10000 into A1862 and 0:40:00 into B1862).  In contrast, if conditional formatting is not applied to the first sheet, data entry is almost instantaneous (just remove all conditional formatting from the document).

Note that other sheets have the same format applied programmatically, via STYLE().  That does not slow down data entry.

This issue has been present since at least the 3.x series; I don't know just how far back it goes.  I've specifically retested it with both 4.3.2.2 and 4.1.6.2 (which is the version I'm using due to another unrelated bug I've filed).
Comment 1 rlk 2014-10-07 17:20:03 UTC 
Created attachment 107512 [details]
Simpler spreadsheet not demonstrating the problem

Note that a simpler spreadsheet with the same kind of conditional formatting applied does not demonstrate the problem.
Comment 2 rlk 2014-10-07 17:34:45 UTC 
Created attachment 107515 [details]
Simpler spreadsheet demonstrating problem to a lesser degree

This is a simpler version of the spreadsheet (more pages and columns removed) that still demonstrates the problem, albeit to a lesser degree.

(The actual spreadsheet is considerably more complicated than attachment 107511 [details].)
Comment 3 rlk 2014-10-10 13:20:49 UTC 
This happens even when entering data into a cell not covered by the conditional formatting.  For example, enter 5000 into Config.D71 (or even Config.C90, which has no connections to anything else).
Comment 4 rlk 2014-10-16 19:45:32 UTC 
More information.

For some reason, the conditional formatting on that spreadsheet got created as a lot of ranges rather than one or a few.  I deleted all of the ranges because I wanted better performance for some structural changes I was making.  When I added it back in, I did so differently (selected the rectangle containing the area I wanted to format, then control-clicked out rows and columns I didn't want conditional formatted).  This time it created a single range.  The performance of entering data is markedly better now (I think it's still a bit slower than with no conditional formatting, but not a lot).

It's suggesting that whenever data is entered anywhere in the spreadsheet that something does an expensive search of all conditional formatting ranges, or something like that?
Comment 5 A (Andy) 2014-10-25 08:54:59 UTC 
Reproducible with LO 4.3.2.2 (Win 8.1)
It also takes quite long to load the files.

@Markus: What do you think about this?
Comment 6 rlk 2015-04-14 23:23:57 UTC 
I've noticed a number of things that fragment a conditional formatting range.  Inserting rows results in a new range being created for each row that's added (so if I insert 100 new rows into the spreadsheet, I get 100 new ranges, so everything is very slow until I go into formatting/conditional formatting/manage and clean it up).

Also, cutting and pasting something into a conditional formatting range results in the range being broken up if normal paste is used.  The way to avoid it is to use paste special and uncheck the format box, so formats aren't copied.  If I do it by mistake, undo does *not* undo the range splitting.  That may be a separate bug; let me know if you'd like me to file it.
Comment 7 tommy27 2016-04-16 07:25:00 UTC Comment hidden (obsolete)
Comment 8 QA Administrators 2017-05-22 13:26:22 UTC Comment hidden (obsolete)
Comment 9 QA Administrators 2019-12-03 14:23:05 UTC Comment hidden (obsolete)
Comment 10 m_a_riosv 2019-12-06 01:13:48 UTC 
Still
Version: 6.5.0.0.alpha0+ (x64)
Build ID: 60e8941fd581bb06cbf6be62edb8c387e7c07812
CPU threads: 4; OS: Windows 10.0 Build 19035; UI render: default; VCL: win; 
Locale: es-ES (es_ES); UI-Language: en-US
Calc: CL
Comment 11 QA Administrators 2021-12-06 04:06:21 UTC Comment hidden (obsolete)
Comment 12 m_a_riosv 2021-12-06 18:29:16 UTC 
Still
Version: 7.2.4.1 (x64) / LibreOffice Community
Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9
CPU threads: 4; OS: Windows 10.0 Build 21390; UI render: Skia/Raster; VCL: win
Locale: es-ES (es_ES); UI: en-US Calc: CL
Comment 13 Michael Meeks 2023-07-06 09:00:57 UTC 
Can you check if this is still a problem in LibreOffice 7.5 - its not incredibly snappy but it yields a sub-second delay for me even with the first problem today; thanks !
Comment 14 m_a_riosv 2023-07-06 09:50:19 UTC 
I think not, although seems it is with sample file, but replacing all formulas with their values, Menu/Data/Calculate/Formula to value (sheet by sheet, it doesn't work selecting all sheets, https://bugs.documentfoundation.org/show_bug.cgi?id=116161), the issue disappears for me.
Maybe it is inherent from the need to have 'Autocalculate' active, to have Conditional Format working.
And in relation with depending cells of the entered cell, what changes how many cells are forced to recalculate.
Comment 15 QA Administrators 2024-01-03 03:11:58 UTC Comment hidden (obsolete)
Comment 16 QA Administrators 2024-02-03 03:15:51 UTC 
Dear rlk,

Please read this message in its entirety before proceeding.

Your bug report is being closed as INSUFFICIENTDATA due to inactivity and
a lack of information which is needed in order to accurately
reproduce and confirm the problem. We encourage you to retest
your bug against the latest release. If the issue is still
present in the latest stable release, we need the following
information (please ignore any that you've already provided):

a) Provide details of your system including your operating
   system and the latest version of LibreOffice that you have
   confirmed the bug to be present

b) Provide easy to reproduce steps – the simpler the better

c) Provide any test case(s) which will help us confirm the problem

d) Provide screenshots of the problem if you think it might help

e) Read all comments and provide any requested information

Once all of this is done, please set the bug back to UNCONFIRMED
and we will attempt to reproduce the issue. Please do not:

a) respond via email 

b) update the version field in the bug or any of the other details
   on the top section of our bug tracker

Warm Regards,
QA Team

MassPing-NeedInfo-FollowUp