We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
1

Any ideas, please, as to why my LO Calc document recently began to spend 30-30 seconds recalculating and so take 40-50 seconds to load instead of 10 seconds?

asked 2020-09-15 21:01:26 +0200

Hubert Taylor gravatar image

About 5 seconnds afeter begining to load the document, the LO Calc status line message sayling "calculating..." and the calculating takes approx 30-40 seconds.

My LO Calc document comprises 6 sheets and file size on disk is approx 11.9 MB.

The document's File > Properties > Statistics states the document contains 795,197 Cells and 2,431 Formula groups

edit retag flag offensive close merge delete

Comments

any recent changes in System, OS, LO version or the document? changed options as e.g. 'recalc on load', deinstalled or added fonts, comments, exotic formatting, 'skia', charts, some formulas and recursions are other possible performance killers, doc with 7MB, 800k cells, 13k formula groups and some charts loads here within 15 sec ... further analysis requires more details or access to the file ...

newbie-02 gravatar imagenewbie-02 ( 2020-09-15 21:21:50 +0200 )edit

Thank you, Newbie-02, for the reply. No "changes" to System, OS, or LO version. I will try to find option dealing with possible. No changes to "fonts" No "comments, exotic formatting, 'skia', charts" other than possible accident I have no recursive formulas. Presently I am not sure how to detect recursive formulas but I will search the help doc. Thank you for the reassuring point re your "doc with 7MB, 800k cells, 13k formula groups and some charts " that loads in 15 sec.

Hubert Taylor gravatar imageHubert Taylor ( 2020-09-15 23:06:06 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-09-15 22:07:56 +0200

igorlius gravatar image

updated 2020-09-15 22:08:46 +0200

Hi,

for debugging (performance and other) issues, it's good to first check if starting in "Safe Mode" improve the situation. If it does, then there might be problems with your profile / settings. (Maybe becaus of an update or something else)

That can most likely be fixed, by resetting your LO setting or restoring an older profile backup.

If ithe situation does not improve by using "Safe Mode", it could be because of the document itself.

Here are some things to check?
- Are you using odt/s or a foreign format like xls/x? - Does your document contain macros?
- Complex Forumlas?

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

Have a nice day and let's (continue to) "Be excellent to each other!"

Ask / Getting Started:
https://wiki.documentfoundation.org/A...

edit flag offensive delete link more

Comments

Thank you for your reply. No recent/linked LO update recent but I will bear in mind possibility of a "profile" issue. My document is wholly LO based, with no macros or overly complex formulas. Instead of complex formulas I have used multiple simple formulas. Functions used include: match(), index(), if(), ifs(), sumif(), sum(), not(), or(), and(), sign(), iserror(), isna(), iferror(), isnumber(), istext(), na(), two data-validity each with one indirect(), I also use indirect() with address() to sort scattered numbers into continuous line. I use five date() formulas, and sheet() is used in six formulas. I have used a lot of named-cells but no named-areas/ranges. I have not used lookup(), random(), offset(). Presently, no conditional-formatting,

Hubert Taylor gravatar imageHubert Taylor ( 2020-09-15 23:33:39 +0200 )edit

Does not need to be an LO update windows and other software also sometimes do "wierd" sh*t. But ok, lets assum e no changes through 3rd party apps or the os.

From your desc. it does not sound like you are using "overly" compley functions.

But did you already try restarting in "Safe Mode"? If so, does the behaviour change?

igorlius gravatar imageigorlius ( 2020-09-15 23:46:07 +0200 )edit

No, I have not tried "Safe Mode". I will try safe-mode now. Thank you.

After restarting LO in Safe-Mode no change re slow/calculating loading. I also saved the file from Safe-Mode and then re-loaded it but still slow - 40-50 seconds to load.

Hubert Taylor gravatar imageHubert Taylor ( 2020-09-15 23:50:26 +0200 )edit

And other programms on the system do not have any slowdown issues?

igorlius gravatar imageigorlius ( 2020-09-16 00:05:32 +0200 )edit

I thank 'igorlius' (and All) for continuing to try to assist me.

Other often used programs on my laptop - as Adobe Acrobat, Chrome browser, email reader, and gimp2 -appear to be running at normal speed.

Does anyone know, please, what factors/conditions within my 11.9 MB LO-Calc file would prompt LO-Calc to re-calculate its own ODS-format file while loading it?

My LO-Calc program is set to 'Autocalculate' and I always save the file after editing. If we know what causes LO-Calc to calculate while loading, I can serch for those factors/conditions witin the 6 sheets of my document.

Hubert Taylor gravatar imageHubert Taylor ( 2020-09-16 12:01:56 +0200 )edit

And the loading speed is faster, when you uncheck Autocalculate,
save the Dokument, and reopen it?

image description

Just to make sure, that the calculations are really the culpit here.

igorlius gravatar imageigorlius ( 2020-09-16 12:37:49 +0200 )edit

Thank you, Igorlius.

I loaded the file, disabled 'Autocalculate', then saved.

When reloaded, the 'manual-calculate' file spent 25 seconds in the calculating phase while loading.

That is same amount of time spent calculating while loading the 'Autocalculate' file - so no difference.

What is calculating while loading, please? With this knowledge, I could then check my sheets.

Hubert Taylor gravatar imageHubert Taylor ( 2020-09-16 13:05:30 +0200 )edit

I am not sure if any "calculation" are done at document load. When i look at the description of Autocalculate (ref: https://help.libreoffice.org/latest/e...) it states, that ony when cells are modified, it should trigger. Not sure what other "calculation" or stuff could be going on, sorry.

My last 2 ideas:

  1. Use the portable version of LO. https://www.libreoffice.org/download/... To exclude your installation as the source of the problem.

  2. validate your ods file here: https://odfvalidator.org/ , to check if something is not right.

Thats all i have at the moment. Report back with your results

And if nothing helps you might have to share a sample of your file, so we try to reproduce your problem.

igorlius gravatar imageigorlius ( 2020-09-16 13:21:06 +0200 )edit

Thank you, Igorlius, I will tryout the odfvalidator.org facility.

Before delving into other LO versions, a prior step ought be to know what forces LO-Calc into "recalculating" while loading a file.

I will report-back my findings.

REPORT-BACK

Introductory Point Apart from the slow-loading issue, once loaded to document (for financial bookkeeping) works fine in terms of its calculations and there's no lag/latency when entering raw data

TDF Validator Initially my 11+MB file too large for the TDF validator so I deleted my columns of formulas to just two rows resulting in a 1.5 MB file. When I tried again to validate, the TDF validator timed-out with a "504 Gateway" issue.so the TDF validation process could not assist us.

Sharing Sample File I am willing to "share a sample" file with sample data. The data entry (sheets Inc_data and Exp_data) and profit and loss and ...(more)

Hubert Taylor gravatar imageHubert Taylor ( 2020-09-16 13:45:23 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-15 21:01:26 +0200

Seen: 238 times

Last updated: Sep 15 '20