ExcelAsyncUtil.Run() and Bulk Recalculation

[John Bylsma]

Hi all,

I am running into an issue with my UDFs, which implement the ExcelAsyncUtil.Run, hanging whenever many cells need to recalculate at the same time.

The way I've structured it, the UDF calls the ExcelAsyncUtil.Run on a function which requests data from a REST API. It first checks to see if the requested data already exists in a cache, but if not, it will request the data and then cache it. Note: I am using caching because a user might request a single day worth of data over hundreds of days, and the amount of time to request 1 day is similar to requesting 1 year.

The issue I having is when a mass calculation occurs. For example, a user has 30+ instances of the UDF requesting different time periods. If a user changes a cell which is being used as a parameter in those 30 UDFs, all 30 UDFs will start recalculating. Sometimes they hang for as long as a minute before finally returning all at once. This happens even if all the data needed has already been cached - those should return very very fast.

I'm sure someone has experienced this before. I tried finding a thread with a similar situation, but I can't.

Is the solution to limit the number of simultaneous calls to ExcelAsyncUtil.Run()?

Thank you for your help! And dank je wel to Govert!

Best,

John.

[Govert van Drimmelen]

Hi John,

 

It doesn’t sound right – the ExcelAscynUtil.Run should return immediately and allow you to continue working, even for many thousands for formulae.

 

The actual async functions may take longer, since they are scheduled on the .NET TreadPool, which is quite small by default and grows slowly.

But that should affect how quickly you get the final result, not how quickly the initial functions run.

 

Maybe you can try to make a simple version of your code which still has the problem?

Otherwise you are welcome to contact me directly and we can arrange for a support session.

 

-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 post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[John Bylsma]

Hi Govert,

Thanks for your reply. The UDF's return data instantly while waiting for the AsyncUtil.Run to finish. The issue I am having is when I many cells recalculate, the AsyncUtil.Run takes forever to return. If 30 cells recalculate at once, it's taking 100x longer than if just one cell recalculated.

Right now my code is making some redundant web requests when multiple cells recalculate at the same time. I'm watching Fiddler to watch the flow of requests coming out of the app. The weird thing is that 5 or 6 requests fire at the time all the cells recalculate, then a few minutes go by and the rest fire all at once. Weird!

I'm working on implementing an synchronous task queue that will handle that influx of calls, one at a time, to make sure we're not making redundant calls, and to just return the cached data when we get it.

I'd love a support session, if you get time. I would compensate for the time, too.

Best,

John.

To unsubscribe from this group and stop receiving emails from it, send an email to exce...@googlegroups.com.

[Govert van Drimmelen]

Hi John,

 

It sounds like either the ThreadPool or the Http mechanism you’re using is limiting the number of concurrent calls.

Sometimes the back-end will limit concurrent http requests from a single host.

 

If you’d like to arrange for a support session, you can get hold of me at gov...@icon.co.za

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

[B W]

Any update on this issue? I am having the same issue. 

[John Bylsma]

Yes, I was able to solve it using the AsyncBatch Util and using The ExcelAsyncUtil.Observe instead of Run. It had to do with using up too many thread pool threads.

I can post some more detail later.

--

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 post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/d48aee33-06ed-4143-8b96-fc61d6ed8ec8%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

John C. Bylsma

[Bing Wang]

John,

Thank you for the quick response. 

I observed the problem while tailing the logs. The ExcelAsyncUtil.Run ( and its caller) return immediately with N/A.  I printed out Max and Available threads in the ThreadPool. Max default is 1023. There are enough available threads in the pool. But the delegates, that are running on these threads in the pool, get executed one by one. I saw they run  on different threads. So the seemingly sequential executing of delegates is confusing, because they don't run on one thread, and there are enough threads.

This happens when I drag copying a range of formula. 

Additionally, I wrote a VBA, where, I turned off autocalc, auto-fill a range with formulars, activesheet.calcualte, turn on autocalc. By doing this, I saw it ran faster. But I lost the benefit of using RTD. The sheet is locked up while the Macro is running. Since the macro is associate withe a button, the button remain pressed down while the macro is going. 

It seems to me that the ThreadPool did not dispatch the threads fast enough in some situation (too many threads as you pointed out). And I can't explain how triggering the same by running a VBA macro makes it faster.

I will try  AsyncBatch Util and ExcelAsyncUtil.Observe. Awaiting to see details, if you post it.

Maybe I can try throttling the pool by setting the Max Threads lower.

Thank you

-Bing

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/CABhHcFg-OdPdFAW7AQw%3DErBPNX2oQhducGyR-VmiSaE%2B%2BVkcdA%40mail.gmail.com.

[Henrik Bergström]

hi,

Im having same issue,

1.

Is there any example of how to use the  ExcelAsyncUtil.Observe in UDF ?

2.

Why is ExcelAsyncUtil.Observe  better here, than ExcelAsyncUtil.Run ?

Br,

Henrik

[Govert van Drimmelen]

Hi Henrik,

 

The AsyncBatch example is here: https://github.com/Excel-DNA/Samples/blob/master/Misc/AsyncBatchSample.cs

 

A simple example of using ExcelAsyncUtil.Observe is here: https://github.com/Excel-DNA/Samples/tree/master/RtdClocks/RtdClock-IExcelObservable

 

ExcelAsyncUtil.Run basically does Task.Run, which runs the async code on a ThreadPool thread. If you are making a large number of async tasks at once (like recalculating a sheet with lots of ExcelAsyncUtil.Run functions) and these async tasks block (like a slow calculation of a blocking network call) then it is easy to run out of threads.

 

You can also get into a situation where there is a recalculation loop, so when one function completed its async call and recalculates, theat triggers another function to recalculate, and when that one in turn completes the async call and recalculates, invalidates the first function again.

 

Using ExcelAsyncUtil.Observe gives you full flexibility in how the work is scheduled and completed.

But it is more difficult to use since you need to understand the .NET IObservable  pattern and how the ExcelAsyncUtil.xxx function identifiers work (typically the function name and parameter values).

 

So overall this is a somewhat tricky topic.

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Henrik Bergström

Sent: 28 April 2021 10:02
To: Excel-DNA <exce...@googlegroups.com>

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/e33a5690-4f03-4377-b50f-5f1f5eb2f2acn%40googlegroups.com.

[Henrik Bergström]

Thanks,

I will look at this code.

Br,

Henrik