Created attachment 98415 [details]
Scroll Down Performance - Test Macro
I noticed a substantial performance deterioration by scroll through cells via macro in LibreOffice Calc since version 4.2.0 (up to 18.104.22.168 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,
LibreOffice 22.214.171.124 00:18 [mm:ss]
LibreOffice 126.96.36.199: 00:15 [mm:ss]
LibreOffice 188.8.131.52: 00:15 [mm:ss]
LibreOffice 184.108.40.206: 01:40 [mm:ss] (!!!)
LibreOffice 220.127.116.11: 01:07 [mm:ss] (!!)
LibreOffice 18.104.22.168: 01:08 [mm:ss] (!!)
LibreOffice 22.214.171.124: 01:07 [mm:ss] (!!)
LibreOffice 126.96.36.199: 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]
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:
A xls file with a macro for the performance test is added as attachment!
Excel VBA macro code for the test:
' 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
Application.ScreenUpdating = False
start_time = Time()
For i = 1 To 10000
' do something or check cell content
stop_time = Time()
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"))
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
That was a nightmare to bibisect - anyways
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
Author: Bjoern Michaelsen <email@example.com>
Date: Fri Oct 18 04:23:31 2013 +0000
Author: Noel Power <firstname.lastname@example.org>
AuthorDate: Thu Aug 29 17:15:33 2013 +0100
Commit: Noel Power <email@example.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
*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
: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
Scrolling with the mouse wheel got markedly slower between 4.1.6 and 4.3 (don't know exactly where (Linux 64 bit).
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 188.8.131.52, 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
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
Did a callgrind on it and here is the results.
Migrating Whiteboard tags to Keywords: (perf, bibisected)
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 184.108.40.206: 01:05 [mm:ss]
LibreOffice 220.127.116.11: 01:05 [mm:ss]
LibreOffice 18.104.22.168: 00:21 [mm:ss]
LibreOffice 22.214.171.124: 00:21 [mm:ss]
LibreOffice 126.96.36.199: 00:21 [mm:ss]
LibreOffice 188.8.131.52: 00:25 [mm:ss]
LibreOffice 184.108.40.206: 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]
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.
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]
Regression introduced in range https://cgit.freedesktop.org/libreoffice/core/log/?qt=range&q=511354504cfc2c8f002752775d5bb336b01bd6ab..bcc51fb2ebdf77a1cc089857775fd742085b45b6
*** Bug 107535 has been marked as a duplicate of this bug. ***
The intersection of bibisected ranges reported in c#3 and bug 107535
comment 3, is a single commit,
"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.
Tested also with new LibreOffice version 5.4.2 (version: 220.127.116.11) for Windows
LibreOffice 18.104.22.168 - 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
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
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 22.214.171.124 compared to the same code running in OpenOffice 4.1.3 (which isn't fast but is tolerable)...
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.
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.
Please DO NOT
Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword
Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!
Needed time is 04:50 [mm:ss]
Arch Linux 64-bit
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
Built on 6 August 2019