Created attachment 124979 [details] MTR fares.ods LibreOffice Calc is too slow for doing spreadsheet operations. For example, even a simple operation, like deleting sheets, takes more than 10 minutes of 100% CPU usage, unresponsive in the process. For example, the attached 'MTR fares.ods', which contains the MTR fare tables from 2007 onwards, makes LibreOffice freeze consistently. Simple operations like moving sheets, deleting sheets, or inserting sheets in the middle, or even undoing a simple operation, freezes the app for a long time. Even working with smaller files exhibits the same problem. When I open the attached 'integrated fares before merger.ods', which contains formulae calculating with values from 'ER.ods', 'WR.ods' and 'MTR.ods', LibreOffice becomes very slow to the extent that scrolling becomes not smooth. To make the matter worse, even calculating the formulae take 100% CPU long time. The attached 'master.ods' contains indirect references where the user can type in any sheet name from any files in order to obtain data (as I have tens of different fare tables to work with, I put them in different files in order to speed up loading time). With AutoCalculate on, the app is basically unusable. When I turn it off and type my desired sheet names, for example, with the attached '2015.ods', I type in '2015.ods'#'adult octopus' into the cell labelled from and '2015.ods'#'student octopus' into the cell labelled to, and press F9, the app freezes with 100% CPU usage. This is totally unacceptable.
Created attachment 124980 [details] ER.ods
Created attachment 124981 [details] WR.ods
Created attachment 124982 [details] MTR.ods
Created attachment 124984 [details] 2015.ods
Created attachment 124985 [details] master.ods
Created attachment 124986 [details] integrated fares before merger.ods
4.3 branch is EOL, please give a try with a recent LO version (last stable one is 5.1.2).
Using whole sheet ranges to search in MATCH(), doesn't look the best way for a better performance. A lot of INDIRECT(), a volatile function.
The calculation of master.ods did complete last night (I left my server and LibreOffice on and hit F9), but it takes more than 4 hours of CPU time. I am running it on a stable Debian server, which comes with 4.3.3.2. Although I have another development machine which I can freely install any version of LibO I want, that machine is very slow to the extent that a macro which takes about 20 minutes to complete on my server needs about 2 hours on my development machine. (The CPU of that machine is totally crap, even simply opening Google Chrome can make it not responding so I am not willing to open the above documents on that machine) I am now trying to optimise my master.ods to reduce the number and range of my formulae and see how much CPU time it takes on the server to recalculate.
Even if we'd know exactly what's the problem on 4.3.3 version, what do you expect? Indeed, a patch would be applied on a new version of "not EOL" branches, ie 5.1.X and future 5.2.0 The only way would be you or some company you'd pay, to build LO 4.3.3 and patch it.
Created attachment 124997 [details] master.ods I have reduced the range and number of formulae in the file, but it still takes nearly 2 hours of CPU time to do a recalculate on my server.
I can't help here, so uncc myself and put it back to UNCONFIRMED.
Michael: please test with 5.1.3. https://wiki.documentfoundation.org/Installing_in_parallel/Linux Set to NEEDINFO. Change back to UNCONFIRMED, if the problem persists in 5.1.3. Change to RESOLVED WORKSFORME, if the problem went away.
Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INSUFFICIENTDATA due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-NeedInfo-Ping-20161207
Dear Bug Submitter, Please read this message in its entirety before proceeding. Your bug report is being closed as INSUFFICIENTDATA due to inactivity and a lack of information which is needed in order to accurately reproduce and confirm the problem. We encourage you to retest your bug against the latest release. If the issue is still present in the latest stable release, we need the following information (please ignore any that you've already provided): a) Provide details of your system including your operating system and the latest version of LibreOffice that you have confirmed the bug to be present b) Provide easy to reproduce steps – the simpler the better c) Provide any test case(s) which will help us confirm the problem d) Provide screenshots of the problem if you think it might help e) Read all comments and provide any requested information Once all of this is done, please set the bug back to UNCONFIRMED and we will attempt to reproduce the issue. Please do not: a) respond via email b) update the version field in the bug or any of the other details on the top section of our bug tracker Warm Regards, QA Team MassPing-NeedInfo-20170131