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
I can totally agree with this description.
Daniel, is it possible that you can attach an example document with this behavior? Thanks.
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.
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.
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!
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 ;-)
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!
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.
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.
@Eike: if you do not have the time "at the moment" should this ticket still be assigned to you?
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.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
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