Hi Oliver, On Monday, 2007-02-19 17:26:48 +0100, Oliver Brinzing wrote:
> > [... broadcaster performance with bulk of identical ranges ...] > > this could be true, cause on another cell range (with about 15.000 cells), > i replaced all formulas (written in oo basic) through a vlookup formula ... > but this lead to a performance desaster ... Actually the cause may be also be that in your SVERWEIS you pass the 4th argument as 0, which causes the lookup to iterate over all records. If you sorted the data range and pass the 4th argument as 1 instead, the lookup would use binary search, which is ways faster. Note the difference between those two forms though that the sorted lookup will always return a result, see online-help. > another two performance question: > > is it better: > > - - to use NamedRanges instead of direct cell addressing inside formulas ? > > CODERANGE = Code.$B$4:$F$3492 > =WENN(ODER(ISTLEER(E6);E6="");"";SVERWEIS(E6;CODERANGE;2;0)) No, that doesn't change anything. The interpreter code generated is almost identical and the listener is setup the same. > - - to split calculations, e.g. > > from > =WENN(ODER(ISTLEER(E6);E6="");"";SVERWEIS(E6;Code.$B$4:$F$3492;2;0)) > =WENN(ODER(ISTLEER(E6);E6="");"";SVERWEIS(E7;Code.$B$4:$F$3492;4;0)) > to > F6 =ODER(ISTLEER(E6);E6="") > =WENN(ISTWAHR(F6);"";SVERWEIS(E6;Code.$B$4:$F$3492;2;0)) > =WENN(ISTWAHR(F6);"";SVERWEIS(E7;Code.$B$4:$F$3492;4;0)) That may change a little bit, but not significantly. Btw, you may omit the ISTWAHR call, a simple =WENN(F6;...) does the same. The change may be more noticeable though if you have several thousand identical expressions of those ODER(ISTLEER(E6);E6=""). Depending on your formulas it may help more to have an interim row of SVERWEIS results if you have identical SVERWEIS lookups. > i noticed that this will increase the file size ... In optimizations speed most times is orthogonal to size ... _If_ the broadcaster/listener performance is really the bottleneck (you may find out by turning off automatic recalculations, if it's still slow probably that is the cause) it _may_ help to use the OFFSET function instead of directly specifying the data range, e.g. SVERWEIS(E6;OFFSET(Code.$B$4;0;0;5;3489);...), which takes advantage of some implementation detail that OFFSET works differently. However, in that case _all_ formulas containing an OFFSET function are recalculated _each_ time the document is changed. Depending on your spreadsheet that may still be ways faster though, and switching off automatic recalculation and doing it manually with F9 will help. Hope this helps. Eike -- OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer. OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS Please don't send personal mail to this [EMAIL PROTECTED] account, which I use for mailing lists only and don't read from outside Sun. Thanks. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
