Executing Stored Procedure Parallel way

Question

As title, how to executed stored procedure in paralleled way?

In more detail,I have a Stored Procedure Created in Sql Server call Execute_Sql and Pass in one Parameter AreaID INT as a argument.

I called and executed successfully from .net C# by passing in one ID at a time with looping the ID to get the New_ID.

Here my problem, I am trying to do a paralleled way of executing the stored prod. Such as when i executing ID=1 ,In the mean time, I also executed ID=2 at the same time.

Whats Came on my mind in thread that created a paralleled way of processing.Is there any way of doing it on executing stored prod?

Perhaps you could write a new stored procedure that takes several `AreaID`s and then do all the processing at once on the SQL server. — Cᴏʀʏ, Oct 31 '11 at 03:32
@CoryLarson agreed with you but there can be a situation we need to show the progress of processing. — Damith, Oct 31 '11 at 03:42

score 0 · Answer 1 · answered Oct 31 '11 at 03:43

You could investigate sp_start_job

You can't pass parameters into jobs however so you'd need to get your parameters in via another means.

You'd could set up the job so that it reads parameters from a table, and populate the table with the values you want to run the sproc against, and then fire off a number of calls to the job.

You'd probably want your job to mark which row it's working on by updating a bit flag or something on the row to prevent duplication of effort between jobs.

http://msdn.microsoft.com/en-us/library/ms186757.aspx

score 0 · Answer 2 · edited May 23 '17 at 12:27

For starters, it would be helpful if you posted your code. Quite often SQL can be modified to process multiple values in a "Set-based" way. In other words, if you had a set of values to process, you could process them all simultaneously.

There are many ways to accomplish this. You could pass in a delimited string and then parse that in your proc. Lots of ideas on the implementation of this here

You could also pass in XML and "join" to the XML in your proc using APPLY.

In SQL 2008+ you can use Table Valued Parameters

Because I don't know what exactly you're doing with the AreaID parameter, I don't know if I've answered your question so here is some more info.

If you want to run a stored procedure asynchronously, your best bet is Service Broker which will allow you to put a parameter in a queue and separate thread(s) will process the queue. SQL manages all the ordering, locking, retry, spawning/killing threads etc. It's a very robust system.

There is a comment on the question from a different user who seems to be connected to the OP concerning progress tracking. Here is a thought:

You have a "queue" table that contains a batchid and the areaID parameter and a status flag.

CREATE TABLE asynchQueue
(
    RecNo INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    BatchID INT,
    AreaID INT,
    Status TINYINT
)

Your C# app inserts into the queue table with a unique batch id for your app and call (to group your requests)

You have a proc scheduled to run periodically like this:

CREATE PROC ProcessQueue
AS
BEGIN
    SET XACT_ABORT ON

    DECLARE @RecNo INT,
            @AreaId INT

    BEGIN TRAN
        SELECT TOP 1 @RecNo = recNo,
               @AreaID = areaId 
        FROM AsynchQueue WITH ROWLOCK, XLOCK
        WHERE Status = 0 

        UPDATE AsynchQueue SET Status = 1  --Mark record as in progress
         WHERE RecNo = @RecNo
    COMMIT 

    EXEC YourProc @AreaId

    UPDATE AsynchQueue SET Status = 2 -- Mark Record as complete
         WHERE RecNo = @RecNo         

 END

Or the proc could contain a loop to process all records in the table. The proc could be scheduled or invoked on demand using sp_start_job as John Weldon suggested.

You could then query status as follows:

SELECT COUNT(NULLIF(Status, 0))/COUNT(*) as PctComplete --in progress or complete div by total
FROM AsynchQueue WITH NOLOCK
WHERE BatchID = @yourBatchId

That query will give you the percent complete of your batch.

Executing Stored Procedure Parallel way

2 Answers2