Download it now!
Bug 63745 - FILEOPEN/FILESAVE extremely slow on large sheets
Summary: FILEOPEN/FILESAVE extremely slow on large sheets
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: perf
Depends on:
Blocks:
 
Reported: 2013-04-19 23:42 UTC by Barna
Modified: 2019-11-16 10:19 UTC (History)
13 users (show)

See Also:
Crash report or crash signature:


Attachments
testcase (76 bytes, text/plain)
2013-04-21 07:16 UTC, Barna
Details
Another for test (724.42 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-12-14 17:07 UTC, Rpnpif
Details
file to demonstrate the extreme slowness of this program versus Excel (387.46 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-06-10 05:31 UTC, Kevin
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Barna 2013-04-19 23:42:51 UTC
I experienced a very similar problem like described in bug ID 63640, however with Calc.
I use a huge spreadsheet: about 50 sheets, each around 1000 rows long, with many calculating and searching formulas like VLOOKUP and HLOOKUP, in xls format it takes over 80Mb. I use it for data collection with my collegues, on several computers (of course not simultaneously). It was originally created in MS Excel 97, but after some time I tried to use it in LibreOffice 3.6.5 and every version thereafter. It is opened finely, every cell looking okay, however opening from a local HDD takes at least 7-8 minutes, while saving it in either ods or xls format takes more than 15 minutes (slow progress bar when opening, no progress bar visible during saving)! 
Just to compare, MS Excel 97 opens the same file in about 10-15 seconds, on a quite similarly built hardware and OS.

This saving problem may cause another also reported bug, namely that it seems to freeze the program for similarly long time during editing if autosaving is set on (without any progress bar at the bottom), and this freezing disappears after changing settings. Now I can at least edit the file if I'm patient enough, however opening and regular saving takes same long as before. When trying to use LibreOffice for my regular work with spreadsheets like this, it is a huge limitation, I would call it a major problem!

I use WinXP system.
Comment 1 Markus Mohrhard 2013-04-20 00:22:09 UTC
To do anything about this problem we need a test file showing the issue. Without test files it is impossible to work on performance problems.
Comment 2 Joel Madero 2013-04-20 19:05:53 UTC
Marking as NEEDINFO, get us a test case and set to UNCONFIRMED and we will investigate
Comment 3 Barna 2013-04-21 07:16:44 UTC
Created attachment 78289 [details]
testcase
Comment 4 Thomas van der Meulen 2013-04-22 09:07:28 UTC
Loading on my Macbook Pro to around 3-4 min I don't think this is to long because the file is very big. Wen trying to open with Apple Number it crasht
opening the file toke 1GB of my ram 

saving the file into .xls format toke 3-4 mintis BUT it toke 99,9% of my CPU and !,5 GB of ram! So if some one has a les powerfull CPU or less ram it will take way longer. 

Version: 4.1.0.0.alpha0+
Build ID: fd179c5f308a0a611bc2b5a479e28ff3ced7964 (21 apr)

os: mac osx 10.8.3
system: macbook Pro 
ram: 4GB
cpu: 2.5 GHZ in core I5
Comment 5 Barna 2013-04-23 21:51:15 UTC
Another observation:
I tried to save the test file to an external HDD. During all the so called saving process, the outside HDD only worked in the last 1-2 seconds. All other work is done in the memory before writing any byte to the file.

I'm not a C++ expert so I don't understand much from the source code, however I write programs in Delphi and experienced similar problems when working with automatically refreshing data: huge refresh-loops launched when a new item is added to the database to be refreshed. Probably something similar is happening here, since the sample database has many formulas. If their values are recalculated every time a new cell is added (or changed) during loading, it might cause severe speed problems and huge memory usage exponentially related to the amount of formulas. However it does not have any sense to do a cell-value refreshing before saving.
Maybe it would be worth if somebody who is somewhat experienced in editing LibOffice code to check that out. If that's the problem, I would have some general ideas how to make cell-value refreshing (and thus loading) way faster. However I would need someone knowing C++ language to set it into practice...
Comment 6 Barna 2013-04-29 20:26:50 UTC
A potentially helpful new finding with the same file:

If autosave is switched on, it is faster compared to clicking the save button. However the progress bar behaves weird: it progresses to about 90% quite normally, then it goes back and forth between 90 and 95%, then progresses to between 95 and 98%, going back and forth again in this range, then progressing to 99%, where I see no "going-back-and-forth", however I cannot exclude that, since the progress bar waits for several minutes in this position right at the right border of my screen. It seems as if somewhat would be performed several times at the end of the saving process, slowing it down quite a lot.
Comment 7 Paijo 2013-06-07 11:33:43 UTC
I'm using LO 4.0.2 shipped with Ubuntu 13.04.
I don't have any problem opening the attached file with 107 sheets. It opened in less than 2 minutes.
Laptop: Acer Travelmate 4740
Ram: 2GB
OS: Ubuntu 13.04
Comment 8 B.J. Herbison 2013-07-02 09:47:29 UTC
(In reply to comment #7)
> I'm using LO 4.0.2 shipped with Ubuntu 13.04.
> I don't have any problem opening the attached file with 107 sheets. It
> opened in less than 2 minutes.

When I saved this sample file in .ods format I observed the issue from comment 6 (which I have also seen in my large files): The progress bar goes to around 90% quickly, flutter around for a while, then goes to about 98%, stays unchanged for a long time, then vanishes.

It's also worth noting that there is a period of time between when the progress bar vanishes and calc is usable after the save. It appears there is some processing happening after the progress bar vanishes that should be covered by the progress bar.

A logging feature that records the state of the save with every progress bar change could:

1) Provide information to produce a smoother progress bar display, which would make it less likely for users to think that calc has hung. The fluttering, pause at 98%, and the unresponsive period after the bar vanishes are all worrisome as a user.

2) Give some hints about which parts of the save are unexpectedly slow (from the view of the original progress bar implementer) and might suggest areas to consider for performance improvements.
Comment 9 Alex Thurgood 2015-01-03 17:38:06 UTC Comment hidden (no-value)
Comment 10 Robinson Tryon (qubit) 2015-12-09 18:07:56 UTC Comment hidden (obsolete)
Comment 11 Rpnpif 2015-12-14 17:07:20 UTC
Created attachment 121301 [details]
Another for test
Comment 12 Rpnpif 2015-12-14 17:08:04 UTC
I can confirm this issue with another with one sheet but of about 25000 rows from Excel also converted in ODS on 5.0.3 with Linux Debian 8 and Ubuntu 14.

The loading or closing is very slow. Some edit operation also. Another issue is a big needing of memory to 2.5 GB (from top) after some operations than reduce never until close LibreOffice. CPU activities were high or huge.
Comment 13 Kevin 2017-06-10 05:27:26 UTC
I can comfirm this and I would add that Calc in general is much much slower than Excel.

1. open attached "big_file.ods"
2. hold down control and use the mouse wheel to zoom in and out
3. select all
4. apply a command
5. double click to enter a single cell
6. save
7. close and reopen

all of these operations take much MUCH longer than Excel and sometime give the appearance of a crash or lock the program into a state where in continually scrolls in one direction or the other, taking up to 30 seconds to return control to the user.
Comment 14 Kevin 2017-06-10 05:31:14 UTC
Created attachment 133938 [details]
file to demonstrate the extreme slowness of this program versus Excel

I had expected Calc, being newer and less corporate, to be less bloated, have a smaller footprint and perform better than Excel. It's the opposite.
Comment 15 mitchfx 2017-08-16 19:39:49 UTC
Calc 5.4.0.3 release is significantly slower to open, edit and save than Calc 5.3.5.2 for the exact same spreadsheet. Calc 5.4.0.3 also can't open some files that Calc 5.3.5.2 can easily open. Calc 5.4.0.3  will crash on very large sheets and show an Allocation failed error dialog on some of the large spreadsheets that the older version will easily open.

The spreadsheets I use do have a lot of data in them. Only 1 sheet but greater than 60,000 rows in 6 columns. And 3 charts showing 1 column of data each.

I would look at what was added between Calc 5.3.5.2 and Calc 5.4.0.3 to see what code causes the 5.4.0.3 to be so slow that it is unusable.
Comment 16 Aron Budea 2017-08-20 22:47:03 UTC
(In reply to mitchfx from comment #15)
> The spreadsheets I use do have a lot of data in them. Only 1 sheet but
> greater than 60,000 rows in 6 columns. And 3 charts showing 1 column of data
> each.
> 
> I would look at what was added between Calc 5.3.5.2 and Calc 5.4.0.3 to see
> what code causes the 5.4.0.3 to be so slow that it is unusable.

Please open a new bug report with a sample spreadsheet.
Comment 17 QA Administrators 2018-08-21 02:34:55 UTC Comment hidden (obsolete)
Comment 18 Rpnpif 2018-08-23 10:10:46 UTC
The issue seems to be fixed for me.
Tested on Version: 6.1.0.3
Build ID: efb621ed25068d70781dc026f7e9c5187a4decd1
Threads CPU : 2; OS : Linux 4.9; UI Render : par défaut; VCL: gtk2; 
Locale : fr-FR (fr_FR.utf8); Calc: group threaded