Excel threading model

[Farhan Ahmed]

I am not very familiar with the Excel threading model so perhaps someone can shed some light into it ... if I call my UDF from multiple cells, does Excel serialize the calls or do they come in parallel? 

Also, is it okay to block the thread that calls the UDF (to wait for a unit of task to complete on another thread) or am I going to get into deadlock issues?

Thanks,

Farhan

--

[Naju Mancheril]

I would not rely on any kind of mult-threading.

For all practical purposes, it's best to think
- All UDFs are being run by one Excel thread
- That same thread updates the Excel app GUI, so it can't be blocked for too long

Blocking in a UDF will lock up your entire app, and this is generally very bad. If you have some UDF that needs to get data from some slow data source, it's best to write an RTD function instead of a normal UDF.

You can verify this for yourself. Here is a sample function which sleeps.

    [ExcelFunction("Util.Sleep")]
    public static object Sleep(int ms) {
      Thread.Sleep(ms);
      return "DONE";
    }

If I call Util.Sleep(5000), I will freeze Excel for 5 seconds and then return "DONE".

If I want to write this function without freezing Excel, then the easiest way is with ExcelDna's ExcelAsyncUtil.

    [ExcelFunction("Util.SleepAsync")]
    public static object Sleep(int ms) {
      return ExcelAsyncUtil.Run("sleep", ms, () => {
        Thread.Sleep(ms);
        return "DONE";
      });
    }

This will not freeze, but display #N/A for 5 seconds. It will then show "DONE".

There are more complicated things (e.g. streams of values) which can also be used.

[Farhan Ahmed]

Great, thanks. I'll give the ExcelAsyncUtil set of functions a try.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.

To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

--

Farhan Ahmed

far...@ycharts.com | ycharts.com

P: 425-802-3818

[Farhan Ahmed]

BTW, I found this good article on the multi-threading capabilities in Excel:  http://msdn.microsoft.com/en-us/library/office/bb687899.aspx . Thought I'd share.

[Naju Mancheril]

Thanks! Looks like a great reference!

Here is some documentation on how the XLL API registers thread-safe and cluster-safe functions.

http://msdn.microsoft.com/en-us/library/office/bb687900.aspx

I believe these are controlled in ExcelDna by setting IsThreadSafe=true or IsClusterSafe=true on the ExcelFunction attribute.

There seem to be many constraints on what functions can be made thread-safe (covered in your link). Also, I think the Excel MTR support is more about parallelism ("using all your cores in calculation") than concurrency. So even if you create an IsThreadSafe=true function, it's probably *still* a bad idea to call into a webservice from it! The async support (built on RTD) is the recommended way.

[Farhan Ahmed]

Thanks for the info Naju. This is good stuff.

I'm trying to find a way to batch requests to my web service to minimize the number of round trips. For example, if there are 100 cells that are calling my UDF, I want to batch up those 100 requests into one web request rather than calling my web service 100 times. I don't believe the async utility will help me in this case, would it? 

Has anyone tried to solve this problem?

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

--

[Naju Mancheril]

Can you provide more info?

Is it the case that each cell calls the function with its own params?

And your web request will let you batch multiple sets of params into one request?

How many responses will you get back? One per request? Multiple per request? Is this some ticking thing where responses stream back?

[Farhan Ahmed]

Yes, each cell calls the functions with its own parameters. The web service allows aggregating requests into one big meta-request. The response, similarly, is bundled into one big meta-response which then can be broken apart on the client side. A unique user-specified token in the request is sent back in the response thus making it easier for the client to account for which request generated which response.

--

You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

--

[Naju Mancheril]

Also, could you provide something like a stubbed out version of the code that you currently use to call your webservice?

I'm mainly interested in how the request is constructed, whether the request-response model is synchronous or whethert there is some kind of callback, etc.

[Farhan Ahmed]

The web service implements a RESTful interface (so stateless) and data is JSON formatted. Thus the client only needs to construct the appropriate HTTP GET request (and the associated authentication token). It uses the HttpWebRequest abstraction to make the request and retrieve the response.

On Mon, Aug 13, 2012 at 3:15 PM, Naju Mancheril <naju.ma...@gmail.com> wrote:

Also, could you provide something like a stubbed out version of the code that you currently use to call your webservice?

I'm mainly interested in how the request is constructed, whether the request-response model is synchronous or whethert there is some kind of callback, etc.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

--

[Naju Mancheril]

BTW, this is one the reasons why Rx observables are so cool. I think the *best* solution would be to create a data library that exposed a function like

IObservable<Data> GetData(params) {...}

... and then use ExcelAsyncUtil.Observe().

The next best way to do this is to probably create your own ExcelRtdServer subclass. See attached file.

Some notes:

- Basically, you want to set some small delay (I picked 1 second) during which you wait for many cell requests. Once the delay is up, you want to start a worker thread which will gather all the pending cell requests, and send one batch request to your web server.

- ThreadPoolQueue provides a basic scheduling loop. It's there to simplify some of the synchronization and ensure that we don't do too much work (e.g. waiting for the web response) on the timer callback thread.

- You basically want to put your batched request sending in DoRequest. The code currently in this method can be erased. I just put it there to make sure my batching works. batchCounter and topicCounter are also used for testing. You can remove them.

- I haven't implemented any cancelation.

I tested it a bit. I quickly entered a few hundred cell requests and it all seemed to stay in one batch.

Of course, any cell request entered after 1 second will go in another batch.

[Naju Mancheril]

Oops, it's possible that there's a compile error or something around ConnectData [not sure].

I'm currently working with a slightly modified version of ExcelDna, so I just guessed what the signature should be. I think I probably need an override or something.

If it's broken, just fix it up to the point where RtdBatchExample is overriding the ExcelRtdServer implementation of ConnectData. After that, it should work fine. If you have problems, just post back.

[Farhan Ahmed]

Thanks - this is great stuff. I'll play around with it a bit and report back. 

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.