Bug Hunting Session
Bug 47106 - References, Adapt row hight and AutoCalculate slow down Calc dramatically due to OFFSET() function
Summary: References, Adapt row hight and AutoCalculate slow down Calc dramatically due...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2012-03-08 08:59 UTC by daniel.schaaaf
Modified: 2018-10-11 07:35 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Spread sheet with many formulas and data that provoke slow downs (2.30 MB, application/vnd.oasis.opendocument.spreadsheet)
2012-06-29 09:15 UTC, daniel.schaaaf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description daniel.schaaaf 2012-03-08 08:59:17 UTC
In huge spread sheets that contain complicated formulas that reference several other sheets, any change made to any cell will result in Calc becoming unresponsive for several seconds to minutes.

This bug report is not about long loading times of spread sheets, but about working with the spread sheet after loading.

With AutoCalculate enabled, Calc is slowed down dramatically, even when the user does not interact with any element that would require re-calculation. E.g., text input into cell "A1" in sheet "XYZ", followed by conformation with the enter key, will result in waiting times up to 20 seconds or more, even though none of the formulas in the spreadsheet reference anything in sheet XYZ. It looks like Calc re-calculates the whole document whenever a change has been made to it, without checking if any of these calculations are actually necessary!

Deactivating AutoCalculate helps a little, but this is very inconvenient due to Calc not calculating what it should calculate. When a formula is entered into a cell, Calc will calculate and show the result. But when that cell is copied down, the new cells will just copy the output of the original cell instead of calculating the new output. The user has to change every cell manually, e.g. by adding empty space into the formula, to force re-calculation.
After some time it might even happen that all cells that contain formulas become blank until re-calculation is forced with "Ctrl+Shift+F9" or AutoCalculate is re-enabled. This might be an issue with Calc trying to use as little memory as possible (which would also explain why scrolling through a document can be slow).

As a summary:
a) Calc should only re-calculate cells when a referenced cells was changed
b) Calc should make use of available memory in todays computers and thereby speed up handling of large spreadsheets
Comment 1 Peter Schmitt 2012-03-14 00:57:54 UTC
I can totally agree with this description.
Comment 2 Daniel Bankston 2012-06-28 15:16:20 UTC
Daniel, is it possible that you can attach an example document with this behavior?  Thanks.
Comment 3 daniel.schaaaf 2012-06-29 09:15:12 UTC
Created attachment 63612 [details]
Spread sheet with many formulas and data that provoke slow downs

This is a document I work with daily. The formulas do not make sense and the data consists of only one number.
Comment 4 Eike Rathke 2012-06-29 12:07:12 UTC
The recalculations are due to the extensive use of the OFFSET() function, which is volatile because it results in indirect references and is recalculated on every input.

Maybe (!) I can do something about it.
Comment 5 daniel.schaaaf 2012-06-29 12:21:53 UTC
This information should have been in comment 3.

Firs my hardware: Dell E6400 laptop, Intel C2D 2.5 GHz, 4 GB RAM, SATA HD, Win
7 x64


1) Open the document (typical lower size I am working with every day)
=> Document will load for 20 seconds and show "Temp" sheet

2) Click on "Calc" sheet immediately
=> "Adjust row height" for 8 seconds

3) Mark a sufficient number of cells (> 500) in one row and hit delete
=> "Adjust row height" for some time (depending on number of selected cells)

4) Enter text into any cell that is not referenced by any other cell (e.g. in
the "Temp" sheet), followed by a return
=> Calc becomes unresponsive for 2-5 seconds


Comments:

1) Is OK, it's a big document after all

2 & 3) I specifically set every row in the sheet (whole document?!) to a size
different from default. Calc should not calculate row heights at all!

4) Calc seems to re-calculate the document, even though information was entered
in a cell that does not influence the content of any other cell


I hope this will help in finding some things that could be improved to speed up
Calc. There are many other users who would highly appreciate that :-)

And yes, Offset() is probably a resource hog here. Unfortunately I cannot live without it. Also referencing different sheets is a problem ...

Thanks that you are looking into this!
Comment 6 Eike Rathke 2012-07-04 11:03:55 UTC
Making this non-volatile turns out to be a larger project that currently doesn't fit into my time frame / schedule / ... and isn't an easy hack at all. So it steps a bit down on my to-do list. Sorry for disappointing you and please be patient ;-)
Comment 7 daniel.schaaaf 2012-07-05 03:59:34 UTC
Too bad, but I already thought that this would be an ambiguous project and I am not expecting anything to change anytime soon. But great that you are looking into it!
Comment 8 daniel.schaaaf 2012-08-11 23:17:31 UTC
I don't want to push this bug, but I had to notice that formatting causes re-calculation as well. This is something that should not happen at all.
Another thing that is slowing down Calc a lot are charts.

Looking at my memory usage, I see that LibreOffice is quite modest and usually uses less than 1 GB. This is less than what my browser eats up ;-)
In times where 4 GB RAM are standard, I think LibreOffice should make use of what it can get its hands on. I'd rather upgrade to 8 GB and have a blazing fast Calc than being able to open huge Calc sheets on an ancient machine, where I am not able to work with the document due to major slow-downs.
Comment 9 daniel.schaaaf 2012-09-13 09:09:28 UTC
Some more information and benchmarking. I found an old bug report (https://bugs.freedesktop.org/show_bug.cgi?id=47299) that might be related.
To please my boss, I converted my spread sheet (a bigger version of the file attached to this bug report) to MS Excel, and I was surprised by the result:


Calc (3.7alpha0, which is actually faster than 3.5)

Open file: 70 to 80 seconds
Memory per file: 300 to 400 MB
Performance: Document feels extremely slow and introduces waiting times, even for simple tasks like formatting

Excel 2007

Open file: 20 seconds
Memory per file: 50 MB
Performance: Excellent, no slow-downs even with 11 copies of the document open


This is not an "Excel is better than Calc" rant, but it shows that there is something very wrong in Calc.
Memory usage is one obvious problem and it even lead to an instant crash when I tried to load more than 4 copies of my document. I don't mind the memory usage, but a crash is serious.
@Eike
You were right, Offset() is the culprit in my file! Replacing it with cell values instead of the function did not reduce memory usage, but it solved the recalculation problem.
Comment 10 Dennis Roczek 2016-10-23 14:38:40 UTC
@Eike: if you do not have the time "at the moment" should this ticket still be assigned to you?
Comment 11 Xisco Faulí 2017-10-10 08:46:51 UTC
Dear Eike Rathke,
This bug has been in ASSIGNED status for more than 3 months without any activity. Resetting it to NEW.
Please assigned it back to yourself if you're still working on this.
Comment 12 QA Administrators 2018-10-11 03:03:08 UTC Comment hidden (obsolete)
Comment 13 Roman Kuznetsov 2018-10-11 07:35:52 UTC
don't repro in

Version: 6.2.0.0.alpha0+
Build ID: d9ad59da50c1172fe98f94370221c9c1b688200a
CPU threads: 4; OS: Windows 6.1; UI render: GL; VCL: win; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-10-08_23:34:44
Locale: ru-RU (ru_RU); Calc: threaded

entering of any values to any cells doesn't give freeze or "unresponsive for several seconds to minutes."

Status-> WFM