~2 second delay when returning async function results

[Exilliar]

Hi all,

I'm making an async function which can take anywhere from a few miliseconds to a few seconds to complete. It uses ExcelAsyncUtil.Run to run the function.

It works really well most of the time. However, I noticed that if I set off two cells with the function inside it, with one cell taking in the result of the first cell as one of it's inputs, there seems to be a 1-2 second delay on returning the result from that second cell.

I did some logging and found that for this second cell, the 1-2 second delay comes after the async function has completed. It seems like dna is waiting 1-2 seconds after the function has completed before returning to collect the result.

I made a test function that's super basic to test what is going on, it just waits two seconds and then adds the two inputted numbers together:

[ExcelFunction(Description = "Async func")]
        public static object AddAsync(int a, int b)
        {
            int _functionRunThroughId = new Random().Next(1000, 9999);
            ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

            var id = $"'({caller.RowFirst},{caller.ColumnFirst}) {_functionRunThroughId}'";

            Logging.Logger.Information($"{id} entering AddAsync");
           
            return ExcelAsyncUtil.Run("AddAsync", new object[] { a, b }, delegate
            {
                Thread.Sleep(2000);
                Logging.Logger.Information($"{id} returning result");
                return a + b;
            });
        }

(the logging is done by serilog)

and I still get the same issue.

I looked at this previous post and tried using a batch request, but was still getting the 1-2 second delay.

This issue occasionally does not occur when only running a couple of rows at a time, but it does still sometimes happen with a small number of rows.

For additional context, these are the logs for just one row that I got from the above code when I dragged down ~50 rows on the table in the attached image (the first two columns are just data, the second two are running the function):

2022-10-24 14:15:36.734 +01:00 [INF] '(6,15) 7835' entering AddAsync
2022-10-24 14:15:38.774 +01:00 [INF] '(6,15) 7835' returning result
2022-10-24 14:15:38.782 +01:00 [INF] '(6,15) 2477' entering AddAsync
2022-10-24 14:15:38.784 +01:00 [INF] '(6,16) 2477' entering AddAsync
2022-10-24 14:15:42.811 +01:00 [INF] '(6,16) 2477' returning result
2022-10-24 14:15:44.796 +01:00 [INF] '(6,16) 1674' entering AddAsync

I am starting to think that this delay might be unavoidable, but I wanted to make sure before accepting it and moving on. I'm also fairly new to excel dna, so there is a very good chance that I'm misunderstanding some part of how this works, apologies if I've missed something obvious.

Thanks

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on GitHub Sponsors.

By signing up for a monthly contribution you encourage further development and support.

--------------------------------------------------

 

Hi there,

 

You’re seeing the Excel RTD throttle interval at play.

 

Excel-DNA implements the async functions using RTD behind the scenes. Excel has a setting called the RTD Throttle Interval. The default value is 2000 milliseconds, and it can be set using the COM object model by modifying the Application.RTD.ThrottleInterval value (in milliseconds). The settings I global (for all RTD servers and all Excel instances) and is persistent (so if set it will stay that way in future Excel runs). Other add-ins may also be settings this values and be affected by this value, so one should be careful when setting it.

 

A ThrottleInterval of 2 seconds means Excel will not process RTD updates more often than every 2 seconds. So even if you push data every 100 milliseconds Excel will wait for 2 seconds after the last fetch before it processes the new RTD data.

 

Mostly you can safely set the value to something like 500, but I would not make it too small, otherwise Excel can become unstable.

It’s also a good idea to give the user a way to opt out of this setting, should they run into trouble.

 

-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/4bf7baf0-661c-462f-8870-2c45971f364an%40googlegroups.com.

[Exilliar]

Hi Govert

Thank you! That's worked super well!

Just incase anyone else comes across this question the code I ended up putting in was:

var com = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
com.RTD.ThrottleInterval = 500;

I put that in the ExcelAddin class and it all started working.

Thanks!