Download it now!
Bug 115488 - FILESAVE: FILEOPEN: Abnormally long open & save times, thought to arise from conditional formatting in spreadsheet
Summary: FILESAVE: FILEOPEN: Abnormally long open & save times, thought to arise from ...
Status: RESOLVED DUPLICATE of bug 81765
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: haveBacktrace, perf
: 116844 (view as bug list)
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2018-02-06 14:09 UTC by tagishsimon
Modified: 2019-04-23 21:05 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
File with conditional format on column B (10.75 MB, application/vnd.oasis.opendocument.spreadsheet)
2018-02-06 14:10 UTC, tagishsimon
Details
file with no conditional formatting (9.09 MB, application/vnd.oasis.opendocument.spreadsheet)
2018-02-06 14:11 UTC, tagishsimon
Details
Callgrind output from master (fileopen) (6.07 MB, application/x-xz)
2018-04-06 21:27 UTC, Buovjaga
Details

Note You need to log in before you can comment on or make changes to this bug.
Description tagishsimon 2018-02-06 14:09:06 UTC
Description:
File open and file save takes ~15 minutes or longer for a ~10Meg ~60k row spreadsheet ... seems to arise from a B2<>B1 type conditional formatting applied to a single column.

Similar file with no conditional formatting takes ~6 seconds to open or close.

(typically, as I'm submitting this report, it's taking my example files ~20m to load, and only about 3m to save. In use over the last few days, autosave, in particular, has been taking 15m+ ... )

Applying a B2!=B1 conditional format to a column of 60K depth also takes an age. 

A stark contrast can be seen if one applies the formula =IF(B2<>B1,1,"") to column A, as compared with B2<>B1 as conditional formatting, both for the 60k rows.

I come away with an interim conclusion that there's something about conditional formatting in LO that leads to excessive time to apply, and to open/save. 


Steps to Reproduce:
1. Open file (example: B4.ods)
2. Amend a cell
3. Save file

1. Open file (example B5.ods)
2. Insert a conditional format to column B, in the form B2!=B1
3. Save file

Actual Results:  
1. File open: takes 15 minutes +

3. File save: takes 15 minutes +


Expected Results:
I'm hoping for significantly faster apply / open / save times.


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 6.0.0.3
Build ID: 64a0f66915f38c6217de274f0aa8e15618924765
CPU threads: 8; OS: Mac OS X 10.13.3; UI render: default; 
Locale: en-GB (en_GB.UTF-8); Calc: group


User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:58.0) Gecko/20100101 Firefox/58.0
Comment 1 tagishsimon 2018-02-06 14:10:53 UTC
Created attachment 139632 [details]
File with conditional format on column B

Example of a problematic file
Comment 2 tagishsimon 2018-02-06 14:11:40 UTC
Created attachment 139633 [details]
file with no conditional formatting
Comment 3 Telesto 2018-02-06 19:07:33 UTC
Repro with B4.ods
Version: 6.1.0.0.alpha0+
Build ID: 8a0b61172a14b8b766a2e85f27762db3558d3af7
CPU threads: 4; OS: Windows 6.3; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-02-06_03:28:54
Locale: nl-NL (nl_NL); Calc: CL

and with
LibO 5.4.0.4

The file is pretty crash prior to 5.4.04
Comment 4 Alex Thurgood 2018-02-13 10:16:33 UTC
I could confirm this with B4.ods just on loading the file. So far, the loading process appears to be suspended and it has been going for more than 20 min.

MacOS system reports that the LO app is not responding.

So the document finally loaded after 22 minutes.

Made a single letter change in one cell with a text string. Saved.
Saving took 4 min.


Version: 6.0.0.3
Build ID: 64a0f66915f38c6217de274f0aa8e15618924765
Threads CPU : 4; OS : Mac OS X 10.13.3; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group
Comment 5 Alex Thurgood 2018-02-13 10:41:28 UTC
Also tested against

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4

File opens after ca. 20 mins.
Comment 6 Aron Budea 2018-03-12 02:17:38 UTC
This issue is not specific to macOS.

Two problem areas are these functions:
https://opengrok.libreoffice.org/xref/core/sc/source/core/data/conditio.cxx#2270
sal_uInt32 ScConditionalFormatList::getMaxKey() const

https://opengrok.libreoffice.org/xref/core/sc/source/core/data/conditio.cxx#2046
ScConditionalFormat* ScConditionalFormatList::GetFormat( sal_uInt32 nKey )

They are both linear searches over m_ConditionalFormats, so with many conditional format entries that can take a while.

What could be done? The comparison is done against the nKey coming from the individual entries in m_ConditionalFormats (currently a set of pointers to ScConditionalFormat objects).
One problem is that those nKey-s are set outside in the code, first by querying an available one from the ScConditionalFormatList (that's what getMaxKey() is for), setting it in the ScConditionalFormat, then adding it to the list, ie. in 'sal_uLong ScTable::AddCondFormat(...)', 'void ScTable::CopyConditionalFormat(...)' and 'ScCondFormatDlg::OkPressed()'.

If managing nKey was internalized in ScConditionalFormatList, then it could keep track of the max (making it a constant time operation), and keep the conditional formats in a data structure sorted by nKey (making searching on nKey logarithmic).

I'm not familiar with the code, so I can't tell if anything else could prevent this change.
Comment 7 Alex Thurgood 2018-04-06 11:06:55 UTC
*** Bug 116844 has been marked as a duplicate of this bug. ***
Comment 8 Alex Thurgood 2018-04-06 11:08:14 UTC
FWIW, MS Excel 2016 (11.11.1) on macOS 10.13.4 opens the ODS file in less than 2 minutes !

Time to switch to Excel to open ODS files ?
Comment 9 Buovjaga 2018-04-06 21:27:11 UTC
Created attachment 141178 [details]
Callgrind output from master (fileopen)

Arch Linux 64-bit
Version: 6.1.0.0.alpha0+
Build ID: 8ed2fb306ffa8c7fef336b858fc5074c309c3c9f
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on April 6th 2018
Comment 10 QA Administrators 2019-04-07 03:44:09 UTC Comment hidden (obsolete)
Comment 11 Xisco Faulí 2019-04-22 13:30:43 UTC Comment hidden (obsolete)
Comment 12 Buovjaga 2019-04-22 13:35:35 UTC
(In reply to Xisco Faulí from comment #11)
> it takes
> 
> real	1m11,283s
> user	1m13,856s
> sys	0m6,017s
> 
> to open in
> 
> Version: 6.3.0.0.alpha0+
> Build ID: e913727c7ee3af0bb4031c6829abfb3373306492
> CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
> Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US
> Calc: threaded

People were getting 20 min opening times, so it is obvious the fixes for bug 81765 had an effect. Maybe we will leave it open for now. I could do a new trace later.
Comment 13 Xisco Faulí 2019-04-22 13:43:19 UTC
(In reply to Buovjaga from comment #12)
> (In reply to Xisco Faulí from comment #11)
> > it takes
> > 
> > real	1m11,283s
> > user	1m13,856s
> > sys	0m6,017s
> > 
> > to open in
> > 
> > Version: 6.3.0.0.alpha0+
> > Build ID: e913727c7ee3af0bb4031c6829abfb3373306492
> > CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
> > Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US
> > Calc: threaded
> 
> People were getting 20 min opening times, so it is obvious the fixes for bug
> 81765 had an effect. Maybe we will leave it open for now. I could do a new
> trace later.

While you wrote the comment I was checking in LibreOffice 6.2. I had to kill it...

real	9m43,806s
user	0m0,021s
sys	0m0,007s

So definitely bug 81765 is related...
I would wait for https://gerrit.libreoffice.org/#/c/71043/ before getting a new trace
Comment 14 Xisco Faulí 2019-04-23 21:05:44 UTC
I've just checked again in


Version: 6.3.0.0.alpha0+
Build ID: 0a04150b6eefb5feb7ecefaa5cd63dbac8c1574f
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US
Calc: threaded

and it's even better ( probably because of https://cgit.freedesktop.org/libreoffice/core/commit/?id=1e3d0a57689006cd7244481958025177c01e4d09 )

real	0m48,060s
user	0m50,231s
sys	0m3,808s

so let's close this one as dupe of bug 81765.
it will improve even more after  https://gerrit.libreoffice.org/#/c/71043/ lands in master

*** This bug has been marked as a duplicate of bug 81765 ***