Newton Excel Bach, not (just) an Excel Blog

Update 3rd April 2022: Following the comment from Larry Schuster I have modified the code so that the factorial0 function actually uses cache as intended, rather than lru_cache. I also added code to clear the cache after each timer run, because the uncleared cache was giving misleading results, and added a timer function to call each of the four factorial routines from within Python, rather than calling them as separate UDFs from Excel. The example timer results have all been updated for the new code.

Functools is a built in Python module “for higher-order functions: functions that act on or return other functions”. Of the collection of functions, the two that seemed the most obviously useful for my purposes were cache and lru_cach:

@functools.cache(user_function)
Simple lightweight unbounded function cache. Sometimes called “memoize”.
Returns the same as lru_cache(maxsize=None), creating a thin wrapper around a dictionary lookup for the function arguments. Because it never needs to evict old values, this is smaller and faster than lru_cache() with a size limit.

@functools.lru_cache(user_function)
@functools.lru_cache(maxsize=128, typed=False)
Decorator to wrap a function with a memoizing callable that saves up to the maxsize most recent calls. It can save time when an expensive or I/O bound function is periodically called with the same arguments.

As a simple example, I have used a recursive function to find the factorial of the passed argument, with alternative versions with the cache and lru_cache decorators, and also the Numba just in time compiler decorator:

def factorial(n):
    return 1 if n <= 1 else n * factorial(n - 1)

@cache
def factorial0(n):
    return 1 if n <= 1 else n * factorial0(n - 1)

@lru_cache()
def factorial1(n):
    return 1 if n <= 1 else n * factorial1(n - 1)

@njit(cache = True)
def factorial2(n):
    return 1 if n <= 1 else n * factorial2(n - 1)

These functions have been called from Excel using pyxll, via timer functions allowing a specified number of repetitions, and returning the factorial result and the execution time for each function. One timer function called a specified factorial function, and was entered separately in Excel for each of the factorial functions. The other called all four factorial functions from within Python.

For 1 repetition with a low input number the cache and Numba functions were all slower than plain Python when called as UDF’s and slightly faster when called from Python:

Increasing the input value to 100 made the cache functions relatively slower, since the cache was updated at each step of the factorial calculation, but never actually used. The Numba function was faster than plain Python, especially when called from within Python, with the speed improvement being due to compilation of the code, rather than use of the cache.

With 10,000 repetitions and input value of 100 the cache functions are over 100 times faster than plain Python, with little difference in the time when called from Excel or Python. The Numba function was only 26 to 27 times faster than plain Python:

In summary the cache functions were substantially faster than plain Python for cases with a high number of repetitions of the factorial function, and for this example were about 4 times faster than the Numba function for 10,000 iterations. When the function was only called once the cache functions were significantly slower.

Examples with more practical applications will be examined in later posts.