storing a function in a LET variable outside recursion is ~2x slower than calling it directly. Why?
Two versions, only difference is whether the operator is resolved via a LET binding once before recursion, or called hardcoded directly inside it.
This is a toy example that calculates factorial or sum of a given number, but the same pattern shows up in real recursive LAMBDAs.
Version A : operator stored in LET, outside recursion:
FACT_OR_SUM= LAMBDA(n, [mode], LET( op, IF(mode, SUM, PRODUCT), me, LAMBDA(me, k, IF(k <= 1, 1, op(k, me(me, k - 1))) ), me(me, n) ) ) =BENCHMARK(LAMBDA(FACT_OR_SUM(170,0)),5000)
Version B: operator hardcoded directly:
Hrd_coded_product= LAMBDA(n, LET( me, LAMBDA(me, k, IF(k <= 1, 1, PRODUCT(k, me(me, k - 1))) ), me(me, n) ) ) =BENCHMARK(LAMBDA(Hrd_coded_product(170,0)),5000)
Testing method:
BENCHMARK = LAMBDA(Func, [iterations], [time_unit], LET( iterations, IF(ISOMITTED(iterations), 1, iterations), start_time, NOW(), loop_result, REDUCE(0, SEQUENCE(iterations), LAMBDA(acc, i,Func())), total_ms, (NOW() - start_time) * 86400000, avg, total_ms / iterations, IF(time_unit, "avg: " & TEXT(avg / 1000, "0.000") & "s | total: " & TEXT(total_ms / 1000, "0.000") & "s", "avg: " & TEXT(avg, "0.00") & "ms | total: " & TEXT(total_ms, "0") & "ms" ) ) ); op is bound outside me, so you'd expect it to be resolved once. But benchmarking shows Version A is nearly 2x slower than Version B.
Question: Isn't Excel supposed to calculate op once? Is this a known limitation, or is something else going on? Is there a workaround?
[link] [comments]
Want to read more?
Check out the full article on the original site