RTD hang excel with "many" Observe-type UDFs

[Tobriand]

I have an addin (VSTO + ExcelDNA for UDF components) I am writing that uses the following pattern for a formula:

• ExcelAsyncUtil.Observe(...) to set up an observable
• Push #GETTING_DATA initially (which seems to be moderately quick)
• Aggregate formula requests into API calls
• On receiving responses from the API, push the values back to the cells
• Actual push to observables that are linked to each cell takes place on a background task

This works very well for moderate numbers of formulae (let's say, less than 50k). However, if computing a large workbook with 260k formula calls involved, something odd happens - the workbook hangs whenever I push large chunks of numbers to their respective observables.

Currently the solution I'm using is to introduce some delay between batches of pushes + detect user input and suspend pushes to my observables for a chunk of time when it's detected. The upshot is Excel is "hangy" (but not dead to input) until a user gets a click in edgewise, and then they gain control moderately quickly until they leave things sitting for 15 seconds or so.

What I'm wondering is if this is something that could better sit in a configuration passed to the Observe(...) call to provide some mechanism for deciding how much RTD data to publish and when to hold off? Or if there's anything I could be doing differently that would avoid the hanging entirely, in an ideal world.

[govert]

I suppose you are following the pattern from this sample: https://github.com/Excel-DNA/Samples/blob/master/AsyncBatch/AsyncBatch/AsyncBatchUtil.cs

I'll have to experiment a bit, and don't have personal experience of what you are doing, with large sheets using this approach. However, I know of one company that does use this extensively and have not heard of similar trouble from them.

Perhaps you could try to tweak the above sample and make a workbook that tries to reproduce what you see. Then I can take a closer look to figure out whether the problem is in Excel or the Excel-DNA implementation side.

Depending on how your functions are structured and used, it might also make sense to try to use dynamic array results to return the same data, with fewer function calls involved.