| Summary: | References, Adapt row hight and AutoCalculate slow down Calc dramatically due to OFFSET() function | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | daniel.schaaaf |
| Component: | Spreadsheet | Assignee: | Eike Rathke <erack> |
| Status: | ASSIGNED --- | QA Contact: | |
| Severity: | normal | ||
| Priority: | medium | CC: | daniel.dev.libreoffice, daniel.schaaaf, erack, klauspeter.schmitt |
| Version: | 3.5.0 release | ||
| Hardware: | All | ||
| OS: | All | ||
| See Also: | https://bugs.freedesktop.org/show_bug.cgi?id=47299 | ||
| Whiteboard: | |||
| i915 platform: | i915 features: | ||
| Attachments: | Spread sheet with many formulas and data that provoke slow downs | ||
|
Description
daniel.schaaaf
2012-03-08 08:59:17 UTC
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. |
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.