[Libreoffice-bugs] [Bug 108560] Pasting or editing multi-line cells ranges from slow to unusable Calc

Sat Nov 28 05:55:56 UTC 2020

https://bugs.documentfoundation.org/show_bug.cgi?id=108560

Francewhoa <francewhoa+bugs.documentfoundation.org at ubertus.ca> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|WONTFIX                     |---
             Status|RESOLVED                    |REOPENED

--- Comment #27 from Francewhoa <francewhoa+bugs.documentfoundation.org at ubertus.ca> ---
Hello again all LO enthusiasts :) This is to confirm that this challenge is
still present with the recent LO Calc version 7.0.3.1. And also with both
versions 6.1.5 and 5.2.7. So I'm re-opening this ticket. Same challenge with
OpenGL deactivated or activated.

I'm the original author of this ticket. I tried to edit my ticket "Description"
but found no way to do this. Anybody know how to do this?
Meanwhile, this present comment 27 cancels and replaces my original ticket
Description above. It is the same Description, but to facilitate collaboration,
this new comment 27 is a clarified and updated Description of the challenge. I
also clarified the ticket title to: "Pasting or editing multi-line cells ranges
from slow to unusable Calc"

This challenge can be reproduce with 7.0.3.1, and 6.1.5, and 5.2.7. But could
not be reproduce with 4.2.
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- 

Challenge summary:

Using LibreOffice (LO) Calc, pasting large amount of multi-lines text into a
cell result in Calc hangs. In turn, all other LO applications hang. This global
hang ranges from seconds to minutes. This range depends mostly on the amount of
multi-lines text, and your CPU. In turn, Calc and all LO application are
unusable. Same challenge with editing cells with large amount of multi-lines
text.
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- 

Steps to reproduce challenge:

1. Using the following fresh installs:
______• LO: 
____________• Version: 7.0.3.1
____________• Build ID: 00(Build:1)
____________• CPU threads: 8; OS: Linux 5.8; UI render: default; VCL: gtk3
____________• Locale: en-CA (en_CA.UTF-8); UI: en-US
____________• Debian package version: 1:7.0.3-3_bpo10+1
____________• Calc: threaded
____________• OpenGL: Deactivated or activated. Same result.
______• Debian:
____________• Version: 10. Buster. 64-bit.
____________• Gnome: 3.30.2 with Wayland
____________• Kernel: 5.8.0-0.bpo.2-amd64 #1 SMP Debian 5.8.10-1~bpo10+1
(2020-09-26) x86_64 GNU/Linux
______• Hardware:
____________• Ram: 31GB. Only LO is open and active.
____________• Processor: Intel Core i7 @ 3.10 GHz
____________• CPU: 8

2. Create a fresh Calc spreadsheet. Optionally, use the spreadsheet attached to
this Comment 1 above. Spreadsheet is titled:
"calc_spreadsheet---before---Slow_Edit_Large_Cell---Francewhoa---2017-06-15.ods"

3. Using any text editor to you liking, such as GEdit:

______3.1. Open the text file attached to Comment 12 above. This file is
titled:
"lorem_ipsum---940_lines---5639_characters---Francewhoa---2017-06-17.txt"

______3.2. Copy all the text as is. Double check that your text editor does not
make any formatting operations on this text without notifying you. Most
importantly, the multi-lines text need to be kept as is. Not automatically
reformatted on one line. If unsure about this, I suggest to use a very small
and simple text editor. Such as GEdit.

5. Using LO Calc, using cell A1, double check that the cell is presently
configured with Calc defaults. Including, but not limited to, no text
formatting of any kind. Do not make any change to the cell.

6. Before you proceed with the next step below. Heads up that both the Calc and
all other LO applications will likely hang for an extended period of time.
Maybe for 15 minutes. During that period, all LO applications are not usable
and 100% of a CPU is permanently use. So before proceeding with the next step
below, I suggest to either use a virtual environment, or save all your valuable
opened documents. As it's likely that you will need to kill LO Calc to resume
using LO. Which risk to result in both valuable time lost and data lost.

7. Still using LO Calc, still using cell A1, paste the text as as plain text.

8. LO hangs. This is the FIRST challenge:
______• This hang ranges from seconds to minutes depending mostly on the number
of multi-lines text
______• During this hang, one CPU is running at 100%.
______• In my case, Calc needs 14 minutes to process this one simple paste
operation for this file
"lorem_ipsum---940_lines---5639_characters---Francewhoa---2017-06-17.txt"
______• After this hang the text is pasted
______• In this comment 24 above, the attached spreadsheet shows the end result
after this step is done. The file is titled
"calc_spreadsheet---after---Slow_Edit_Large_Cell---Francewhoa---2020-11-27.ods"

9. Double click on this cell A1 to open it. LO hangs again. This is the SECOND
challenge. And so on. In other words, at each paste, or edit, this cell result
in a hang. Thus LO and all its application are unusable. Because at each
operation the hang returns. As of November 27th, 2020 we were able to reproduce
this challenge on multiple devices. With freshly installed LO ranging from
versions 5 to 7.

10. If somehow you're not able to reproduce this challenge, I suggest to double
that you use the same steps as above as is, and using the attached files.
Otherwise it is risky that you won't be able to reproduce. Also double check
that the text editor you use to copy paste the text, does not do any operation
on the text without notifying you or asking you to confirm. Optionally, for
testing shorter hangs or long hangs, I attached smaller test files into:
• Comment 8
• Comment 9
• Comment 10
• Comment 11
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- 

Expected outcome:

• Expected result is the text should be pasted into the cell within ~1 second.
Or edited within ~1 second. Which are the fast performance back in the time of
dinosaur with LO 4.2 ;) Not ~30 seconds not 15 minutes with the present LO
version 7. 30 seconds is very fast if we need to do one operation within ~one
day. But the challenge is that we need to do multiple operations within 1
minute. Then we need to do that multiple time per day, week, year. For example
when we need to do 10 to 20 copy-paste operations per minute, the total freeze
time range from 5 to 10 minutes. Per operation. Which is too slow. And not
usable. Another example is with 100 to 200 copy-paste, which result in a total
freeze ranges 50 to 100 minutes. Again too slow. There is something slowing
down both the copy-paste process of a large cell and opening a large cell. It's
unclear what is causing that.
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- 

Suggested resolution:

• How about resolving this with this attached mockup titled
"mockup---suggested_resolution---francewhoa---ksnip---2020-11-27_nov---v2.jpg"?
at https://bugs.documentfoundation.org/attachment.cgi?id=167629
Where:
___• Number 1 shows the user using "Format Cells" on a selected cell(s)
___• Number 2 shows the user clicks on "Cell Protection" horizontal tab
___• Numbers 3 & 4 show a new group titled "XML Parse Buffer". With a new
checkbox titled "Do not parse". Using simply check this new "Do not parse" box.
Bingo done. Resolved.
____ With help text reading "If checked, the cells selected will be ommited
from the automated XML Parse Buffer operations. Which may result in speed
increase for operations such as pasting or editing multi-line cells. To the
cost of automated operations by the XML Parse Buffer." 
___• By default this "Do not parse" check box is not checked. So that no change
are made to past or future spreadsheets. But interested users, like me, would
be able to check this box per cell. Thus speeding up multi-line cells. Similar
to faster performance back with LO 4.2.
___• I'm not a developer, I'm both a quality assurance/tester & end-user.
Attribution to Steve Magoun for the inspiration about the title "XML Parse
Buffer". This is just a mockup to facilitate communications, of course both
this suggested title and its help text could easily be adapted. To better
reflect what is the primary cause of this challenge. After it is identified.

• Allow users to add large amount of text without triggering whatever is
triggering intensive CPU usage. Assuming the user is adding simple text and
without complex formatting and without any calculation. No cell validation is
requested, but maybe somehow LO is trying to validate the full content of the
cell. If so, how about allowing the user to deactivate such automated
validation per cell, per sheet, per spreadsheet, per LO global setting?
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- 

What we tried that was EFFECTIVE:

• Using LO 4.2. Instead of 5.2.7. This issue can not be reproduce on LO 4.2.
• Using 5.2.7, brake down the large cell content into multiple small cells. But
that's not usable, as I need to add large amount of text to large amount of
cells, and sheets.
• Using Microsoft Excel .xlsx files. This issue can not be reproduce.
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- 

What we tried that was INEFFECTIVE:

• Use LO 7.0.3.1. Instead of 5.2.7.
• Use LO 6.1.5. Instead of 5.2.7.
• Activate OpenGL or deactivate OpenGL. Same results.
• "Format" menu > "Cell" option > "Numbers" > Text 
• "Format" menu > "Clear Direct Formatting" option 
• Increase memory at “Tools > LO > Memory“
• Force LO to use the latest installed Java 1.8 at “Tools > Options > LO >
Advanced > Expert Configuration”
• Close the LO sidebar
• "Format" menu > "Cell" option > "Alignment" > unchecked "Wrap text
automatically" 
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- 

Speculation:

• The amount of multi-lines text in the cell is directly correlated to the
length of the hang. So 470 lines is roughly double the hang time than 235
lines. And so on.
• This challenge is maybe related to the XML_ParseBuffer() and its children.
Details and trace output at
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1034999/comments/3
• My guess is that somehow Calc tries to validate the cell. In turn, within the
cell, each time Calc reach the end of a line, Calc detect a break line, also
called text wrapping, somehow this restart the validation over. In turn, this
is repeated at each line. Until Calc reaches the last line. But there is
nothing to validate. No calculation. No text formatting. No condition. No
nothing on that cell. Except plain text.
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- 

Similar challenges:

• https://bugs.launchpad.net/ubuntu/+source/LO/+bug/1034999
•
https://askubuntu.com/questions/925383/LO-calc-is-slowing-down-when-working-with-big-cells
• https://ask.LO.org/en/question/75074/LO-5204-unbearably-slow-on-ubuntu-1404/
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- 

Any volunteer for a patch? I would be happy to contribute testing and
documentation if needed.

Let me know if you have any questions or need anything else

Cheers,

Francewhoa

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20201128/cfb39c27/attachment-0001.htm>