Bug 151406 - CALC: Changing cell background via macro is very slow
Summary: CALC: Changing cell background via macro is very slow
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
7.4.0.3 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisectRequest, regression
Depends on:
Blocks: Macro
  Show dependency treegraph
 
Reported: 2022-10-07 13:44 UTC by Julian Ragan
Modified: 2022-10-19 18:55 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Diagnostic macros (11.71 KB, application/vnd.oasis.opendocument.spreadsheet)
2022-10-07 13:44 UTC, Julian Ragan
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Julian Ragan 2022-10-07 13:44:35 UTC
Created attachment 182902 [details]
Diagnostic macros

After upgrading LO to 7.4 from 7.1 we have found that some macros "hang", I have narrowed it down to a for loop that is changing cell style in an entire row. Part of the problem is that 7.4 allows for many more columns, than 7.1, but even if I limit the number of columns in 7.4 to  1024, it is still much slower, than 7.1

The attached file contains macros that were used to narrow down the nature of the bug, there are two entry subroutines runAs71 and runAsDefault, both setup the test and run it, the main difference is that runAsDefault on 7.4 will try to iterate over every column in row 5 while runAs71 will only iterate over 1024 columns. You can adjust the number of runs (I have set it to 10) and time sampling interval (I have set it to 100 iterations)

For example sub runAs71 from attached testing macro in 7.1 gives the following results:
Total Time = 12658ms
Avg. min = 82,2ms, avg. max = 161,4ms, avg. time = 134,8m

And the same configuration run on 7.4 produces the following results:
Total Time = 63304ms
Avg. min = 540,2ms, avg. max = 687,3ms, avg. time = 682,2ms

I have abandoned the idea of running the test over all columns in version 7.4, since it was at 7th run after more than an hour or two.

Versions used in testing:

Version: 7.4.0.3 (x64) / LibreOffice Community
Build ID: f85e47c08ddd19c015c0114a68350214f7066f5a
CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: pl-PL (pl_PL); UI: pl-PL
Calc: CL

Version: 7.1.3.2 (x64) / LibreOffice Community
Build ID: 47f78053abe362b9384784d31a6e56f8511eb1c1
CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: Skia/Vulkan; VCL: win
Locale: pl-PL (pl_PL); UI: pl-PL
Calc: CL
Comment 1 Roman Kuznetsov 2022-10-15 15:26:23 UTC
Total Time = 25161ms in

Version: 7.5.0.0.alpha0+ / LibreOffice Community
Build ID: 55ee3ede2bb0211e895053ed3a54bb1c99cc94ca
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb)
Locale: ru-RU (ru_RU.UTF-8); UI: en-US
Calc: threaded

I have here an 12 years old mobile 2-core Intel CPU

Mike, could you please look at it
Comment 2 Roman Kuznetsov 2022-10-16 13:58:30 UTC
I checked in several versions of LibreOffice on the same notebook (but it was another, more modern machine than in Comment 1) and I got:

7.5 - Total Time = 16229ms
7.4 - Total Time = 59505ms
7.3 - Total Time = 86750ms
7.2 - Total Time = 11296ms
7.1 - Total Time = 10944ms

There is still 1.5x speed difference between 7.1 and current 7.5.

I couldn't bisect it because win64-7.3 bisect repo was updated (WHY?!) to

Version: 7.3.7.0.0+ (x64) / LibreOffice Community
Build ID: d0312786571221c2dd4f63fa69f6f0489d7d39ec
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: default; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded

that alreay include the fix

But I tried find the patch that fixed problem in 7.5 and I got
 938c899831bd5b165b2b7db3945867a1ce155483

And I'm not sure in result
Comment 3 Mike Kaganski 2022-10-16 17:49:30 UTC
So this was a regression from commit 3749d9af3745c0eaff7239e379578e4e2af89e9d (bibisected using 7.3), and it was reverted in commit 938c899831bd5b165b2b7db3945867a1ce155483, so it is fixed.

I don't know if it's possible to find the commit responsible for the residual 1.5x slowdown, or if it's even some artifact of e.g. development builds used to check the fixed versions?
Comment 4 Mike Kaganski 2022-10-16 17:51:42 UTC
(In reply to Julian Ragan from comment #0)

Regarding the macro: consider applying styles to ranges rather than to individual cells - it is generally much faster.
Comment 5 Julian Ragan 2022-10-17 06:59:48 UTC
(In reply to Mike Kaganski from comment #4)

Indeed it is, 11 - 16 ms for an entire row with 16384 cells. Thanks.