Situation: When working with very large spreadsheet documents (in my case spanning 75 sheets with 30-2000 rows an 5 to 60 columns each, about half of which with formulae), LibreOffice is stable but very slow. Data entry is nearly impossible due to long wait times for recalculation after each cell edit. Suggested enhancements: a) Implement switch to manually enable/disable recalculation globally. b) Switch should be UI-centric, not document-centric. Default is "on" (enable auto-recalc), and can be configured to be "off". c) Switch should be accessible using the menu an symbol bars so that it is easily usable. d) Switch should be in "alerting" design when deactivated to indicate the possible inconsistencies resulting in the document. e) Status bar message "INCONSISTENT DATA-PLEASE RECALCULATE" (in "alerting" design) when auto-calculation is switched off and a cell has been edited. f) Recalculation can be started any time by pressing a second button in the symbol bar or by pressing a predefined key. Expected use: Switching off automatic recalculation allows for efficient data entry and data editing operations. Manually recalculating re-establishes consistency within the document. Status messages and stark button colors indicate possibly inconsistent content. References: - Bug #47106 - Function "AutoCalculate" which can be disabled but does not seem to have an effect. - http://en.libreofficeforum.org/node/1061
Re: Function "AutoCalculate" which can be disabled but does not seem to have an effect: Switching off AutoCalculate reduces the delay somewhat, but by less than half, which does not really matter from a practical viewpoint.
Does this issue still persist with the latest release of LO? There have been a lot of speed improvements in the last releases of LO.
I am currently unable to test due to a lack of an installation. The newest libreoffice available to me is 3.6.6.2 as contained in Ubuntu 12.10 and it still has the same speed issues. As the PPA does not contain LO 4 my answer will need to wait for the availability of Ubuntu 13.04 where I suppose it will be included.
I have installed LibreOffice4 into an Ubuntu 12.10 following the instructions on the libreoffice.org site. Opening the large document (now 6M in size) using its name as a command-line parameter, it started with a much faster "loading", but LO crashed about half-way in "recalculating". Up to that point, it was faster, but esimating on the half-way calculation, recalculation of the whole sheet will take about 60 to 70s, compared to several minutes in 3.6.6. This WOULD indeed be much faster. However, even that improvement is not enough for me to consider the issue resolved, and I would still state that the original suggestion would be an improvement. I have tried two more times. LO consistently crashed after about 60% of recalculation (using a visual estimate of the progress bar indication).
I need to correct my previous statement. The delay on loading the document in LO 3.6 happens _after_ recalculation and _after_ adjusting line heights. There is no progress bar for the actions going on inside LO at that time. Neither is there when I enter a new value into one of the cells. As stated previously, I cannot test on LO 4 (the version I installed is LO 4.0.0.3) as it crashes during recalculation. Therefore I cannot estimate the the speed improvement.
(In reply to comment #5) > I need to correct my previous statement. > The delay on loading the document in LO 3.6 happens _after_ recalculation > and _after_ adjusting line heights. There is no progress bar for the actions > going on inside LO at that time. Neither is there when I enter a new value > into one of the cells. > As stated previously, I cannot test on LO 4 (the version I installed is LO > 4.0.0.3) as it crashes during recalculation. Therefore I cannot estimate the > the speed improvement. Does this still happen with 4.0.4 or 4.1.0.beta1/beta2? If so could you please open a bug report with a test case? I sounds strange that despite auto calc being turned off we still spend a considerable time recalculating. The only idea that I have is that somehow your document contains a few shapes that force row height calculations. But that would still means that in ScFormulaCell::MaybeInterpret we would not call the interpreter so there must be something strange going on. Maybe you can open a bug report with the test file and put perf in the whiteboard line.
> Does this still happen with 4.0.4 or 4.1.0.beta1/beta2? I still do not know. I am using LO 3.7.5.2 on Xubuntu 13.04 right now. > If so could you please open a bug report with a test case? That would probably mean to create a spreadsheet file with 100 sheets, each of which with several hundred lines and several dozen columns. I refrain from that. Or posting my original file - which I cannot do due to its content. > I sounds strange that despite auto calc being turned off we still spend a > considerable time recalculating. The only idea that I have is that somehow > your document contains a few shapes that force row height calculations. > But that would still means that in ScFormulaCell::MaybeInterpret we would > not call the interpreter so there must be something strange going on. I mentioned it earlier in one of the other bugs I filed at the same time (#53825, #53826, #53627, #53628, all of them probably not depending on this bug), but I would like to point it out here again: My sheet does lots of verification using conditional formats. Almost each of the million individual cells has a conditional format, which is mainly used to set the cell's background color depending on the cell's value. I do this as a visual validation feedback. Many of these validations use lookups (german function names "VERWEIS" or "SVERWEIS") or summation functions (german function names "ZÄHLENWENN", "SUMMEWENN"), and I suspect a heavy impact here. Furthermore, even with recalcuation off, validation seems to work partly (background colors are updated, but inconsistently - I have not investigated further). > Maybe you can open a bug report with the test file and put perf in the > whiteboard line. I am afraid I am not enough into LO debugging. Please let me know if my hint regarding the conditional formats was of any use to you.
@Markus - without a test document is there anything we can do?
Max can't post the document causing the problem and also can't create another test document. Max you could try cleaning your document of the sensitive data in question or replace the sensitive info with nonsense values. As long as we don't have a document to reproduce this problem there's nothing we can do. So setting to WONTFIX. Max if you want to test further: LO 4.1.0.1 is available here http://www.libreoffice.org/download/pre-releases/ (there's also an explanation what ppas to add to Ubuntu / Xubuntu to get the pre-releases on that platform.
WONTFIX implies we have confirmed there's an issue - as far as I know we haven't done so as we don't have a test document. Setting to INVALID because without the test document we can't do anything about it including confirm that there's a problem