How do you limit multithreading in Excel XLL UDF

Add-in Express™ Support Service
That's what is more important than anything else

How do you limit multithreading in Excel XLL UDF
 
Jason Rodman




Posts: 40
 Joined: 2016-11-01
I am porting a COM excel addin to XLL and one of the features I am taking advantage of is multi-threaded recalculation. Normally these UDF's are pretty taxing on our database and I want to put in some protection so that multi-threaded recalculation does not bring my server to its knees. We have thousands of spreadsheets that have thousands of individual calls to these functions in each of them, with hundreds of concurrent users. Now the performance gain is great, a spreadsheet that took 30 minutes to recalc before now takes 90 seconds, but I witnessed it peg the SQL server processor when recalculating 1200 cells in parallel which brings all other operations to a halt. Do you know of any way to put limits on how many parallel threads can run when recalculating in excel?
Posted 03 Nov, 2016 08:46:42 Top
OliverM


Guest


Hi Jason,

With
ExcelApp.MultiThreadedCalculation.ThreadCount = xx
you can set the number of threads used for recalculation.
Posted 03 Nov, 2016 09:49:24 Top
Jason Rodman




Posts: 40
 Joined: 2016-11-01
That works! Its a lot easier than employing a SemaphoreSlim. The only downside is it affects all recalculation, not just specific UDF's. If anyone needs more granular control I have found SemaphoreSlim to be effective in limiting processing when used in a UDF. It doesn't actually prevent extra threads from being created, but it holds up threads that are over the limit until others complete. This way I can allow less resource intensive UDFs to run on more threads than more resource intensive ones.
Posted 03 Nov, 2016 09:57:59 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Thank you very much! Both Jason and Oliver! That's two great pieces of info to know! thank you very much for sharing your knowledge!


Andrei Smolin
Add-in Express Team Leader
Posted 03 Nov, 2016 10:24:48 Top