Download it now!
Bug 84763 - Poor performance entering data with conditional formatting
Summary: Poor performance entering data with conditional formatting
Status: NEW
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 treegraph
 
Reported: 2014-10-07 17:17 UTC by rlk
Modified: 2019-12-06 01:13 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

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