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
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 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
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 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
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. https://drive.google.com/file/d/0B6qJrVIa0SAlRUJRRHg0Qm9QS28/view?usp=sharing
Migrating Whiteboard tags to Keywords: (perf, bibisected)
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]
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.
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, <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.
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
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)...
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.
Dear Tor24_1975314, 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! Warm Regards, QA Team MassPing-UntouchedBug
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
Needed Time: 00:00:11 Closing as worksforme Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 9c930c4f3109d123c0831d0fcecf9c8b32e5bbc7 CPU threads: 2; OS: Windows 10.0 Build 19042; UI render: default; VCL: win Locale: fi-FI (fi_FI); UI: en-US Calc: threaded
this is fine with X11 Version: 7.2.0.0.alpha0+ / LibreOffice Community Build ID: dff586735b6618d9b011823594a33287d8f7f223 CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: x11 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded Start: 10:21:02 PM Stop: 22:21:30 Needed Time: 00:00:28 but it takes forever with GTK3, so it might be, it's slow when using accessibility
Created attachment 192032 [details] hotspot flamegraph with gtk3 debug build (In reply to Xisco Faulí from comment #20) > this is fine with X11 > > Version: 7.2.0.0.alpha0+ / LibreOffice Community > Build ID: dff586735b6618d9b011823594a33287d8f7f223 > CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: x11 > Locale: en-US (en_US.UTF-8); UI: en-US > Calc: threaded > > Start: 10:21:02 PM > Stop: 22:21:30 > > Needed Time: 00:00:28 > > but it takes forever with GTK3, so it might be, it's slow when using > accessibility With current bibisect repo (i.e. a release build), this takes ~12s with gen, ~23s with gtk3 for the same build, so it seems to be "only" a factor of 2 slower now. Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 96493091a80fb01c33ea9153b737fdc554de61f0 CPU threads: 12; OS: Linux 6.5; UI render: default; VCL: x11 Locale: en-GB (en_GB.UTF-8); UI: en-US Calc: threaded Attached is a hotspot flamegraph for gtk3 with a master debug build (after letting the macro run for a bit more than a minute), which doesn't seem to indicate that a11y is a main culprit, but rather the gtk clipboard handling (note VclGtkClipboard::setContents). (Debug builds aren't ideal for profiling of course, but this may still give an idea.) Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 3e9a700091872480dd085f0928d1d30b7d74cfd7 CPU threads: 12; OS: Linux 6.5; UI render: default; VCL: gtk3 Locale: en-US (en_GB.UTF-8); UI: en-US Calc: threaded
It seems amazing that we do so much clipboard work on extending a selection, rather than deferring it to a <copy> action; even if that is necessary, deferring that to idle would be extremely helpful I expect.