Bug 139304 - WAY too many rows. It gives performance problems.
Summary: WAY too many rows. It gives performance problems.
Status: RESOLVED INSUFFICIENTDATA
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.1.0.1 rc
Hardware: Other Windows (All)
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-12-29 11:18 UTC by MMM
Modified: 2021-11-28 19:40 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample file (7.99 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-12-30 11:19 UTC, m_a_riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description MMM 2020-12-29 11:18:25 UTC
Description:
The 1 million rows gives both logistical as speed issues.
Please give the users an option to physically LIMIT the number of rows and columns. If I copy mark up to all cells below with ctrl+shift+end, it is ALSO unwanted copied to all hidden rows. Then scalc hangs for a short time. It does NOT hang for a short time if I copy the mark up only to the visable number of rows.
So... HIDING the rows is NOT a solution.
Of course, there are some power users who use many rows. But for 99% of users, 1 million is WAY too much. 1 k Would be MORE than enough in most usage.

Calc version: 7.0.4.2

Steps to Reproduce:
1. Hide rows 100 to 2^20.
2. Copy mark up of some cells to far below with ctrl+shift+end.
3. Then there is the wait. There is NO WAIT of you would copy it only to 1000 cells. But... There's no easy way to quickly copy the mark up to only 1000 rows.

Actual Results:
Waiting time.

Expected Results:
Waiting time. But... the 'Expected Results' would be NO waiting time, if there weren't so many rows.


Reproducible: Always


User Profile Reset: Yes


OpenGL enabled: Yes

Additional Info:
Offer a possibility to limit the number of rows and columns; or add the option for a 'slimmed down' sheet with 32 columns and 1024 rows.
Comment 1 m_a_riosv 2020-12-30 11:19:25 UTC
Created attachment 168575 [details]
Sample file

Maybe I'm wrong, but it is easy to get it with sheet protection, disabling 'Select protected cells'

At least since
Version: 7.0.5.0.0+ (x64)
Build ID: e29387a749edb1bf39ca2b558099964da42b3a4b
CPU threads: 4; OS: Windows 10.0 Build 20180; UI render: Skia/Vulkan; VCL: win
Locale: es-ES (es_ES); Interfaz: es-ES Calc: CL
Version: 7.1.0.1 (x64)
Build ID: b585d7d90ab863bf29b2d110c174c0c2a98f3ee4
CPU threads: 4; OS: Windows 10.0 Build 20180; UI render: Skia/Vulkan; VCL: win
Locale: es-ES (es_ES); UI: es-ES Calc: CL
Comment 2 MMM 2020-12-30 18:26:45 UTC
(In reply to m.a.riosv from comment #1)
> Created attachment 168575 [details]
> Sample file
> 
> Maybe I'm wrong, but it is easy to get it with sheet protection, disabling
> 'Select protected cells'
> 
> At least since
> Version: 7.0.5.0.0+ (x64)
> Build ID: e29387a749edb1bf39ca2b558099964da42b3a4b
> CPU threads: 4; OS: Windows 10.0 Build 20180; UI render: Skia/Vulkan; VCL:
> win
> Locale: es-ES (es_ES); Interfaz: es-ES Calc: CL
> Version: 7.1.0.1 (x64)
> Build ID: b585d7d90ab863bf29b2d110c174c0c2a98f3ee4
> CPU threads: 4; OS: Windows 10.0 Build 20180; UI render: Skia/Vulkan; VCL:
> win
> Locale: es-ES (es_ES); UI: es-ES Calc: CL

Thank you for your response. I didn't think of that option. But, unfortunately, it it gives a new problem: if you want to copy mark up to the end of a column with ctrl+alt+end, you get a warning that you can't edit cells EVERY single time.
It would be helpful if they did something with this bug: https://bugs.documentfoundation.org/show_bug.cgi?id=120726.
I will add an entry there.

Either way, I think it would be best that there would be a native option to just completely get rid of most rows and columns. Giving the user maximum flexibility/options is key for getting to optimal software.
I'm sure that MANY spreadsheet users would use the new option, if it would be implemented.
Comment 3 m_a_riosv 2020-12-31 10:27:26 UTC
Seems really unfortunately, the software doesn't fir your requirements.

There it's not an improvement in the performance of doing so, but in the contrary, surely. Oh, perhaps for your usability.

There is an experimental option to allow 16 million rows and 16 thousand columns, it has near two years on development, and not finished jet.

So I think this will never happen at least in short/middle time.
Comment 4 Buovjaga 2021-11-26 07:50:04 UTC
(In reply to MMM from comment #0)
> Steps to Reproduce:
> 1. Hide rows 100 to 2^20.
> 2. Copy mark up of some cells to far below with ctrl+shift+end.
> 3. Then there is the wait. There is NO WAIT of you would copy it only to
> 1000 cells. But... There's no easy way to quickly copy the mark up to only
> 1000 rows.

Steps were not clear, but tried with these:

1. Input something to A1048576
2. Select range A100:A1048576 by using the Name Box
3. Right-click a row number, Hide Rows
4. Input something in a visible cell in A column
5. In the cell with data, Ctrl+Shift+End to select to end of file
6. Ctrl+D to fill down

There is no wait. Now if I select all, right-click a row, Show Rows, I see that it has not filled down to A1048576.

I also tested with all rows shown, filling down to A1048576 and it only takes 1 second.

Please explain how I can experience the wait that is causing you problems.

In comment 2 you mention bug 120726, but it is about Writer tables, not Calc.
Comment 5 MMM 2021-11-28 15:02:43 UTC
"Please explain how I can experience the wait that is causing you problems."

I don't know. I gave up, spending hours and hours to find a solution.
I'm not using scalc anymore; it was unworkable for me in the sheet I wanted to make. The only difference I could see, was the number of cells you copied it to.

BTW: waiting 1 second is still 1 second to much, if you do such copying often. In Excel, the wait is 0 seconds, doing the exact same.
Comment 6 MMM 2021-11-28 15:13:53 UTC
"In comment 2 you mention bug 120726, but it is about Writer tables, not Calc."

As you can read in bug 120726: "The message box is similarly popping up in Calc."
Although technically, it might need another bug report, because it is in Calc; not in Writer. I don't know if the code in the background is the same for both programs.
Comment 7 Buovjaga 2021-11-28 19:40:15 UTC
(In reply to MMM from comment #5)
> "Please explain how I can experience the wait that is causing you problems."
> 
> I don't know. I gave up, spending hours and hours to find a solution.
> I'm not using scalc anymore; it was unworkable for me in the sheet I wanted
> to make. The only difference I could see, was the number of cells you copied
> it to.
> 
> BTW: waiting 1 second is still 1 second to much, if you do such copying
> often. In Excel, the wait is 0 seconds, doing the exact same.

Ok, as you can't provide an example file we can't evaluate the performance. However, your original description sounded like there was a delay when filling only a 1000 cells, not 1048576.

I don't have access to the latest Excel or any Excel on my main machine, but I now tested with Excel 2013 on a less performant machine and it took 2 seconds to fill down to A1048576.

Calc is getting performance improvements all the time. The most recent significant one is autofilter, where testers reported it is now much faster than Excel for large data sets.