jeudi 5 avril 2012 12:55
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.CommandText = "dbo.block1_sync";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = connection;
//Async execution of block2
Thread thread = new Thread(delegate()
connObject.ConnectionString = conn;
ExecuteBlock2 (connObject, param1,param2);
Toutes les réponses
lundi 9 avril 2012 05:58ModérateurHi .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.
TechNet Community Support
lundi 9 avril 2012 06:28
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).
- Marqué comme réponse Stephanie LvModerator lundi 16 avril 2012 07:26