Ask Your Question
0

Calc incredibly slow to load and save large but simple ods

asked 2019-05-25 03:19:16 +0200

joshisanonymous gravatar image

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?

edit retag flag offensive close merge delete

Comments

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).

Cookievore gravatar imageCookievore ( 2019-05-25 07:34:51 +0200 )edit

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.

newbie-02 gravatar imagenewbie-02 ( 2019-05-30 11:43:40 +0200 )edit

@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.

joshisanonymous gravatar imagejoshisanonymous ( 2019-05-31 20:07:48 +0200 )edit

@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 ...(more)

newbie-02 gravatar imagenewbie-02 ( 2019-05-31 23:08:54 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2019-05-31 21:11:54 +0200

joshisanonymous gravatar image

@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.

edit flag offensive delete link more

Comments

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 ...

newbie-02 gravatar imagenewbie-02 ( 2019-05-31 22:35:08 +0200 )edit

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).

Cookievore gravatar imageCookievore ( 2019-05-31 23:14:48 +0200 )edit

@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 ..

newbie-02 gravatar imagenewbie-02 ( 2019-06-01 11:07:51 +0200 )edit

@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 ,-)

Cookievore gravatar imageCookievore ( 2019-06-01 12:00:27 +0200 )edit

ok :-) _

newbie-02 gravatar imagenewbie-02 ( 2019-06-01 15:42:44 +0200 )edit
0

answered 2019-05-31 21:26:58 +0200

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 Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-05-25 03:19:16 +0200

Seen: 41 times

Last updated: May 31