How to execute sub-tasks in a SQL Procedure in parallel

I have a process that analyzes audit data from one system to build reporting data for another system. There is a managing procedure that loops for each day to be analyzed and calls a entity specific procedure with the current iteration's day. Some entities take less than a second to process while others can take minutes. Running serially as it does in t-sql the cpu utilization never crests above 8% on the 16-core server. Each of the entity specific procedures are not dependent on the others, just that all of the entities for that day are complete before the next day is started.

My idea is to have a CLR managing procedure and start the longer running procedures for the day running on their own threads, then once the quick ones are done, Thread.Join() the long running threads to wait for all Entities to complete for that day before moving on to the next.

Below is my try as the simplest thing that could work for just one worker thread, and calling Start on that thread does not result in the static method being called. I have set a break point in the HelloWorld method and it is never hit.

I have tried something very much like this in a console application and had it work as does calling it on the same thread in the commented out line at the start of AsyncHelloWorld. Is there something about threading within SQL CLR Procedures that is different?

using System.Threading;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void AsyncHelloWorld()
    {
        // HelloWorld(SqlContext.Pipe);

        var worker = new Thread(HelloWorld);
        worker.Start(SqlContext.Pipe);
        worker.Join();
    }

    public static void HelloWorld(object o)
    {
        var pipe = o as SqlPipe;

        if (pipe != null)
            pipe.Send("Hello World!");
    }
}

Answers


You absolutely cannot do that. A SqlPipe is very strongly tied to the context of the thread you were invoked on. While you can, technically, launch threads from SQLCRL, these threads must do all interaction with the caller from the original thread. But even so, launching CLR threads inside the SQL hosted environment is a very bad idea (and I won't enter into details why).

Instead, separate your logic into procedures than can be invoked in parallel and invoke these procedures in parallel from the client. You can use Asynchronous procedure execution as a pattern of scheduling procedures to be launched in asynchronously and queue based activation has built-in support for parallelism via MAX_QUEUE_READERS setting.

But most likely your procedures do not need explicit parallelism. T-SQL loads than can benefit from explicit user controlled parallelism are so rare that is not worth mentioning (not to mention that pulling transactional semantics right across parallel tasks is beyond mere mortals). T-SQL can leverage internal statement parallelism for processing data in parallel, so there is never a need for explicit parallelism.

So better you explain what is that you're really trying to solve and perhaps we can help.


Need Your Help

Best clock or number generator function for concurrency/scalability on Erlang OTP 18?

performance time concurrency erlang benchmarking

Currently in the process of converting an erlang application from version 17 to 18. Scalability and performance are prime directives in the design. The program needs a way to differentiate and so...

Change template after user login in joomla 1.5

joomla joomla1.5

Anybody ever tried to change joomla 1.5 template in code? Don't know how to do it on current version. I just wanted to change the template after the user login.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.