February 8, 2010 at 4:09 am
Is there a way to add lock for multithreading without the need to put the assembly with UNSAFE permissions on the server?
Or can we make the aggregate singlecore in .net or sql so no lock is needed?
Aggregate: Median implementation in sql server 2005.
February 21, 2010 at 10:12 am
wim.buyens (2/8/2010)
Is there a way to add lock for multithreading without the need to put the assembly with UNSAFE permissions on the server? Or can we make the aggregate singlecore in .net or sql so no lock is needed?
You don't need to concern yourself with multi-threading issues in a UDA.
If SQL Server decides to run a UDA under parallelism, it will create a separate instance of the UDA for each thread. Each object will receive one partition of data, which it is responsible for processing in the Accumulate method.
The Merge method is there to enable SQL Server to combine the partial aggregates from separate threads into one final aggregate.
Keeping to the contract for Accumulate, Merge, and Terminate is your only responsibility - SQL Server handles everything else for you. This is one of the nice things about the way SQL Server implements parallelism internally: with very few exceptions, the component that is running on multiple threads doesn't need to know anything about it.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 9, 2010 at 7:41 am
Paul,
What would you recommend for serializing a Median UDA?
I am asking this in the context of Merge method/parallel threads.
For median, we need to keep all the value in an array structure, sort it and then find the value in the middle. This work fine with an in memory array/list but in case of multiple threads, we need to serialize those values and the issue in 2005 is MaxByteSize = 8000 (I believe in 2008 it can go up to 2 GB?).
Ionut
March 9, 2010 at 8:20 am
It is problematic in 2005 for the reasons you describe. Big arrays = lots of memory usage = poor scalability. On 32-bit SQL Server you are also likely to run out of memory pretty quickly. There are any number of fast solutions for finding medians that can be written directly in T-SQL. CLR is the wrong tool for this job.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply