Bug 61837 - FILESAVE: perf:Calc takes long time to load and save file about 50 MB in size.
Summary: FILESAVE: perf:Calc takes long time to load and save file about 50 MB in size.
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL: https://docs.google.com/file/d/0B8qaX...
Whiteboard:
Keywords: haveBacktrace, perf
Depends on:
Blocks: Calc-Images
  Show dependency treegraph
 
Reported: 2013-03-05 06:50 UTC by abhimj
Modified: 2019-04-24 11:13 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Perf flamegraph (841.09 KB, image/svg+xml)
2019-04-24 09:54 UTC, Buovjaga
Details

Note You need to log in before you can comment on or make changes to this bug.
Description abhimj 2013-03-05 06:50:38 UTC
Problem description: 
I need to do the following- 
1) Data is organized in single column A 
2) I have to select some rows at a time copy them and transpose then in column B in order to split that in columns. I have created a macro for this. At a time, i select about 15 rows. The macro execution takes about 2 seconds to execute completely. Same file, same macro on Excel, its instantaneous. 
Details of the File as follows - Note- the file has been created in Excel saved in XLS format. Converting it to XLSX makes no difference. 
1) File size ~55MB 
2) Number of sheets - ~30 
3) Rows per sheet - ~10500 
Saving the file in Calc takes > 5 minutes. Till the files is completely saved, cannot use any other LibreOffice tool. Saving the same file on Excel is within a minute at the most. Strange thing observed, the macro execution becomes a little faster when I am charging the laptop.
Steps to reproduce:
1. ....
2. ....
3. ....

Current behavior:
1) Macro execution takes about 2-3 seconds to completely execute.
2) File saving and loading takes mores that 5 minutes.
Expected behavior:
1) Should be instantaneous.
2) Shouldnt take more than a minute.
              
Operating System: Windows 7
Version: 4.0.0.3 release
Comment 1 Jorendc 2013-03-05 17:43:18 UTC
Is it possible to upload that file to an external upload-server (Dropbox, Google Drive, SkyDrive, ...)?

If this document contains confidential (personal, business, medical, ...) information, please do not hesitate to send the link only by mail. In that case the document will only be shared with known developers and QA volunteers who'll work on this issue.

Thanks in advance,
Joren
Comment 2 abhimj 2013-03-06 13:50:39 UTC
Would it be ok if i attach i the bug itself.

-Best regards,
Abhijeet

On Tue, Mar 5, 2013 at 11:13 PM, <bugzilla-daemon@freedesktop.org> wrote:

>  Jorendc <joren.libreoffice@telenet.be> changed bug 61837<https://bugs.freedesktop.org/show_bug.cgi?id=61837>
>  What Removed Added  Status UNCONFIRMED NEEDINFO  CC
> joren.libreoffice@telenet.be  Ever confirmed   1
>
>  *Comment # 1 <https://bugs.freedesktop.org/show_bug.cgi?id=61837#c1> on bug
> 61837 <https://bugs.freedesktop.org/show_bug.cgi?id=61837> from Jorendc<joren.libreoffice@telenet.be>
> *
>
> Is it possible to upload that file to an external upload-server (Dropbox,
> Google Drive, SkyDrive, ...)?
>
> If this document contains confidential (personal, business, medical, ...)
> information, please do not hesitate to send the link only by mail. In that case
> the document will only be shared with known developers and QA volunteers who'll
> work on this issue.
>
> Thanks in advance,
> Joren
>
>  ------------------------------
> You are receiving this mail because:
>
>    - You reported the bug.
>
>
Comment 3 abhimj 2013-03-06 13:51:10 UTC
Would it be ok if i attach it in the bug itself?

Best regards,
Abhijeet

On Wed, Mar 6, 2013 at 7:20 PM, Abhijeet Joshi <abhipun11@gmail.com> wrote:

> Would it be ok if i attach i the bug itself.
>
> -Best regards,
> Abhijeet
>
>
> On Tue, Mar 5, 2013 at 11:13 PM, <bugzilla-daemon@freedesktop.org> wrote:
>
>>  Jorendc <joren.libreoffice@telenet.be> changed bug 61837<https://bugs.freedesktop.org/show_bug.cgi?id=61837>
>>  What Removed Added  Status UNCONFIRMED NEEDINFO  CC
>> joren.libreoffice@telenet.be  Ever confirmed   1
>>
>>  *Comment # 1 <https://bugs.freedesktop.org/show_bug.cgi?id=61837#c1> on bug
>> 61837 <https://bugs.freedesktop.org/show_bug.cgi?id=61837> from Jorendc<joren.libreoffice@telenet.be>
>> *
>>
>> Is it possible to upload that file to an external upload-server (Dropbox,
>> Google Drive, SkyDrive, ...)?
>>
>> If this document contains confidential (personal, business, medical, ...)
>> information, please do not hesitate to send the link only by mail. In that case
>> the document will only be shared with known developers and QA volunteers who'll
>> work on this issue.
>>
>> Thanks in advance,
>> Joren
>>
>>  ------------------------------
>> You are receiving this mail because:
>>
>>    - You reported the bug.
>>
>>
>
Comment 4 Jorendc 2013-03-06 14:02:15 UTC
@abhimj: please do not reply the email you get, but browse to this bug (link https://bugs.freedesktop.org/show_bug.cgi?id=61837) and leave a comment there.

(In reply to comment #2)
> Would it be ok if i attach i the bug itself.

I'm afraid that isn't possible. There is a file size limit, so a 50 MB file would be rejected. If you can send that big documents by email to me, I can do it for you.

Kind regards,
Joren
Comment 5 abhimj 2013-03-06 14:12:52 UTC
OK, I have the file on my google drive account. 
Can you please let me know with whom can i share it with?

Thanks.
Comment 6 abhimj 2013-03-06 14:15:37 UTC
I have shared the file on joren.libreoffice@telenet.be.
File name is English_611-900.xls
Comment 7 Jorendc 2013-03-06 14:33:29 UTC
(In reply to comment #6)
> I have shared the file on joren.libreoffice@telenet.be.
> File name is English_611-900.xls

Saw that, thanks for sharing.
Is it possible to post the link in this bug so everyone who looks at this bug can download it immediately? See http://support.google.com/drive/bin/answer.py?hl=en&answer=37579 how you can do this.

If this is not possible (as far I can see there is no confidential information in it, but if you don't want to we would not oblige you something), I can provide this document to all well known QA-volunteers and developers.


Okay, back to the bug report: 

I can't reproduce this behavior.
What I noticed:
* Open document (about 30 seconds)
* It took another 30 seconds till all images where loaded
* Scrolling seems not that fluently
* File > Save As and save it with another name took me also 30 seconds

Tested using Linux Mint 14 x64 with LibreOffice 4.0.1.2; intel core i5, 4GB memory, ...

So the only thing I can confirm is that scrolling through this document isn't that fluent.
Comment 8 Jorendc 2013-03-06 14:34:37 UTC
@Joel: you mind taking a look at this one?
Comment 9 abhimj 2013-03-06 15:15:10 UTC
@Joel,@Joren,
Can you try the following -
Record a macro with the following steps -
1) Select some rows (i select beginning from the row whihc has the book name author name, down 10-15 rows.)
2)Copy them.
3) Paste special-Transpose in a row in the column B.
4) Assign a key board shortuct and execute the macro.

I use this on Windows 7 64 bit machine.
Comment 10 Joel Madero 2013-03-06 16:05:02 UTC
@abhimj - please don't add irrelevant requests to the comments. We try to keep FDO clean and to do that any comments should be relevant to this bug - recording  a macro and all of that seems well outside the scope
Comment 11 Joel Madero 2013-03-06 16:14:51 UTC
My mistake - I didn't read the report correctly :) Ignore my last comment, reading over everything again (again my apologies, early in the morning here!)
Comment 12 Joel Madero 2013-03-06 16:35:55 UTC
Sorry for the spam  but I'm really unclear about this bug. I can open the file fine, saving it takes a minute or so (not sure compared to excel). I don't see a macro at all on my side when I download the file, furthermore, if you created the macro in Excel I'm shocked that it would even work in Spreadsheet as we don't offer cross compatibility. 

Can you explain a bit more, sorry for the confusion just not sure what the true issue is. According to the title you say load and save takes forever, but then in bug explanation you talk about macros (which again, I don't see  macro on the sheet). If it's about loading/saving the file, loading the file takes less than a minute, saving the file also takes less than a minute
Comment 13 abhimj 2013-03-07 01:51:54 UTC
Sorry my mistake, i should have summarized the exact problems more clearly.

I am facing two problems -
1) Load and save the file takes > 5 minutes. Same when Calc auto saves the changes for recovery.
     - On Excel, it gets loaded and saved within a minute.
2) Macro execution (with the steps that i earlier mentioned) takes about 3-5 seconds.
     - On Excel, its instantaneous.
     - The file that is uploaded on the drive doesnt contain the macro. I created it seperately in Calc and Excel with the same steps. Thats why i requested to tyr creating the macro with the steps.

The reason for the slow execution of macro could be as following as mentioend by @Joren
>>So the only thing I can confirm is that scrolling through this document isn't that fluent.
Because thats exactly what i did while recording the macro - Shift+down arrow key to select the required rows. Now that itself is slow.
But as i have menitioned in the original description, the scrolling or the selection part speeds up by about 10-20% when i am charging my laptop. Thats really weird, but thats what is happenning.

Unfortunately cannot try on Linux. dont have one.

I am ok with the slow macro execution actually,  but the load/save times is a real pain.

I have updated the bug with the link to the file.
Comment 14 Joel Madero 2013-03-07 15:13:32 UTC
What are your system specs? I definitely don't see 5 minute loads/saves.
Comment 15 Joel Madero 2013-03-07 15:41:11 UTC
Tested on 2nd machine and indeed - very slow. 

Tested machine: Windows 7 running LibreOffice 4.0.0.3 release, i3, 1.8 ghz, 4 GB RAM

For me opening file took 3 minutes 50 seconds.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Makes document almost unusable.

Marking as:
NEW (confirmed)
Normal (no loss of data, just very slow)
Medium (makes document first use very tedious, need to turn off auto save to prevent performance issues) 

The reason why I left this as Medium is that it's a very specific file that I'm not sure if Spreadsheet is being used as it's intended to be used. There may be room for using database here instead, 30 sheets + 10500 rows, 50 megs - usually a good sign that database should be used but that's just my opinion


Saved as ODS it drops down load time to about 45 seconds.
Comment 16 abhimj 2013-03-07 18:57:50 UTC
Exact machine specs on which I have tried this -
Processor - i3-2350M CPU @2.30GHz quad.
RAM - 3GB.
OS - Windows 7 64 bit.
Model - Inspiron N5110

Tried with saving it as ODS. The save/load time does reduce drastically, scrolling improves just only a bit though (which contributes to the overall macro execution time as I had explained earlier).
Besides, I would like to keep it to xls(or xlsx format) because I have to share that file with others who user Excel.

Any thoughts about why would it seem to be faster when the laptop is charging?

Thanks
Abhijeet
Comment 17 Joel Madero 2013-03-07 19:02:10 UTC
macro issue should be reported separately :) 1 bug report = 1 bug :)

for the charging thing - no that's probably the most interesting thing I've heard. I wonder if it can be quantified....maybe your CPU works a bit faster when 100% charged or on AC... no clue really
Comment 18 abhimj 2013-03-10 06:04:35 UTC
>>macro issue should be reported separately :) 1 bug report = 1 bug :)
Agreed.
opened a new bug - Bug 62087 - Performance:Very slow scrolling.
Theres no problem with the macro execution as such, the scrolling is slow which affects the macro execution time.
Comment 19 b_power 2013-05-21 12:12:47 UTC
Hi, 

Some info which may be useful in improving SAVE performance.

I just started with LibreOffice 3.6.6 - moving from Excel 97. 

Am using Windows XP. 2GiB RAM. Pentium M.

Two xls docs open in LibreOffice - one has 18 sheets / variable amounts of data, the other 150 sheets lots of data on some pages.

I made a small change to one cell in one sheet in each file - added some text. I then clicked save ( as .xls ) ;

The 18 sheet file wasn't too bad. 
The 150 sheet file takes quite a while.

I suspect it is looking at and possibly saving all sheets - which is not necessary as they haven't changed. There is not much data in the one page that has changed ( and it's all text with no calcs / links etc ) so a long save isn't justified. 

Confirmation of behaviour would allow workarounds / avoidance while awaiting fix.

Bob.
Comment 20 Alex Thurgood 2015-01-03 17:38:53 UTC Comment hidden (no-value)
Comment 21 Robinson Tryon (qubit) 2015-12-09 18:08:31 UTC Comment hidden (obsolete)
Comment 22 QA Administrators 2017-01-03 19:43:28 UTC Comment hidden (obsolete)
Comment 23 Buovjaga 2019-04-24 09:54:39 UTC
Created attachment 150970 [details]
Perf flamegraph

Saving takes about 14 secs for me (hard to say exactly as there is no progress bar...)

Arch Linux 64-bit
Version: 6.3.0.0.alpha0+
Build ID: cfbb223d5666cb803539ac98918ff39b27efc6e7
CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: gtk3; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Built on 24 April 2019
Comment 24 Buovjaga 2019-04-24 10:03:19 UTC
I discussed this on IRC and it was thought that fileopen and filesave for this are now quite optimised. Let's close and celebrate.
Comment 25 Xisco Faulí 2019-04-24 11:13:16 UTC
in

Version: 6.3.0.0.alpha0+
Build ID: 90e3b47b52f26420425a7417d2f51b6a386282d9
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US
Calc: threaded

it takes

real	0m35,916s
user	0m33,886s
sys	0m1,887s

while in

Version: 6.0.0.0.alpha1+
Build ID: 6eeac3539ea4cac32d126c5e24141f262eb5a4d9
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group threaded

it takes 

real	1m8,167s
user	1m2,033s
sys	0m4,738s