Parallel execution of Sql in CLR stored procedure

Question

 

I have one stored procedure which takes on an average 4 seconds to execute. This stored procedure takes care of two business blocks, viz block1, block2. These two blocks are not directly dependent on each other.

Currently when I execute a stored procedure it executes block 1 and block 2 synchronously in 4 seconds. Block 1 on an average takes 1 second and block 2 on an average takes 3 seconds to execute.

So I am thinking converting this stored procedure to CLR stored procedure and calling block1 synchronously through stored procedure and block2 ASYNCHRONOUSLY with the help of System.Threading.Thread class.

I am using .NET 4.0 and Sql server 2008.

This is working well as of now..however I would like to understand the implications of this thread inside the CLR stored procedure, context connection, Sql connection sharing. Following is the snapshot of code I am using…

using (SqlConnection connection = new SqlConnection(connectionString))             {                 //Sync execution of block2                 connectionString = connection.ConnectionString;                 using (SqlCommand cmd = new SqlCommand())                 {                     cmd.Parameters.AddRange(sqlParameter);                     cmd.CommandText = "dbo.block1_sync";                     cmd.CommandType = CommandType.StoredProcedure;                     cmd.Connection = connection;                     connection.Open();                     cmd.ExecuteNonQuery();                 }                 //Async execution of block2                 Thread thread = new Thread(delegate()                 {                     connObject.ConnectionString = conn;                     ExecuteBlock2 (connObject, param1,param2);                 });                 thread.Start();             }

Answer 1

Hi .net.developer,

If you have specified “context connection=true” on your connection string, it will use the context that invoked in the first place. Please see: Context Connection.

---

Stephanie Lv

TechNet Community Support

Answer 2

Hello,

You can't use the namespace System.Threading in a CLR, see Supported .NET Framework Libraries. And everything is execute synchrously.

If you want to performa asynchrouslies action you may use the SQL Server Service Broker to do so. You define a Service Broker + stored procedure + message + queue; from you SP you send a message to the Service Broker and this one execute it asynchronisly, while you SP continues it's work (or ends).

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing