Forum Discussion

Kevin Dean's avatar
Kevin Dean
Copper Contributor
Sep 06, 2023

Timing function execution using LAMBDA

I've been developing a large library of LAMBDA functions and I found myself in need of some crude performance testing, as many of my functions return large arrays and can take some time to execute. I...
developer
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 07, 2023

Kevin Dean 

Didn't dig this solution. As a comment, CalcTime with lambdas is in samples for AFE on GitHub. It's bit specific but could be adjusted. The only the precision is 10ms since that's the limitation of the desktop app. VBA could give more precise result.

Kevin Dean's avatar
Kevin Dean
Copper Contributor
Sep 07, 2023

SergeiBaklan I wasn't aware of AFE until now. It looks really cool, and likely would have saved me some time in developing my library!

 

I can't find CalcTime, though. I don't see it anywhere in the https://github.com/microsoft/advanced-formula-environment, and there's no "Samples" option in the add-in.

 

Finally, I did try the Evaluate() in VBA, but I found it to be really inconsistent in its results and significantly slower than the LAMBDA, which tells me there's a lot of overhead that's unaccounted for. That may have been my environment (Excel on Mac, which only begrudgingly supports VBA), but given that it doesn't work on mobile or the web, I prefer a LAMBDA-based solution.

 

Do you have a link to CalcTime or know exactly how I can find it?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 07, 2023

    Kevin Dean 

    Sorry, where is no such function directly, it's the sample how to get calculation time. In general AFE sources with examples are here microsoft/advanced-formula-environment: Create, edit, and reuse formulas in Excel (github.com)

    In Lib.md you may find this peace of the code

    // Timing a computation wrapped in a thunk

timer = LAMBDA(thunk,
  LET( time_0, NOW()
     , value, thunk()
     , time_1, NOW()
     , days, time_1 - time_0
     , ms, days * 24 * 60 * 60 * 1000 // milliseconds (resolution 10ms on desktop)
     , pair(round(ms,0),value)
     )
);

    It referenced pair() function in the sample file, all together return calculation time and the resulting value. We may ignore pair() and based on above create more universal function.

    I played with it long ago and forget the details. In day to day activities it's not very practical - 10ms precision is not good enough and do we have 500 ms on the formula or 900ms on its variant - who cares.

    PeterBartholomew1 has more experience with timing, but I guess he uses VBA. Perhaps Peter could share his approach.

Resources