Created attachment 103478 [details] The file original in ODF format and exported to OOXML Problem description: I create many files with a template. Fill the data with macros. But now, when open the file, if the file is in local hard disk takes 5 minutes to open. If the file is in a shared folder takes 30 minutes or crash LibO. The problem is with very files. Now, export the file to OOXML format and open with MS Excel. The file open in 5 seconds, in hard disk or net. Steps to reproduce: 1. Download the files in ODF format 2. Open with LibreOffice and measure the time 3. Download the file in XLSX format 4. Open with MS Excel and measure the time Current behavior: Long time to open in LibreOffice. Expected behavior: The some time to open in LibreOffice and Ms Excel. Operating System: Windows 7 Version: 4.2.5.2 release
very slow loading of both the .ods and .xlsx version of that file. tested under Win7x64 using LibO 4.2.5.2
Also slow in 4.1.6.2
Slowness is due to the massive amount (>1000) of conditional formats, most on sheets FT-ING-PR-02 and FT-ING-PR-03, many of them even applied to identical ranges. Looks like a result of heavy copy&paste actions, or the macro you mentioned creates them. All these conditionals also extremely slow down scrolling around in the document once it was loaded. @Markus: Shouldn't a conditional style when pasted be merged with its neighbors if identical? Can we somehow detect and correct this mess during document load? @Eduardo: You could unprotect the sheets and invoke Format->Conditional->Manage and manually remove all the single formats and extend one of them to cover the needed ranges. It's quite a tedious task though.
(In reply to comment #3) > Slowness is due to the massive amount (>1000) of conditional formats, most > on sheets FT-ING-PR-02 and FT-ING-PR-03, many of them even applied to > identical ranges. Looks like a result of heavy copy&paste actions, or the > macro you mentioned creates them. All these conditionals also extremely slow > down scrolling around in the document once it was loaded. > > @Markus: > Shouldn't a conditional style when pasted be merged with its neighbors if > identical? Can we somehow detect and correct this mess during document load? > > @Eduardo: > You could unprotect the sheets and invoke Format->Conditional->Manage and > manually remove all the single formats and extend one of them to cover the > needed ranges. It's quite a tedious task though. The password to unprotect is 123456. The macro create a conditional format when add a row. Never know how many rows will be add. If you say the roblem is conditional format, I try to modify the macro to create only uone range of conditional format. I report the changes.
(In reply to comment #3) > Slowness is due to the massive amount (>1000) of conditional formats, most > on sheets FT-ING-PR-02 and FT-ING-PR-03, many of them even applied to > identical ranges. Looks like a result of heavy copy&paste actions, or the > macro you mentioned creates them. All these conditionals also extremely slow > down scrolling around in the document once it was loaded. > > @Markus: > Shouldn't a conditional style when pasted be merged with its neighbors if > identical? Can we somehow detect and correct this mess during document load? > We already do that if a document is imported and does not contain the new conditional format entries. However it is much more complicated during copy&paste as we have now conditional formats that rely on the range. Take the simple example of a conditional format that just formats one column with a color scale. Now copying that column to the next one has two solutions for what we could do with the conditional formatting: merge and create ahuge one spanning two columns or create a second one that just spans this new column. There will always be cases where one or the other solution are wrong. Therefore we have now the manage conditional formatting dialog which helps the user to change the formatting or range if it is not what he expected. Sadly I don't see a perfect solution here. Sadly the UNO API does not reflect all the possibilities (I have to support the old one as it is published) and does not allow to modify the range of a conditional formatting currently.
** Please read this message in its entirety before responding ** 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 on a currently supported version of LibreOffice (5.0.0.5 or later) https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System 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) 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: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-09-03
To compare for me (one-time non-scientific measurement): 4.1: 6:40 i.e. 400 secs 6.2+: 2:50 i.e. 170 seconds Making the title more precise.
Created attachment 145108 [details] Callgrind output from master Callgrind from opening the XLSX in case it might help Arch Linux 64-bit Version: 6.2.0.0.alpha0+ Build ID: 0ffa7a733d834647dfd59b864c52a015028822b6 CPU threads: 8; OS: Linux 4.18; UI render: default; VCL: gtk3_kde5; Locale: fi-FI (fi_FI.UTF-8); Calc: threaded Built on September 21st 2018
ODS file takes real 3m20,457s user 3m19,592s sys 0m0,441s in Version: 5.2.0.0.alpha1+ Build ID: 5b168b3fa568e48e795234dc5fa454bf24c9805e CPU Threads: 4; OS Version: Linux 4.15; UI Render: default; Locale: ca-ES (ca_ES.UTF-8) @Noel, seeing your recent optimizations, I thought you might be interested in this issue...
(In reply to Xisco Faulí from comment #9) > ODS file takes > > real 3m20,457s > user 3m19,592s > sys 0m0,441s > > in > > Version: 5.2.0.0.alpha1+ > Build ID: 5b168b3fa568e48e795234dc5fa454bf24c9805e > CPU Threads: 4; OS Version: Linux 4.15; UI Render: default; > Locale: ca-ES (ca_ES.UTF-8) > > @Noel, seeing your recent optimizations, I thought you might be interested > in this issue... I meant Version: 6.3.0.0.alpha0+ Build ID: 3b518953a8141b0d5043c2f3996a92956fdc3a47 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US Calc: threaded
Noel Grandin committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/ec7ba61a6164c805f5a71b077715b7e1521a2d62%5E%21 simplify SfxPoolItemArray_Impl (tdf#81765 related) It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Noel Grandin committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/bcb0c9b4bee1d943d9c60f9d4512dba901f85f54%5E%21 flatten SfxItemPool_Impl (tdf#81765 related) It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Did a new build just now and the XLSX opening is significantly improved. Opening XLSX 6.3.0: real 1m33,838s user 1m25,919s sys 0m2,076s 6.2.2: real 2m1,142s user 2m0,547s sys 0m0,272s Opening ODS: 6.3.0: real 1m34,639s user 1m34,088s sys 0m0,285s 6.2.2: real 1m36,199s user 1m35,475s sys 0m0,286s
buovjava, that was just laying the groundwork, haven't landed the real improvement yet :-)
Noel Grandin committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/003d11f410b7e515981b3efbd65d936d94d87121%5E%21 tdf#81765 slow loading of .ods with >1000 of conditional formats It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I've got a couple of small tweaks still coming for this, but with the time now under 15s for me, I consider this closed
(In reply to Noel Grandin from comment #16) > I've got a couple of small tweaks still coming for this, but with the time > now under 15s for me, I consider this closed Nice, many thanks Noel!
Noel Grandin committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/c757117afb398277a46e79ba22066c5bbf2c9f72%5E%21 tdf#81765 slow loading of .ods with >1000 of conditional formats, part 2 It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Noel Grandin committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/aae39e5dbfde76726e3ad6a2e99874490515da58%5E%21 tdf#81765 slow loading of .ods with >1000 of conditional formats, part 3 It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Xisco Faulí from comment #9) > ODS file takes > > real 3m20,457s > user 3m19,592s > sys 0m0,441s now it takes real 0m28,094s user 0m27,225s sys 0m0,398s in Version: 6.3.0.0.alpha0+ Build ID: e913727c7ee3af0bb4031c6829abfb3373306492 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US Calc: threaded the XLSX takes real 0m29,372s user 0m29,097s sys 0m0,380s @Noel, thanks a lot for the huge improvements here!!
*** Bug 61484 has been marked as a duplicate of this bug. ***
*** Bug 115488 has been marked as a duplicate of this bug. ***
Checked again in Version: 6.3.0.0.alpha0+ Build ID: 0a04150b6eefb5feb7ecefaa5cd63dbac8c1574f CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US Calc: threaded and the time measure is even better for me ( tried a few times ) real 0m22,981s user 0m22,592s sys 0m0,316s
Opening the XLS with today's daily build in Windows takes ~20s for me, nice improvement! (In reply to Commit Notification from comment #15) > https://git.libreoffice.org/core/+/ > 003d11f410b7e515981b3efbd65d936d94d87121%5E%21 > > tdf#81765 slow loading of .ods with >1000 of conditional formats Let's mention the follow-up fix as well: https://cgit.freedesktop.org/libreoffice/core/commit/?id=1e3d0a57689006cd7244481958025177c01e4d09 author Noel Grandin <noel.grandin@collabora.co.uk> 2019-04-23 11:23:36 +0200 committer Noel Grandin <noel.grandin@collabora.co.uk> 2019-04-23 19:56:53 +0200 fix bad comparison in StrCmp/StrLess
Noel Grandin committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/943a2f44bdc67cc795e553fd98693dba501ad717%5E%21 tdf#81765 slow loading of .ods with >1000 of conditional formats, part 4 It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Noel Grandin committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/585e0ac43b9bd8a2f714903034e435c84ae3fc96%5E%21 revert part of "tdf#81765 slow loading of .ods" It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Reopen this because I had to revert the major speedup part
Attaching the IRC discussion of this problem May 02 14:10:15 <noelgrandin> so of the items we stick into pools we modify all over the place May 02 14:10:20 <noelgrandin> s/so/some May 02 14:10:39 <mst___> noelgrandin, that sounds very wrong May 02 14:11:44 <noelgrandin> mst___, ScPatternAttr May 02 14:12:17 <noelgrandin> and of course there are the SvxFieldItem things that editeng likes to modify May 02 14:12:29 * mst___ is surprised that such horrible abuse of interface contract is in sc, not sw... May 02 14:13:12 <noelgrandin> I think its a case of (a)busing the de-duplicate functionality they have May 02 14:13:25 <erAck> noelgrandin: where does that modify items in the pool? May 02 14:14:27 <noelgrandin> erAck, ScPatternAttr ends up in a pool, and ScPatternAttr extends SfxSetItem, and lots of places (1500+ lines) call GetItemSet() on it and modify stuff inside it, and it's operator== is dependant on it's contents May 02 14:17:00 <noelgrandin> which was not a problem when the code would always scan all the ScPatternAttr's in a pool to find a match, but as soon as I tried to make the scanning smarter... May 02 14:19:21 * erAck doesn't quite understand.. and doesn't have time to spend hours to dig into it now May 02 14:21:11 <noelgrandin> erAck, pool contain items, and when we add an item, we attempt to match against an existing item (de-duplicate for memory saving I guess). that process of matching becomes O(n^2) when loading certain documents. I made the scanning faster by sorting the list. But that sorting fails because various code likes to modify the ScPatternAttr that are inside a pool, resulting in an unsorted list inside the pool May 02 14:23:44 <mmeeks> noelgrandin: perhaps good to have some stack-traces from a hardware watchpoint of the state being changed (?) =) May 02 14:24:59 <noelgrandin> mmeeks, it gets changed from tons of places, I have a 1500+ line patch where I tried to move the changing inside the class so I could trigger a manual re-sort of the cached items in the pool. May 02 14:25:02 <erAck> noelgrandin: so what you call a pool is actually the SfxItemSet? May 02 14:25:20 <noelgrandin> erAck, no, the SfxItemSet is itself also an item in a pool May 02 14:25:41 <noelgrandin> erAck, sorry, that should be SfxSetItem (which contains an SfxItemSet) May 02 14:25:53 <noelgrandin> all rather confusing May 02 14:27:10 <noelgrandin> anyhow, just a load-time perf improvement, backing it out no big deal
back to real 3m44,168s user 3m36,107s sys 0m4,511s in Version: 6.3.0.0.alpha0+ Build ID: f20810f7829d9f3b7167df316e1303810b746366 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US Calc: threaded
*** This bug has been marked as a duplicate of bug 135215 ***
*** Bug 135215 has been marked as a duplicate of this bug. ***
Luboš Luňák committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/0d402556af53790adb06380b4b04ea421d14d09e fix usage of std::lower_bound() in SfxItemPool (tdf#81765) It will be available in 7.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
This now needs 15-20s here. With Noel's reverted change it could get somewhere to 12s, but somebody would need to figure out comment #28 first. Let's call this good enough.
XLSX file takes real 0m13,726s user 0m13,826s sys 0m0,211s in Version: 7.4.0.0.alpha0+ / LibreOffice Community Build ID: 0723b41bed9bb4ad50d2993744a60177966d1a21 CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: gtk3 Locale: es-ES (es_ES.UTF-8); UI: en-US Calc: threaded while the ODS takes real 0m24,153s user 0m24,177s sys 0m0,189s @Luboš Luňák, thanks for fixing this issue!!
*** Bug 122040 has been marked as a duplicate of this bug. ***