Bug 78254 - Substantial performance deterioration by scroll through cells via macro in LibreOffice Calc
Summary: Substantial performance deterioration by scroll through cells via macro in Li...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.3.3 release
Hardware: All All
: low normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected, perf, regression
: 107535 (view as bug list)
Depends on:
Blocks: Macro
  Show dependency treegraph
 
Reported: 2014-05-04 14:16 UTC by Tor24_1975314
Modified: 2019-08-11 11:25 UTC (History)
15 users (show)

See Also:
Crash report or crash signature:


Attachments
Scroll Down Performance - Test Macro (25.00 KB, application/vnd.ms-excel)
2014-05-04 14:16 UTC, Tor24_1975314
Details
profile result ... (482.75 KB, image/png)
2016-06-27 08:08 UTC, Michael Meeks
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Tor24_1975314 2014-05-04 14:16:28 UTC
Created attachment 98415 [details]
Scroll Down Performance - Test Macro

Hello,

I noticed a substantial performance deterioration by scroll through cells via macro in LibreOffice Calc since version 4.2.0 (up to 4.2.4.1 pre-release)!
To scroll through cells via macro needs in LibreOffice Calc 4.2.x more as four times as compared to Calc 4.0.x or 4.1.x!

Here my test results to scroll through 10.000 cells only,
needed time:

LibreOffice 3.6.5.2  00:18 [mm:ss]
LibreOffice 4.0.4.2: 00:15 [mm:ss]
LibreOffice 4.1.4.2: 00:15 [mm:ss]

LibreOffice 4.2.0.4: 01:40 [mm:ss]  (!!!)
LibreOffice 4.2.1.1: 01:07 [mm:ss]  (!!)
LibreOffice 4.2.2.1: 01:08 [mm:ss]  (!!)
LibreOffice 4.2.3.3: 01:07 [mm:ss]  (!!)
LibreOffice 4.2.4.1: 01:12 [mm:ss]  (!!)  (pre-release)
(reproducible with an aberration of around +/-1 second)

Needed time with Excel on the same computer to compare:
Excel 2010 with "Application.ScreenUpdating = False": ~00:01 [mm:ss]
Excel 2010 with "Application.ScreenUpdating = True" :  00:05 [mm:ss]

Test system: 
Windows 7 64-bit, Intel Core2Duo P8700 2.53 Ghz, 4 GB RAM, 
(Java SE Runtime Environment build 1.6.0_24-b07)

(Because the function "Application.ScreenUpdating = False" does not works in LibreOffice I opened already a separate bug report:
https://bugs.freedesktop.org/show_bug.cgi?id=52603 )

A xls file with a macro for the performance test is added as attachment!

Excel VBA macro code for the test:

Sub Test_ScrollDownPerformace()
    ' Performance test for scroll down via macro in Libre Office
    ' Testing Excel VBA Code in Libre Office:
    
    Dim start_time, stop_time As Date
    Dim needed_time As Double
    
    Range("A1").Select
    Application.ScreenUpdating = False

    start_time = Time()
    
    For i = 1 To 10000
        ActiveCell.Offset(1, 0).Select
        ' do something or check cell content
    Next i
    
    stop_time = Time()

    Range("A1").Select
    Application.ScreenUpdating = True
    needed_time = TimeValue(stop_time) - TimeValue(start_time)
    
    MsgBox ("Start: " & start_time & Chr(13) & _
            "Stop: " & stop_time & Chr(13) & Chr(13) & _
            "Needed Time: " & Format(needed_time, "HH:mm:ss"))
End Sub
Comment 1 Yousuf Philips (jay) (retired) 2014-06-14 23:59:55 UTC
Results of testing the attachment on a laptop with Windows 7 64-bit, Intel Core 2 CPU @ 1.83Ghz, 2.5GB RAM.

4.1.6 : 00:24 mins
4.2.6 : 01:20 mins
4.3.0 : 01:22 mins
4.4.0 : 05:36 mins
Comment 2 Joel Madero 2014-07-16 16:51:04 UTC
That was a nightmare to bibisect - anyways
Ubuntu 14.04


Prioritizing:
Minor - can slow down high quality work but will not prevent it (by definition this is about making things slower)
Low - with one test case hardly a high bug. Despite this being a regression as of now it's affecting exactly 1 user.

My best time in bibisect package was 22 seconds so that was my base time. One hit 23 seconds and I said that was fine. Most the other ones hovered between 45 seconds to 1:45.


21be8eddb95a12408b74f43d3effb9dc9821e99e is the first bad commit
commit 21be8eddb95a12408b74f43d3effb9dc9821e99e
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Fri Oct 18 04:23:31 2013 +0000

    source-hash-bcc51fb2ebdf77a1cc089857775fd742085b45b6
    
    commit bcc51fb2ebdf77a1cc089857775fd742085b45b6
    Author:     Noel Power <noel.power@suse.com>
    AuthorDate: Thu Aug 29 17:15:33 2013 +0100
    Commit:     Noel Power <noel.power@suse.com>
    CommitDate: Thu Aug 29 21:42:05 2013 +0100
    
        add support for in-place style preview
    
        selecting a style in the styles dialog ( without double clicking )
        will apply the style to the currently selected cell(s) You can
        with the keys navigate to other styles and they in turn will
        also be applied. Preview will end when you click back onto the
        document.
    
        *FIXME* - the styles dialog isn't really suitable for previewing, a
        new dialog ( possibly in the toolbar ) might be nicer ( see Excel )
    
        *FIXME* - when there is a multiple selection the highlight colour
        (applied as a transparent overlay) is most annoying ( and is mixed
        with any background colour applied if part of a style )
        see ( ScGridWindow::UpdateSelectionOverlay() ) However my puny
        attempts to make the selection use a transparent colour made all the
        borders of the selected cells dissappear. I guess maybe a box/border
         around each selected cell ( or group of cells ) would also work
        but I didn't try that
    
        Change-Id: I0950e79085ffb75f60ee961835665df0c230172f

:100644 100644 8fab1fdadbd439deb64a4f714bff2f9526411bf3 835fff1805e8dc39bfd7bd7d6cc135aeb3b3c181 M	autogen.log
:100644 100644 e26e2b8c270cd824114091d167dae2b14e9dc10c fd07dfa379a8bec1d8edcff2f495ba575f1ab416 M	ccache.log
:100644 100644 116323b70a7bf25f0a07e0534405af3833e28d4d 23a5b8b538220c7ff6416642ade815a875518df9 M	commitmsg
:100644 100644 eb158bbc099f5743ae6479d555bb6b76ba00ac72 4210b173f74258372c2837a9bef262ab02db4653 M	dev-install.log
:100644 100644 80d286b683b673d2a90e0ea3d07b47a728e6a0de 54e4b02174c8ab14e3f07c184475efc9604ca4ce M	make.log
:040000 040000 b98420146279e9da46dc037c56e55b12c36101e1 8e95f2d5dd47c365d05e430f6f5a6cd8656e4488 M	opt


# bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# good: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb
git bisect good e02439a3d6297a1f5334fa558ddec5ef4212c574
# bad: [4850941efe43ae800be5c76e1102ab80ac2c085d] source-hash-980a6e552502f02f12c15bfb1c9f8e6269499f4b
git bisect bad 4850941efe43ae800be5c76e1102ab80ac2c085d
# skip: [a043626b542eb8314218d7439534dce2fc325304] source-hash-9379a922c07df3cdb7d567cc88dfaaa39ead3681
git bisect skip a043626b542eb8314218d7439534dce2fc325304
# skip: [aba65c3e4c0df07e4909aeefb758cdb688242bf6] source-hash-827524abfb4b577d08276fde40929a9adfb7ff1a
git bisect skip aba65c3e4c0df07e4909aeefb758cdb688242bf6
# bad: [c81a8a0dcfc1ed095a80e4485c89dd0fcaf73f31] source-hash-c69ed33628ec0b7abf6296539cf280d6c4265930
git bisect bad c81a8a0dcfc1ed095a80e4485c89dd0fcaf73f31
# skip: [1d4980621741d3050a5fe61b247c157d769988f2] source-hash-89d01a7d8028ddb765e02c116d202a2435894217
git bisect skip 1d4980621741d3050a5fe61b247c157d769988f2
# skip: [9daa289e178460daaafa4b3911031df5b8736218] source-hash-704292996a3731a61339b1a4a5c90c9403aa095f
git bisect skip 9daa289e178460daaafa4b3911031df5b8736218
# bad: [56d7a7963ef4d32b0c5b60dc5f85d4bc218785d9] source-hash-1a412370ab03af8f3865ccbfaaa8dcff1d0ac0ad
git bisect bad 56d7a7963ef4d32b0c5b60dc5f85d4bc218785d9
# good: [66426e60b06c075436eaa9640140d3a263dc173c] source-hash-7e07a45500dcbb891a85f0bc9b7049cf4d50bba1
git bisect good 66426e60b06c075436eaa9640140d3a263dc173c
# good: [bac2489ff3b644bd046102e379bff5a6c6c469d9] source-hash-621c1e491e56db5416da1c763aaff862e8ede67a
git bisect good bac2489ff3b644bd046102e379bff5a6c6c469d9
# bad: [b946f469e1740faa557741120989330fa22df995] source-hash-d3ff876f3c7f441fd72a037ed31fb973f223ca6d
git bisect bad b946f469e1740faa557741120989330fa22df995
# good: [f47efc54c1f3916052ffda455e5ea179f6aa400a] source-hash-511354504cfc2c8f002752775d5bb336b01bd6ab
git bisect good f47efc54c1f3916052ffda455e5ea179f6aa400a
# bad: [e33eaf662f84503c8de782d6677d9eb1b0b0d96b] source-hash-6c3d74e8b779b1eb2d9779ed84f1518e078113c4
git bisect bad e33eaf662f84503c8de782d6677d9eb1b0b0d96b
# bad: [21be8eddb95a12408b74f43d3effb9dc9821e99e] source-hash-bcc51fb2ebdf77a1cc089857775fd742085b45b6
git bisect bad 21be8eddb95a12408b74f43d3effb9dc9821e99e
# first bad commit: [21be8eddb95a12408b74f43d3effb9dc9821e99e] source-hash-bcc51fb2ebdf77a1cc089857775fd742085b45b6
Comment 3 rlk 2014-12-08 02:37:57 UTC
Scrolling with the mouse wheel got markedly slower between 4.1.6 and 4.3 (don't know exactly where (Linux 64 bit).
Comment 4 Matthew Francis 2015-01-16 03:20:57 UTC
Can't bisect this any further - it doesn't take enough seconds for me to get a reliable reading on speed (0:10-0:18 from the bibisected range through to 4.4.0.2, varying a couple of seconds at random)

In any case, if there is a current issue then it's more a job for valgrind/cachegrind
Comment 5 Matthew Francis 2015-01-16 03:24:56 UTC
One additional note - running the attached micro-benchmark on a dbgutil build (e.g. 4.5 bibisect) is an order of magnitude or two slower, so make sure not to mix types of build when comparing speed using it
Comment 6 Yousuf Philips (jay) (retired) 2015-01-16 23:55:10 UTC
Did a callgrind on it and here is the results.

https://drive.google.com/file/d/0B6qJrVIa0SAlRUJRRHg0Qm9QS28/view?usp=sharing
Comment 7 Robinson Tryon (qubit) 2015-12-10 11:04:27 UTC Comment hidden (obsolete)
Comment 8 Tor24_1975314 2016-06-26 14:26:08 UTC
Hello,

with newer versions of LibreOffice the scroll down performance was improved but is still significantly worse compared to MS Office which needs with option "Application.ScreenUpdating = False" only around 1 second for the same test! (Option "Application.ScreenUpdating = False" still does not works in LibreOffice, see also Bug 52603)
This performance difference makes it really hard and frustrating to migrate MS Office macros to LibreOffice to support migration projects and switching from MS Office to LibreOffice.

Needed time to scroll down through 10.000 cells via test macro:

LibreOffice 4.4.5.2: 01:05 [mm:ss]
LibreOffice 4.4.7.2: 01:05 [mm:ss]

LibreOffice 5.0.2.2: 00:21 [mm:ss]
LibreOffice 5.0.3.2: 00:21 [mm:ss] 
LibreOffice 5.0.4.2: 00:21 [mm:ss]
LibreOffice 5.1.0.3: 00:25 [mm:ss]
LibreOffice 5.1.1.3: 00:25 [mm:ss]

(Test system: Windows 7 64-bit, Intel Core2Duo P8700 2.53 Ghz, 4 GB RAM, Java SE Runtime Environment build 1.6.0_24-b07)

Needed time with Excel:
Excel 2010 with "Application.ScreenUpdating = False": ~00:01 [mm:ss]
Excel 2010 with "Application.ScreenUpdating = True" :  00:05 [mm:ss]
Comment 9 Michael Meeks 2016-06-27 08:08:37 UTC
Created attachment 125937 [details]
profile result ...

I guess that ~all of the problem here is the 30k GetOptimalHeightsInColumn calls which turns into 30 million separate GetOptimalHeight calls ... which looks like a madness.

Surely we can iterate down the col/row tree and fetch the data more simply.

It is unclear why UpdateSelectionArea ends up doing a SetOptimalHeight anyway - seems odd to me; ~all of the cost here comes from: the 'SelectionChanged' event handling, which in turn comes from the VBA Range's "Select" method.

Hope that helps anyone wanting to have a poke at this.

ATB.
Comment 10 Buovjaga 2017-05-06 11:40:03 UTC
Terrence suspects bug 107535 is a duplicate of this. I did a callgrind of the steps, but in kcachegrind it does not match attachment 125937 [details].
Michael: what do you think?
My callgrind output is attachment 133102 [details]
Comment 12 Xisco Faulí 2017-09-14 09:35:36 UTC
*** Bug 107535 has been marked as a duplicate of this bug. ***
Comment 13 Terrence Enger 2017-09-15 16:47:38 UTC
The intersection of bibisected ranges reported in c#3 and bug 107535
comment 3, is a single commit, 
<a href="https://cgit.freedesktop.org/libreoffice/core/commit/?id=bcc51fb2ebdf77a1cc089857775fd742085b45b6">bcc51fb2
"add support for in-place style preview"</a>.  I am adding keyword
bisected, and adding Noel Power to cc.

Bug 107535 is marked a duplicate of this one, and from notes I made
before filing bug 107535:
(*) Bug 80064, though resolved INVALID for lack of interest, impressed
    me as a "definite maybe" duplicate when I filed 107535.
(*) Bug 84475, in light of its comment 4, is a good candidate.
(*) Bug 104802 is really close.
In light of these, I am raising the priority here to normal.
Comment 14 Tor24_1975314 2017-11-05 20:14:47 UTC
Tested also with new LibreOffice version 5.4.2 (version: 5.4.2.2) for Windows

LibreOffice 5.4.2.2 - test macro run time: 1 minute and 02 seconds

MS Excel 2013 needs for the same actions <= 1 seconds! (with "Application.ScreenUpdating = False")

Test system: Windows 7 64-bit, Intel Core2Duo P8700 2.53 Ghz, 4 GB RAM
Comment 15 Shane 2017-12-10 21:21:57 UTC
I challenge whether this is truly of low importance and minor significance. Perhaps it is because not too many people use complex macros? But for people using any significant macros this represents a substantial obstacle to moving to LibreOffice.

In my case I am trying to move to LibreOffice from OpenOffice but it has made some of my automation unviable. If MS Office performance figures are so many orders of magnitude better then one can only imagine it could also present a major hurdle to uptake of LibreOffice for some MS Office users too.

If we know that a flood of GetOptimalHeightsInColumn calls seem to be the culprit here is there any kind of workaround to improve scrolling speed in a macro? 

I have tried 

ThisComponent.LockControllers
ThisComponent.addActionLock()

and

ThisComponent.CurrentController.Frame.ContainerWindow.setEnable(False)

and, without timing anything too accurately, none of these seem to have improved performance of my macro at all - it always takes an unacceptably long time in LibreOffice 5.4.3.2 compared to the same code running in OpenOffice 4.1.3 (which isn't fast but is tolerable)...
Comment 16 Michael Meeks 2017-12-11 09:04:38 UTC
Priority-wise, the real question is if any hackers want to spend more time trying to fix it =) there is no real way to encourage volunteers to do that by prioritizing it differently. The best way to affect change here is to eg. add some SAL_DEBUGs in the code there to print out what is going on, and/or try to work out what is going on - why do we adjust the heights here ? do we do that repeatedly ? what rows are being measured ? is there some way of significantly optimizing this by looking at spans instead of individual rows etc. etc. anyone can do that work - if almost any semi-technical person can spend a few days on it they should make progress. Getting a good bisection would also help. Noel's commit description doesn't seem to match the callgrind trace.
Comment 17 QA Administrators 2019-07-12 02:52:11 UTC Comment hidden (obsolete)
Comment 18 Buovjaga 2019-08-11 11:25:37 UTC
Needed time is 04:50 [mm:ss]

Arch Linux 64-bit
Version: 6.4.0.0.alpha0+
Build ID: 37fc9f51a8de11d40632e8cda17ccf1fa4b1f503
CPU threads: 8; OS: Linux 5.2; UI render: default; VCL: gtk3; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Built on 6 August 2019