Created attachment 119845 [details] Spreadsheet demonstrating heavy CPU consumption w/STYLE() in conditional formatting I have a spreadsheet using STYLE() in conditional formatting as a form of computed style (see bug 85264). Traditional conditional formatting using fixed conditions and styles will not work well for my broader application; the attached spreadsheet is greatly simplified. When I'm on the first tab (All Results), LibreOffice burns CPU continuously. 5.0.2.2 runs at 100% and LibreOffice feels slow; on 4.2.7, it runs at more like 60-70% and there's no obvious slowdown. If I switch to the second tab (Config), the CPU consumption drops to zero or close to it.
Ubuntu 15.04 x64 LibreOffice 5.0.4.1 (master) Setting to: New Minor - slows down but doesn't prevent high quality work; Medium - regression apparently from 4.2.7 Whiteboard: regression (before 60-70% use, now 100%)
Created attachment 119862 [details] Simpler spreadsheet demonstrating the same problem This one is smaller and even simpler: a single cell with conditional formatting. I've replaced the named ranges in the LOOKUP with hard-coded addresses, too. Three other things I note are: 1) If I try to type into $'All results'.$A$1, it flashes the background color rapidly. 2) If that cell is visible, operations such as clicking on Format in the menu bar are extremely slow, and it's not clear that they would ever return, but if I switch to sheet 2 (Config), the problem goes away. If I scroll so that that cell is *not* visible, however, they act normally. 3) If I scroll so that that cell isn't visible, LibreOffice no longer consumes CPU...until I scroll back such that it is visible.
4.2.7 shows the same effect (consuming a lot of CPU time), just not as severely. This shouldn't be happening at all. I would rate the severity a bit more than minor, because if a cell with that conditional formatting applied is visible, some operations (such as the format menu) apparently cannot be used at all.
In 4.2.7, the second spreadsheet (the "simpler" one) still consumes about 10-15% of a CPU even when completely idle. However, the UI is fully responsive. So yes, I'd call it a regression, but an existing bug behaving even worse rather than a completely new issue. (I'm using 4.2.7, BTW, because the spreadsheet I actually care about has a lot of problems with higher versions, and in 5.0, it crashes if I even switch sheets. I've filed a number of other bugs related to those behaviors.)
(In reply to rlk from comment #0) > Created attachment 119845 [details] > When I'm on the first tab (All Results), LibreOffice burns CPU continuously. > 5.0.2.2 runs at 100% and LibreOffice feels slow; on 4.2.7, it runs at more > like 60-70% and there's no obvious slowdown. This seems to have begun at the below commit. Adding Cc: to Matthew J. Francis ; Could you possibly take a look at this one? Thanks bc0b2085d043ff9bb2d891b55e4a7f45a9dd4bf7 is the first bad commit commit bc0b2085d043ff9bb2d891b55e4a7f45a9dd4bf7 Author: Norbert Thiebaud <nthiebaud@gmail.com> Date: Sun Jul 26 14:49:20 2015 -0700 source 61b1697069c50ff72339d4592add42ab72b03243 source 61b1697069c50ff72339d4592add42ab72b03243 author Matthew J. Francis <mjay.francis@gmail.com> 2015-07-06 00:09:24 (GMT) committer Stephan Bergmann <sbergman@redhat.com> 2015-07-08 14:51:54 (GMT) commit 61b1697069c50ff72339d4592add42ab72b03243 (patch) Reduce the amount of up front work in performing introspection :040000 040000 692410a93685fff579933c82ff7c2504e4c65810 56d6e692bb0a557ea5eda8410452aec6443cdadb M instdir /bibisect-win32-5.1 $ git bisect log # bad: [a2ca6bb70db1ba8f306a617d92070351d9a3a624] source sha:8b5182155b6d35a1be6 4d37136584e30ea6a6ef8 # good: [c1efd324c6ad448ac9edb030dc9738b9e6899e4d] source sha:ab465b90f6c6da5595 393a0ba73f33a1e71a2b65 git bisect start 'a2ca6bb70db1ba8f306a617d92070351d9a3a624' 'c1efd324c6ad448ac9e db030dc9738b9e6899e4d' # bad: [1e602523875e23a7969d8c28276c82311eb2fa74] source sha:5b8e4f3d676eb0a026c e1eb4c1df2ec6e0736cb1 git bisect bad 1e602523875e23a7969d8c28276c82311eb2fa74 # bad: [1e602523875e23a7969d8c28276c82311eb2fa74] source sha:5b8e4f3d676eb0a026c e1eb4c1df2ec6e0736cb1 git bisect bad 1e602523875e23a7969d8c28276c82311eb2fa74 # good: [01eef57435e835a8bc484038bd81d1aa8a762920] source sha:064481201341d92af2 ec1387bb53a9c4b480217f git bisect good 01eef57435e835a8bc484038bd81d1aa8a762920 # bad: [6be5b8f118856a995c7d0c0db843167fbea1555d] source sha:69df701742a9c3946e2 031488c377c18a5ec4de0 git bisect bad 6be5b8f118856a995c7d0c0db843167fbea1555d # bad: [407159f8b3a4d483ad0952ab04de75fc78898852] source sha:71c6ee42d3311b31896 e5a408d8f83f4e5d7726e git bisect bad 407159f8b3a4d483ad0952ab04de75fc78898852 # bad: [05c15e6999ee21f035066da19ea13bac9d1c7e99] source sha:74f9d9808fce14f015d 56a2aaa4ec5616ed7aa3e git bisect bad 05c15e6999ee21f035066da19ea13bac9d1c7e99 # bad: [84e6f10a26c8d7b07ee2cfe851588f39ba21bd38] source sha:f4b189df64cc6f6238a 5bdf00390cd5dd5ac9e89 git bisect bad 84e6f10a26c8d7b07ee2cfe851588f39ba21bd38 # good: [eadbc73a5371ff74c3195ac072c190df917b02cf] source sha:1349c8356429279f6b 6ff6d8fc7a1a51e5c7ee55 git bisect good eadbc73a5371ff74c3195ac072c190df917b02cf # bad: [e2430741fe4593c03bfc45ede3435913b35ad969] source sha:64bc8b45b5c23efc5fe 57585a69aa4263aaf4e83 git bisect bad e2430741fe4593c03bfc45ede3435913b35ad969 # good: [b5026025b18d7dba990482409a8f4846cac63dbb] source sha:4ec7f1d7a82e13532b 07acb5da6bb17cfd550ee2 git bisect good b5026025b18d7dba990482409a8f4846cac63dbb # good: [d266bb906464940b0d921b56fbf5aa2c9bf7bb9d] source sha:ca55180d74d9e6f400 71f453c4b0280ae768e5a0 git bisect good d266bb906464940b0d921b56fbf5aa2c9bf7bb9d # good: [8c52c13c44f6b6232bdffba6a383f336c3707252] source sha:c3c6bf235af32781e8 3d4fed2867a16bfeafa659 git bisect good 8c52c13c44f6b6232bdffba6a383f336c3707252 # bad: [bc0b2085d043ff9bb2d891b55e4a7f45a9dd4bf7] source sha:61b1697069c50ff7233 9d4592add42ab72b03243 git bisect bad bc0b2085d043ff9bb2d891b55e4a7f45a9dd4bf7 # good: [1f62c5a5e84bf22c1e4aec2448ab73a9c0dfd634] source sha:b3e645cde951906d88 3f015d342f85fc0eedb2ec git bisect good 1f62c5a5e84bf22c1e4aec2448ab73a9c0dfd634 # first bad commit: [bc0b2085d043ff9bb2d891b55e4a7f45a9dd4bf7] source sha:61b169 7069c50ff72339d4592add42ab72b03243 # bad: [a2ca6bb70db1ba8f306a617d92070351d9a3a624] source sha:8b5182155b6d35a1be6 4d37136584e30ea6a6ef8 git bisect bad a2ca6bb70db1ba8f306a617d92070351d9a3a624 # bad: [2531e54e73e3d38e3e4d80d32108af70c4a5613c] source sha:d7e7bf139a615f68345 c36572d12219bcf2c2658 git bisect bad 2531e54e73e3d38e3e4d80d32108af70c4a5613c # bad: [e0c10b369a89978bd2dc6f84485c22d594b788a1] source sha:67169b65d0ffb8c44f8 4b483618c285bf17a6b54 git bisect bad e0c10b369a89978bd2dc6f84485c22d594b788a1 # bad: [012e648e42316a7ead891182c0359fddaf460913] source sha:e2281331b1ab4028849 4ab3659d9c66e13d3e905 git bisect bad 012e648e42316a7ead891182c0359fddaf460913 # bad: [16e18cf96c77787729c23a1c9dbfa287d0069c09] source sha:a52ad6b1cca7ef4cd21 386866c1e9b56d027db18 git bisect bad 16e18cf96c77787729c23a1c9dbfa287d0069c09 # bad: [7572cd1d9fba56fb58f5766d3d98d61287cd45fa] source sha:75d0f7262004d93d457 b4b0b499ebf0b23fcda04 git bisect bad 7572cd1d9fba56fb58f5766d3d98d61287cd45fa # bad: [1471d214655e71f1bc894f31915004564b5bed41] source sha:72c11ce76abebdbe88a 7be793dbf690c54b30500 git bisect bad 1471d214655e71f1bc894f31915004564b5bed41 # bad: [904a0c16aec072f969c42f22d73b8aff41ee849d] source sha:9a11e59e5699c5eb085 4355d3dd3848bc895545c git bisect bad 904a0c16aec072f969c42f22d73b8aff41ee849d # bad: [aefa67741cda1a114d84a3cdca9f7ed05f3cd340] source sha:9125549453e3038edda fc3c89bc830a5e40953b4 git bisect bad aefa67741cda1a114d84a3cdca9f7ed05f3cd340 # bad: [4af2c8dae0ac8aa4408dac184572fda40b6a73da] source sha:b6c570aff4c6dc7a469 ed0e2c3dff8ce8f9934b8 git bisect bad 4af2c8dae0ac8aa4408dac184572fda40b6a73da # bad: [1b41e9ed704bd4eb6e7544989a4914830e843a1c] source sha:feab17bc3626b5d1585 d91ce006b4b9105483c1f git bisect bad 1b41e9ed704bd4eb6e7544989a4914830e843a1c # bad: [395f7d694be35fd65273dc1f6dac5d63dcb46867] source sha:26aebe26f706d9fbc9c 113f5c1fdc495578435bf git bisect bad 395f7d694be35fd65273dc1f6dac5d63dcb46867 # bad: [80dcc8399d0f4552b44f3478837f69c4d26c74c5] source sha:394c5057e2c2a10bc09 504646eed1b80e3cb061c git bisect bad 80dcc8399d0f4552b44f3478837f69c4d26c74c5 # bad: [bc0b2085d043ff9bb2d891b55e4a7f45a9dd4bf7] source sha:61b1697069c50ff7233 9d4592add42ab72b03243 git bisect bad bc0b2085d043ff9bb2d891b55e4a7f45a9dd4bf7 # first bad commit: [bc0b2085d043ff9bb2d891b55e4a7f45a9dd4bf7] source sha:61b169 7069c50ff72339d4592add42ab72b03243
Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit]
I'm not yet sure if that is a bug or an user error. STYLE applies a style to the cell while the conditional format wants to apply one as well. Now as STYLE changes the cell we of course need to recalculate the conditional format again. Honestly right now I believe that using STYLE inside of conditional formats is a bad idea and see no reason to fix this problem. I would need to see a valid use case that actually does something that can't be done through adding a conditional format. Currently if I would do anything it would be to make using STYLE in a conditional format an error. I'll remove the regression and perf keyword as I think we don't have a bug around here.
*** Bug 75826 has been marked as a duplicate of this bug. ***
I think that actually _using_ STYLE() inside of conditional formatting is a strange thing to do. What I'm trying to do, though, is as ddescribed in bug 85264: "Request the ability to apply the format returned by a formula. This is in essence like the STYLE() function, except applied via conditional formatting rather than by explicit use of STYLE(). "Use case: applying a style to manually entered data. For example, a formula that returns a named style based on the year of an entered date (perhaps by means of LOOKUP()) would have the style applied by conditional formatting to the entered cell. "The date method of conditional formatting is not appropriate for this case; the basic conditional formatting could be used, but only up to 10 years (or other ranges). It's also less flexible, because the formula to be used for the computed conditional format could actually be a name; editing the value of the name changes the formula, which would allow the computed style to be changed easily everywhere." Conditional formatting will not work for this case because I have more than 10 possible styles to apply. In addition, I'd like the style to be the result of a formula, which I can change on the fly, rather than a purely static list or one of the limited options provided by conditional formatting. What I usually do for cells with formulas is =a1+b2+STYLE(LOOKUP(CURRENT(), FooRanges, FooStyles) where FooRanges and FooStyles might be adjacent columns that look something like this (this is an actual example -- number of meters I've rowed in a session, to be precise): 0 blank 1 slow 1000 default 2000 slow1 4000 200 5000 158 6000 155 7000 1525 8000 151 10000 150 12000 149 15000 148 17000 147 20000 146 21097 145nb 22000 143 23000 fast 24000 fast1 25000 fast2 26000 fast3 27000 fast4 28000 fast5 I'd like to be able to do this for text entry cells, but easily change the breakpoints (which are used elsewhere) without having to change both the lookup table and the conditional formatting (which won't even work in this case due to the number of ranges). Reopening 85264 would be one way of handling this.
Marcus, please see https://bugs.documentfoundation.org/show_bug.cgi?id=75826#c10
So the reason that I think that the bug is actually to allow STYLE in conditional formatting is that conditional formatting functions are expected to be side-effect free. The whole conditional formatting code is more or less based around that idea. Additionally it is absolutely an implementation detail how and when conditional formatting rules are evaluated. Take as an example a conditional format formula like STYLE("style1", 10, "style2") which does not what users would expect as it would constantly apply style1. Even if we would change the code and fix the loop that makes it constantly apply style1 it would immediately need to repaint when we apply style2 and (as part of the implementation detail) evaluate the conditional format formula which would force it to apply style1. IMHO there are good reasons that something like this is not possible in MSO. Our conditional format code is already way too complicated because of some missing restrictions and I think restricting the use of STYLE would be a sane one. I still need to sleep one night and maybe talk to Eike or Kohei about it but at least in my opinion it is the way to go.
(In reply to rlk from comment #9) > > Conditional formatting will not work for this case because I have more than > 10 possible styles to apply. In addition, I'd like the style to be the > result of a formula, which I can change on the fly, rather than a purely > static list or one of the limited options provided by conditional formatting. > The limit on conditions in a conditional format has been removed for quite some time now. You can add unlimited entries to one conditional format.
It's still a lot less convenient than allowing the style to be the result of a formula, which can be a name (so simply editing the name would allow the number of conditions and breakpoints to be changed for all ranges using the conditional formatting) or a lookup (which changing the range does the same thing). For example, I have the name "dr" defined as LOOKUP(CURRENT(),DistanceRanges,DistanceStyles) I can either edit that name, change the values in DistanceRanges/DistanceStyles (which themselves could be the results of formulas), or insert more cells into DistanceRanges and DistanceStyles to increase the number of format conditions. With conditional formatting, I have to edit the conditions for each range using them, and that editing is not a very pleasant prospect. I realize that that's an RFE, and I'm fine with that, but I suspect it's an RFE that would be useful for quite a few cases.
(In reply to rlk from comment #13) > I realize that that's an RFE, and I'm fine with that, but I suspect it's an > RFE that would be useful for quite a few cases. Every user wants to think their corner case is something that will be "useful for quite a few cases." I can confidently say that you're in the vast minority (I'm quite confident to say less than 0.1%) that would ever do something like this. The question then becomes, do we complicate our code even more to make an incredibly corner case scenario "easier" for a vast minority of our users....making it harder for developers in the future to handle the code in the process. If it's already possible to do (which Markus has said how, you just don't like that method) I see no reason to complicate the code more. @Markus - if Kohei and/or Eike agree I suggest we just close the bug as WONTFIX. Else we risk going round and round. I'm not sure that there's anything to gain from that.
I do not consider this (95233) to be a bug (other than perhaps disallowing it entirely). It's a very weird and at best highly ambiguous way of accomplishing what I'm looking for. I would prefer that it be treated as an enhancement as I originally filed it (bug 85264, which was closed duplicate of this one). I'm well aware that enhancement requests have to be prioritized and not all of them will be done; if this one falls out, that's your call as people working on the project. I'm of the opinion that it would be useful to others, but I'm not working on LO so my opinion counts for meh. So closing this, and either reopening 85264 as a low priority enhancement request (so that others who may be interested in it can find it) or closing will not fix would be fine. But 85264 doesn't "work for me".
(no, not closed duplicate, closed "works for me" referring to 85264)
Markus Mohrhard committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=28d0e255e231639c4d79f6dedbe972d6daeae7f0 tdf#94429, tdf#92198, tdf#95233: STYLE in conditional formatting It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I'm still going to either remove STYLE in conditional format formulas or limit it to the 1 parameter version as part of 5.3. This is an ugly hack for 5.2.
I'd like to know what you are going to do for 5.3 -- I don't want to do something in 5.2 that will be immediately deprecated in 5.3.
(In reply to rlk from comment #19) > I'd like to know what you are going to do for 5.3 -- I don't want to do > something in 5.2 that will be immediately deprecated in 5.3. He explained why. He gave a quick fix with crappy code to appease you but we're not going to keep it around so he's going to remove one of two things to make the code nicer. If you want to know more, I suggest learning some c++ and looking at the code. Markus is one of our most dedicated developers and spending a ton of time explaining everything he does makes no sense (just not efficient use of his time). Furthermore, he's explained repetitively throughout this bug why he doesn't think the current solution is functional. As far as you not wanting something....then don't do it. That's pretty straight forward.
If it's going to be removed altogether, then I won't try to use it. Apologies if that wasn't clear, and for the time that was lost over this.
(In reply to rlk from comment #21) > If it's going to be removed altogether, then I won't try to use it. > Apologies if that wasn't clear, and for the time that was lost over this. Absolutely no need to apologize :) On our side (contributors generally) we just try to inform and teach users how the project generally works. Imagine a few thousand bug reports with lots of users "yelling" (symbolically) that their bug is so super important and demanding answers about why choices are made....it can become draining and counter productive :) @Markus: Just to clarify (and to repeat rik's comment), will this feature be deprecated completely in 5.3? Thanks all for keeping the conversation pleasant and not making it personal or counter-productive as some other threads have gone
I'm a long time SW engineer and project manager myself, so I'm all too familiar with this issue. I know that bugs and RFE's need to be prioritized, and while I think the capability I described in bug 85264 would be of broader use, I'm well aware that this isn't my call to make.
(In reply to Joel Madero from comment #22) > (In reply to rlk from comment #21) > > If it's going to be removed altogether, then I won't try to use it. > > Apologies if that wasn't clear, and for the time that was lost over this. > > > @Markus: Just to clarify (and to repeat rik's comment), will this feature be > deprecated completely in 5.3? > Honestly I have no idea yet. Basically all calc devs currently think that STYLE together with conditional formats is just a bad idea and that it can be broken any time. Using STYLE inside of conditional format functions makes quite a few assumptions about the internal evaluation order of the formulas. Also all use cases that I have seen can be implemented with the current conditional format code. The number of styles is finite so there is no use case that can not be expressed with a static number of conditional formats.
Again, please see what I want to do in bug 85264 (or comment 9 of this bug). Yes, in principle I can do it with static ranges. In practice, if I have 15 or 20 ranges and I want to tweak them (perhaps as the result of a formula) or add in some other ranges, it's going to be extremely painful to do. I don't like the idea of using STYLE() in conditional formatting either. It really is a hack to work around the fact that conditional formatting doesn't have a mode whereby I can say "the style will be the result of this formula", which is what bug 85264 is all about. I was advised in that bug to use STYLE() in a condition, which I tried, leading to my filing this bug. But I'd really like to see this done as an RFE where the name of the condition can be the result of a formula, _analogous_ to what STYLE() does inside formulas. In other words, Conditional Formatting currently allows formatting by a fixed set of conditions, a parameterized color scale, a or an icon set (or a few others). I'm really requesting a new type of conditional formatting, which might be named "By Formula".
(In reply to rlk from comment #25) > Again, please see what I want to do in bug 85264 (or comment 9 of this bug). > Yes, in principle I can do it with static ranges. In practice, if I have 15 > or 20 ranges and I want to tweak them (perhaps as the result of a formula) > or add in some other ranges, it's going to be extremely painful to do. That it is not the most convenient way is not an argument convincing me to keep supporting STYLE. > > I don't like the idea of using STYLE() in conditional formatting either. It > really is a hack to work around the fact that conditional formatting doesn't > have a mode whereby I can say "the style will be the result of this > formula", which is what bug 85264 is all about. I was advised in that bug > to use STYLE() in a condition, which I tried, leading to my filing this bug. > But I'd really like to see this done as an RFE where the name of the > condition can be the result of a formula, _analogous_ to what STYLE() does > inside formulas. > > In other words, Conditional Formatting currently allows formatting by a > fixed set of conditions, a parameterized color scale, a or an icon set (or a > few others). I'm really requesting a new type of conditional formatting, > which might be named "By Formula". That will not be implemented. Our current conditional format stuff is already way too complicated (and supports many things that Excel for example prevents) which make it slow and nearly impossible to not cause regressions in some cases. Making this even more complicated with another place that would support formulas is just asking for even more trouble. So if you want to know if STYLE will still be supported in 5.3 I would not count on it.