GNOME Bugzilla – Bug 326595
Gnumeric calculates both branches of "if"
Last modified: 2009-01-20 16:20:32 UTC
Hi, It seems that gnumeric is doing extra work: if (C5 = "yes"; very_complicated_calculation; "") when I change C5 value from "yes" to "no" or from "no" to "foo", gnumeric uses almost half the cpu that will use for the very_complicated_calculation. Demo file attached. Note 1: Exporting the file to .xls and loading it with OpenOffice.org 2, when using a "no" value, no cpu is used. Note 2: Calculation under OpenOffice.org is faster.
Created attachment 57152 [details] Example ilustrating the bug
This is true: "if" evaluates both the "true" and the "false" branch. This has to do with getting the semantics of implicit iteration right. If this was a visible problem for a real (== non-test) spreadsheet we would be more concerned than we currently are. There is, incidentally, another reason why Gnumeric takes longer on this sheet: Our "product" function takes a good deal of care avoiding overflow and underflow in intermedia results. That takes time, but yields better results in general.
I was trying to solve my perfomance problem, because I use sumproduct a lot (2 or 3 seconds freeze every time I modify a cell that causes recalculations). This is, don't do the sumproduct until I need some statistics. I tried using the "if" solution and found that it also uses lots of cpu. The example is just to make it evident. I haven't tried any older gnumeric versions though.
Setting os=all, and version=cvs. There is nothing Linux-specific here and the version it will be fixed for is cvs HEAD. We will not do it for the stable series. It sounds like your immediate problem could be solved by setting manual recalculation.
Sorry, refreshing the page kept version=cvs and os=linux. I need automatic recalculation in the sheet. Thanks!
Bringing this back onto the radar. To solve this problem, we need to be able to tell (statically) if a given call to "if" might require implicit iteration. That, in turn, means that we need to tell if a given sub-expression can return an array or a cell range. That means we need that ability per function. I think a flag with possible values "maybe" or "no" should be enough. Calls to g_warning/g_error can be added to check the flag against run-time results. Our test suite ought to test a good part of this. If we have that information and look at a given call to IF where none of the up to three arguments can return array/range, then that call can be changed to a non-strict version of IF.
Created attachment 126598 [details] [review] Preliminary patch Brutally simple way of fixing this. Needs a bit of work, though: the "if2" function should not be visible from the outside.
Created attachment 126603 [details] [review] Even simpler patch
A variant of the last patch has been committed. This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.