Calc incredibly slow to load and save large but simple ods

joshisanonymous · May 25, 2019, 1:19am

I have an ods file that I use as a checkbook. It’s only 7 columns and the only caluclation it does is add or subtract each deposit or withdraw from the previous line. It’s large at this point, about 16mb (~2000 lines), but really simple. Rather suddenly, however, its gone from opening in about a minute or two to taking literally 20 minutes to open. Saving also suddenly takes much longer: maybe 10 seconds in the past and now a couple minutes. It’s to the point where the spreadsheet is unusable without planning well ahead of time. I thought maybe this was a version issue, but I’ve updated again to 6.2 and have the same problem. I even uninstalled and reinstalled LibreOffice and it’s still just as slow. This all seems a bit odd because I’ve been able to work with 100,000 line x ~20 column files in the past without this much difficulty.

Is there something I can do to make this manageable again? Maybe there’s something about my spreadsheet that I can check that might have gotten corrupted or something? Is it possible just a problem with LibreOffice?

Cookievore · May 25, 2019, 5:34am

Hello, 16 MB is incredibly large for a file containing 7 columns by 2000 lines. I’ve just generated something similar (likely since you did not mention anything specific about your content) and it has only 81 KB (!). Even when I make my spreadsheet of 7 col x 2000 lines more complex, e.g. timestamps, lots of formulas, strings generated by random, numbers with lots of digits aso, it does result in a file of only 154 KB. Both files open within 3 seconds without LO preloaded (on Windows 10, LO 6.2.3.2 x64).

newbie-02 · May 30, 2019, 9:43am

pls. check if you are using comments anywhere in the sheet (sometimes named annotations, text strings you can add to a cell and which is normally hidden but shows up either on mouseover or when you set ‘show comments’). afaik they are a source of different performance problems since years.

up from 100 of them you may get ‘feelable’ sluggishness, up from 2000 heavy impact up to crashes or ‘not responding’ - relies on hardware, OS, LO ver. …

you can easily check how many of them you have in the sheet by opening ‘navigator’ - F5 - and unfold ‘comments’, you get a list.

joshisanonymous · May 31, 2019, 6:07pm

@Cookievore I thought this was large, too. The only formula is one column that just takes the value of the cell above, adds the value of the cell 2 to the left, and subtracts the value of the cell 1 to the left. I copied the formula into every cell in the column by selecting the column, so it goes on forever, but I’ve had it setup that way since 2013 and never had a problem.
@newbie-02 I checked and I do not have any comments on the sheet at all.

newbie-02 · May 31, 2019, 9:08pm

@joshisanonymous: you are working in i similar way as i, i implemented two ideas,

work from bottom to top, you have the actual situation in sight on top of the sheet and the ‘history’ wandering off to the bottom, just if needed insert some rows on the top and fill in ‘empty’ lines with the formulas,

(beware, when inserting rows inbetween others the formulas directly above the insertion will be fucked)

create an evaluation block on top, summing up columns from below, this way you can easily check against errors,

(additional: just key in the expenses as negative numbers and format negative numbers red …)

i work like this with about 12k rows and ~80 cols, 8 of them being ‘stack calculated’, and with quite some formulas in every row (sharing, provisions, VAT, …), the filesize is about 5 MB, it didn’t produce any problems, what killed the performance was … using comments!

for your special case, look comment on shared formula groups below,

joshisanonymous · May 31, 2019, 7:11pm

@Cookievore’s observation about the size of my ods file made me check what happens when I don’t copy my formula to an entire column but instead limit it to the rows that actually have content in them. This reduced the file size, which had somehow grown even more to 25mb, down to 100kb, and now the file opens quickly, as expected.

I believe this was caused by some change in how LibreOffice Calc treats formulas that have been copied to entire columns via clicking on the column header, because I have an identical laid out spreadsheet with different content that hasn’t been opened in a year (meaning it was last opened with an older version of LibreOffice) and it’s only 40kb. Also, the problem file only started to be a problem when I upgraded to LibreOffice ~6.2.3.

tl;dr Don’t copy formulas to whole columns via clicking on the column headers because Calc can no longer handle this.

newbie-02 · May 31, 2019, 8:35pm

you’re right, there have been changes in the handling of ‘shared formula groups’ shortly, they were ‘broken’ killing autocalculate in rare situations, i don’t know about the quality of the fix …

Cookievore · May 31, 2019, 9:14pm

This looks like you have found a bug which could be reported. Maybe you check if there is something already reported. But probably it works as intended. So you have created a file with 1.048.576 lines although most of them are empty (prefilled with the formulas of course).

newbie-02 · June 1, 2019, 9:07am

@cookievore: no, i don’t create such files, for my work i add as much lines as needed, it’s just i had two major problems in the last time, - calc was somewhat buggy around shared formulae and autocalculate, and has massive performance issues with many comments after autosave … it’s plenty reportet about that …

Cookievore · June 1, 2019, 10:00am

@newbie-02 this comment was not addressed to you but to the OP @joshisanonymous since it is placed related to his answer and not marked with ‘@newbie-02’.
Just to make a guess why this way to insert a formular produces such a large file. You made also a helpful point with your comment-impact-on-performance-topic but not all refers to your findings & comments ,-)

newbie-02 · June 1, 2019, 1:42pm

ok _

schiavinatto · May 31, 2019, 7:26pm

Hi @oshisanonymous, I have a file similar to 10 columns x 2062 rows with a sum per row filled, this is 265 KB.

Suggestion, start new file and copy the old worksheet (only area with data) and paste in the new, if it is some dirt should solve.

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

JrgSampaio · February 12, 2020, 6:08pm

Hello,
run soffice from the terminal and see the possible warning messages. particularly, assure you have JRE installed. also assure you install appmenu-gtk3-module.
I have also appmenu-gtk2-menu installed because its lack causes a delay of 25 seconds to start some application, particularly UNISON.

Jorge

mikekaganski · February 13, 2020, 7:49am

particularly, assure you have JRE installed

What for? Unless JRE is actually needed, it’s not needed - and missing JRE is not something that slows things down, just disables some functionality (please prove me wrong if I’m mistaken).

also assure you install appmenu-gtk3-module

This is very specific to used environment, and might easily not be needed for a specific situation.