Sequential Async UDF (re)-calculation delay

59 views
Skip to first unread message

Kenneth Gabriel Birkedahl Fabricius

unread,
Nov 3, 2022, 5:24:55 AM11/3/22
to Excel-DNA
Hi.


First of all, thank you very much for a very impressive framework. I have convinced my organization, that Excel-DNA is the way to go, and hence they have signed up for a sponsorship.

I have defined a range of async UDFs, which I need for performing requests against a WebAPI, which I control myself.

The functions work perfectly on their own, however when I try to chain them together as calls dependent on each other, I get a sequential recalculation.

I have made the following observations:
  1. There is a delay between each of the sequential calculations of about 1,3 seconds (not the first dependant one, which I expect is due to the initial calculation being run instantly)
    1. Is this some delay, that you have any control over or do you know, if it is by design in the Excel calculation engine?
    2. The delay disappears whenever I comment out any "long-running" awaits (i.e. calls, which are not expected to return immediately). Also there is no delay for in-sync functions, which uses  f.x. Thread.Sleep().
  2. Results of functions, which end up with identical inputs return immediately. This means that calls further down the chain may end up showing results before they are actually calculated before finally being recalculated, when the dependencies have be resolved. In the attached example, it is problematic, since the results are time dependent.
    1. Are you aware of any form of input caching in either Excel-DNA or in the Excel calculation engine, which avoid calculation of inputs? 
Please find attached a minimal working example of my Add-In and an Excel workbook illustrating the different cases. Please also let me know, if you need any more information, or if any of the above is unclear.

Best regards,
Kenneth
Async.Function.Example.zip

Govert van Drimmelen

unread,
Nov 3, 2022, 5:52:58 PM11/3/22
to exce...@googlegroups.com

Hi Kenneth,

 

If you control the web API, my general advice would be to:

  • Change from the automatic wrappers that the Excel-DNA Registration library gives you, to your own wrapper implementation.
  • Make batch calls to the back end, using the ‘AsyncBatch’ approach from here: Samples/AsyncBatchSample.cs at master · Excel-DNA/Samples (github.com)
  • Also set up a local cache for the calls, so that you can skip back-end calls when possible.

 

The Excel-DNA async functions are implemented using Excel’s RTD mechanism.

It’s a rather complicated story how this all fits together, and your questions about timing and the results are returned are intricately related to this implementation.

I give a bit of an overview here, but you might like to set up a support chat with me to go through your case in more detail.

 

For this we create a wrapper UDF that

  1.  Sets up some “topic information” to identify the call - typically the UDF name and the parameter values
  2. Records the topic and the “Task” that will be able to provide the result for that topic
  3. Checks whether we have received the value for the topic
  4. If we do not have a value for the topic, calls Excel’s RTD function with the topic information
    1. Excel will then set up a call to the Excel-DNA RTD server, providing the topic information, and returning #N/A to the wrapper function
    2. Excel associates the topic information with the calling cell.
    3. The RTD server will the asynchronously call the Task associated with the topic to get the result
    4. When the result returns, the RTD server notifies Excel that there is new data, which Excel fetches
    5. Excel then recalculates the cells according to the updated topics
  5. If we do have a value for the topic, we return the value from the wrapper function
    1. Excel notices that the calculation for the cell has not called the RTD function again, and clears the topic
    2. We clear the information we have kept for the topic.

 

Notice that the function is called (at least) twice, and the mechanism depends on the fact that both calls have the same “topic information”.

 

The mechanism depends on the fact that a function call depends only on the topic information. So if different cells call the same function, with the same arguments, then the topic information will be the same, and they will be considered the same async call. It is possible to add a reference to the calling cell to the list, which will make calls from different cells into different topics. But you can’t split different calls from the same cell into different topics easily, since we need the correlation to complete the async call.

 

One timing aspect is the throttling Excel does in the step 4(d). Excel will not fetch data from the RTD server too often – there is a setting called the RTD ThrottleInterval that sets how Excel will wait before fetching now data from the RTD Server. You’ll see some discussion of this setting in other discussions on the Google group.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/a21d9522-d334-4e2e-8566-1b8f24d94849n%40googlegroups.com.

Reply all
Reply to author
Forward
0 new messages