Multithreaded operations in Excel

[Simon]

Hi all

I've been using a complex udf written in c# using excel-dna.  I have made this function thread-safe (I hope) and marked it as such.  It does indeed run a lot faster than before.  I've also tried it out on a number of computers and virtual machines with differing numbers of cores (everything from 4 to 16) with quite interesting results.

Excel has a way of specifying how many threads you want to allow it to use for thread-safe udfs.  This is under options/advanced/formulas.

I've found that for my function, I get best results in general if the number of calculation threads is 1 less than the number of processors.  So if I have 4 cores, setting number of threads to 3 gave me the best performance.  If I have 8 cores, the best results were obtained by setting calculation threads to 7.

There were some strange results for 16 cores on a dual processor xeon virtual machine:

Using 64 bit excel, I got the best results when setting calculation threads to 15.

Using 32 bit excel, I got the best results when setting calculation threads to 8 (or thereabouts).

I wonder if anyone can explain this?

Also, I wonder if anyone knows how to programatically determine the number of cores available, and how to programatically set the number of calculation threads to be used by udfs in excel.  

All the best

Simon

[Govert van Drimmelen]

Hi Simon,

Thanks for posting this, it's far beyond anything I've tried.

You should be able to control the multithreaded calculation with the Application.MultiThreadedCalculation property, through which you can set the ThreadMode and ThreadCount properties.

System.Environment.ProcessorCount gives you the number of logical processors (counting hyperthreading). For more details, this StackOverflow question had a few ideas using WMI or API calls: http://stackoverflow.com/questions/1542213/how-to-find-the-number-of-cpu-cores-via-net-c

I suspect the n-1 finding means that the extra core available for operating system and other work reduces overall the thread-switching, so that the calculation threads can just keep running without ever being switched out.

On the xeon you might need to check how many cores it has vs. the number of threads it support with hyperthreading. I also don't know if there are limitations for 32-bit processes.

Maybe someone else can shed more light on this?

Regards,

Govert

[Simon Gross]

Hi Govert

Spot on as usual.  Thank you so much for your help.  I can now allow the user to see and change the number of threads directly from the ribbon.

I'm still investigating the relationship between processing time and number of threads.  In some configurations, I'm finding that n-1 is not always the optimal arrangement - sometimes we need a number of threads around half the number of logical processors.  Not sure why.  Maybe it has something to do with how xeon processors are set up.  In any case, using just two threads gives a big improvement over using one.  Using more than two is often better, but the marginal improvement is much smaller.  Presumably, the more threads there are, the greater the overhead from managing locks to avoid race conditions.  At some point it stops being efficient to use more threads.

All the best

Simon

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/WTD3qko6x0E/unsubscribe.
To unsubscribe from this group and all its topics, 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.

[Govert van Drimmelen]

Hi Simon,

You might need to read up a bit on "hyperthreading". For some work loads you're better off only running one thread per core, rather than one per logical processor. I think that's when you're seeing improvement using half the number of logical processors (which match the number of hyperthreading pipelines).

-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.

[FastExcel]

Hi Simon,

I did some tests on general calculation rather than specifically UDFs
https://fastexcel.wordpress.com/2014/01/29/threading-and-hyper-threading-optimizing-excel-calculation-speed-by-changing-the-number-of-threads/

I think the influence of multithreading on UDF calc speed can be very dependent on the nature of the UDFs. For instance I think returning the results to the grid is done on the main thread, so a UDF that returns a large array of values will have different characteristics to one that returns a single value.

- Charles

To unsubscribe from this group and all its topics, send an email to exceldna+...@googlegroups.com.
To post to this group, send email to exc...@googlegroups.com.

Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Simon Gross]

Hi Charles

Thanks for this excellent analysis.  I think you are right when you say the nature of the UDF can have a big effect on calculation speed.  What you say about all the results being returned on the main grid makes sense to me. 

All the best

Simon

To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.