Re: ThreadSafe RTD

[Govert van Drimmelen]

Hi Simon,

I would guess that async or 'Observable' functions that are marked
IsThreadSafe won't work. I think the eventual call to Excel's RTD
function has to be on the main thread, so will always fail for a multi-
threaded function when it is called on a helper thread.

Since the thread-safe option is chosen at registration-time, I can't
see how you could switch the behaviour between 'observable' and multi-
threaded at runtime. Perhaps one can try to optimise the non-live case
a bit, e.g. maybe do it as async for the first call (allowing the
calculation at the back to be multi-threaded) and then cache the
result to make subsequent calls fast.

Regards,
Govert



On Mar 28, 7:58 pm, Simon <simon.mi...@solution7.co.uk> wrote:
> Hi Govert,
>
> We are looking at the RTD functionality via the Rx framework and have
> noticed that, when functions marked as threadSafe are executed, the
> function either stays at #N/A, when called from the worksheet, or appear to
> function correctly when called from the function wizard.  Is this a
> possible bug?
>
> Our goal is for the user to have the option to choose whether the functions
> are 'live', via an Observable RTD, or static, ie. return a simple value,
> but support mutli-threaded / 'threadSafe' calculation in both 'modes'.
>
> Thanks in advance,
>
> Simon.

[Naju Mancheril]

Are you sure the thread-safe mode helps? It may be useful if you have some computation-heavy function and want to avoid RTD, but why bother when the RTD support is so easy? If the user chooses to see live data, then you should use the IObservable support for that live stream. If the user wants "static" data, then you should use the async task support. This will invoke a specified function once and channel the value back through RTD. You only need to be on the main thread long enough to determine which of these you want and to call into the ExcelDna RTD framework. This will be very fast.

[Govert van Drimmelen]

Hi Simon,

The async and observable support in Excel-DNA is based on RTD and
should work on Excel 2002+.
My initial understanding of the native Excel 2010 was not promising
and is not currently supported via Excel-DNA, though I hope to support
it some day.

I would guess is that you can fill your 20k cells from a cache in less
than half a second.
Using async only for the first call (or when the cache expires) would
give you the multi-threaded back-end calculation.
Subsequent calls won't go through RTD, so would be fast.

I can't see a way to make the 'observable' functions faster than RTD
allows (single-threaded).

Switching the function registration at runtime would be possible in
theory, particularly with some of the latest check-ins. Maybe you
should consider having separate function names, and giving the users a
macro to search/replace from one to the other. That makes everything
transparent.

Regards,
Govert

* * * * * * * * * * * * * * * * * * * * * * * * *
You can encourage future Excel-DNA development
by arranging a corporate support agreement
or making a donation to the project via PayPal.

See: http://excel-dna.net/support/
* * * * * * * * * * * * * * * * * * * * * * * * *

[Govert van Drimmelen]

Hi Simon,

My understanding is this:

1. Excel has one 'main' thread, which is the thread used to load
all .xll and COM add-ins into the process, (thus the thread on which
AutoOpen is called when the .xll is loaded), and this is the same
thread used for all non-threadsafe UDF function calls.

2. Under Excel 2010, calls to Excel's RTD function (via
Application.RTD or xlfRtd with the C API) will only work from the main
thread. (See http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/01bc3771-7c83-4e36-baba-e12fe8cc1439/
which implies that this limitation was not present under Excel 2007).
Excel-DNA is not currently enforcing this limitation (I think we
should, since I might have made this assumption in writing the code).

3. If calls to Excel's RTD function cal only be made from the main
thread, then calls to Excel-DNA's ExcelAsyncUtil.Run and
ExcelAsyncUtil.Observe functions can only be made from the main
thread, and functions using these cannot be marked
'IsThreadSafe=true'.

4. Excel always creates the RTD server COM object on the main thread.
This may resolve one 'conflict' you ask about - it means that the
thread that creates the RTD server is _always_ the main thread. I'm
very sure this is true - a lot of things would be broken if this were
not true.

5. The COM contract means that the callback to UpdateNotify should
happen on the thread that created the RTD server, (which will be the
main thread). (This is because the main Excel thread is in a single-
threaded apartment, so the RTD server is created in an STA.)

6. The work done in Excel-DNA 0.30 implements a base class for RTD
server implementations - ExcelRtdServer - which implements the cross-
thread notifications you ask about. The ExcelRtdServer.Topic object
passed in the connect calls can be safely updated -
Topic.SetValue(...) - from any thread. Other calls to the RTD server
will be on the main thread. The cross-thread implementation is similar
to how WinForms implements Invoke, using Windows messages.

7. The async and observable support in Excel-DNA 0.30 is built on top
of this thread-safe RTD mechanism.

8. The thread-safe RTD notification mechanism does not affect the
issue of whether Excel will accept RTD calls on different threads. It
might be that under Excel 2007 Excel did accept RTD calls from
different threads, but the RTD server would still have to be created
on the main thread. (I suspect the implementation under Excel 2007 was
problematic (it would certainly have been difficult), and they blocked
it completely under Excel 2010. But this is pure speculation.)

9. I've posted a sample that shows how you could cache the threadpool-
scheduled async call. This won't work as-is for large numbers of cells
(you'd exhaust the threadpool too quickly) but could give you some
idea of what I mean.

[Govert van Drimmelen]

In recent versions of Excel (M365 version 2002 monthly channel or later) the RTD function is now thread-safe.

See https://docs.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-performance-and-limit-improvements

-Govert

[FastExcel]

The tests I ran on the new thread-safe RTD function showed performance improvements that were significantly greater than you would expect just from multi-threading vs single-threading, but I have not tested xlfRTD calls

[Govert van Drimmelen]

That’s interesting – thank you Charles.

 

What do you think is the best way programmatically for code to detect whether the version its running under indeed supports thread-safe RTD calls?

That way one could decide how to register the RTD-related UDFs at runtime.

 

-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/5e58b1fc-5a03-43e5-abeb-b36336ec9661n%40googlegroups.com.

[FastExcel]

I am not sure: all this channel and version numbering is not very accessible.

But I think if Application.Version=16.0 or greater and application.build=12527 or greater that probably is when the RTD speedup was introduced.