Bug 47299 - CALC: Escalating when re-calculating a huge number of crosslinked cell functions (EDITING, FILEOPEN, FILESAVE)
Summary: CALC: Escalating when re-calculating a huge number of crosslinked cell functi...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 release
Hardware: x86 (IA32) Windows (All)
: high critical
Assignee: Markus Mohrhard
URL:
Whiteboard: target:3.6.0 target:3.5.3
Keywords: perf, regression
Depends on:
Blocks:
 
Reported: 2012-03-14 00:50 UTC by Peter Schmitt
Modified: 2015-12-15 11:31 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Zip archive with 4 files for the decribed testing scenario (1.25 MB, application/zip)
2012-03-14 00:50 UTC, Peter Schmitt
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Peter Schmitt 2012-03-14 00:50:45 UTC
Created attachment 58421 [details]
Zip archive with 4 files for the decribed testing scenario

In the case a CALC document consists of a huge number of cells which contain sheetwise cross-linked cell functions, LibO's runtime seems to escalate and leads to extremely slow performance and/or freezing/blocking of the GUI for many seconds or minutes.
The effect concerns LOADING DOC, RECALCULATING on cell change event and SAVING DOC.
It looks in such a way, as if at any time ALL cells are computed instead of recalculating/refreshing only the changed and depending cells.

It also slightly remembers old BUG 40650, but directly 43804 and 47106. 

The given example will IMHO reproduce the effects.
Test conditions of the following benchmark data:
OS:  Windows Xp Pro SP3
CPU: Intel Core i5-2410M 2.30 GHz
RAM: 3 GB
COMPARED APPLICATIONS: OpenOffice 3.3.0, Excel97 and LibO.

Effects reproduced with LibO 3.4.3, 3.4.5, 3.5.0RC3, LOdev3.5.2RC-nightlyBuild20120312 on WindowsXpSP3 
AND LibO 3.5.0RC3 on Windows7Pro-64bit

Test:
Step 1: download zip and extract files
Step 2: try File 1 with OpenOffice
Step 3: extract File 2, and try xls with Excel97 or OpenOffice
Step 4: open File 1 with LibO
Step 5: change to sheet "Ansicht" and edit cell Y9 with Value 50 as displayed in screenshots Files 3 and 4

Benchmarks:
LibO 3.5.0/ods
OPEN        376 s = 6 min 16 s
CALCULATE    30 s
SAVE        257 s = 4 min 17 s
(OPEN with 355 s remaining at progress bar message "Adapt row height"/"Zeilenhöhe anpassen")

Excel97/xls: 
OPEN         5 s
CALCULATE   <1 s
SAVE         3 s

OOo 3.3.0/ods
OPEN        37 s
CALCULATE    5 s
SAVE        17 s
(OPEN with 25 s remaining at progress bar message "Adapt row height"/"Zeilenhöhe anpassen")

Attachment: test-escalation-at-cross-linked-cell-functions.zip
contains
File 1: linked_tabs-many_formulas.ods 
A simple stock management document, migrated from xls to ods, using many cross-linked table formulas e.g.
english:
a) =IF($A9="";"";VLOOKUP(CA9;$Plan.BI$1:BL$1001;COLUMN(D6);0))
b)=IF(SUMIF($CQ9:$CU9;AA$2;$CL9:$CP9)=0;0;SUMIF($CQ9:$CU9;AA$2;$CL9:$CP9))
c) =IF(A9="";"";IF(SUM(CW9-$Plan.$J$2)<-1000;" no Arr !!";CW9-$Plan.$J$2))
german:
a) =WENN($A9="";"";SVERWEIS(CA9;$Plan.BI$1:BL$1001;SPALTE(D6);0))
b) =WENN(SUMMEWENN($CQ9:$CU9;AA$2;$CL9:$CP9)=0;0;SUMMEWENN($CQ9:$CU9;AA$2;$CL9:$CP9))
c) =WENN(A9="";"";WENN(SUMME(CW9-$Plan.$J$2)<-1000;" no Arr !!";CW9-$Plan.$J$2))

File 2: linked_tabs-many_formulas-export.xls
is a conversion from ods to xls

File 3: before_edit_cell_Y9.jpg
screenshot "before" doing the test

File 4: after_edit_cell_Y9_to_value_50.jpg
screenshot "after" having done the test
Comment 1 s-joyemusequna 2012-03-14 05:35:39 UTC
Confirmed with LibO 3.4.5 and LibO 3.5.1 RC2 on Windows XP / Vista 64.

LibO 3.3.4 and OOo 3.1 : no problems.

This is a regression.
Comment 2 Stephan Raab 2012-03-14 11:23:49 UTC
Also confirmed with LibreOffice 3.4.3 and 3.5.0 on MacOS X 10.7 (Lion).
Comment 3 Oliver Brinzing 2012-03-15 23:53:25 UTC
.
Comment 4 Not Assigned 2012-03-22 18:14:19 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=bbe088bc757fa120f50e4e104bdcd46c90f1feb1

cache calls to mdds:mixed_type_matrix::size, related fdo#47299
Comment 5 Markus Mohrhard 2012-03-22 20:09:08 UTC
This seems to be only one part of the problem.

Need to run a new callgrind run with a release build after the patch.
Comment 6 Markus Mohrhard 2012-03-23 09:53:43 UTC
A non debug build shows that we are back to 3-3 performance with this patch.
Comment 7 Not Assigned 2012-03-23 19:49:45 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-3-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9e00196c1a59eef1889f561e2c9891fdc280a333&g=libreoffice-3-5

cache calls to mdds:mixed_type_matrix::size, related fdo#47299


It will be available in LibreOffice 3.5.3.
Comment 8 Robinson Tryon (qubit) 2015-12-15 11:31:32 UTC
Migrating Whiteboard tags to Keywords: (perf)
[NinjaEdit]