Run SQL Statements Parallel within a SP

Question

Hi there,

Is there a way to run SQL Statements in parallel within the same SP?

Thanks for your time.

CREATE PROCEDURE CustomerPARALLEL

AS 

BEGIN


EXEC DBO.CUST1 - TAKES 20 MINS (INDEPENDENT)


EXEC DBO.CUST2 - (TAKES 20 MINS (NO DEPENDENCY)) HOW TO RUN THIS IN PARALLEL WHILE ABOVE EXCECUTES


END


##ANOTHER EXAMPLE 



CREATE PROCEDURE CUSTOMER

AS 

BEGIN


SELECT C1,C2,C3 INTO #T1 FROM TA (TAKES 20 MINS)


SELECT C1,C4,C5 INTO #T2 FROM TB (TAKES 20 MINS) - HOW TO RUN THIS IN PARALLEL WHILE ABOVE IS BEING BUILT


SELECT C1,C2,C3,C4,C5 FROM #T1
JOIN #T2 ON T1.C1 = T2.C1

#
The above is just an example to see if I can run few process in parallel. Yes, I know i can run directly do a join instead of temp tables. 


END

Answer 1

Hi @RJ,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

In addition to the above answer provided by Amira Bedhiafi

Is there a way to run SQL Statements in parallel within the same SP?

Yes, you can run SQL statements in parallel within the same stored procedure (SP) by using SQL Server Agent Jobs.

Here’s a basic example using SQL Server Agent Jobs:

Create a job for each independent task (e.g., CUST1 and CUST2).

Use sp_start_job to start the jobs from within your stored procedure.

Here’s how you can modify your stored procedure to run the tasks in parallel:

CREATE PROCEDURE CustomerPARALLEL
AS
BEGIN
    -- Start Job for CUST1
    EXEC msdb.dbo.sp_start_job @job_name = 'Job_CUST1';
    -- Start Job for CUST2
    EXEC msdb.dbo.sp_start_job @job_name = 'Job_CUST2';
END

For more information, please refer the document: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-start-job-transact-sql?view=sql-server-ver16

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Service Broker is a built-in feature in SQL Server that supports asynchronous processing. It allows you to send messages to a queue and process them independently.

What to do ?

• Enable Service Broker.
• Create a queue and a service.
• Send messages to the queue for EXEC DBO.CUST1 and EXEC DBO.CUST2.
• Use activated stored procedures to process the messages in parallel.

Example:

-- Create the queue and service
CREATE QUEUE ParallelQueue;
CREATE SERVICE ParallelService ON QUEUE ParallelQueue;
-- Add tasks to the queue
CREATE PROCEDURE CustomerPARALLEL
AS
BEGIN
    BEGIN TRAN;
    SEND ON CONVERSATION @conversation_handle
        MESSAGE TYPE [Task1Type] ('EXEC DBO.CUST1');
    SEND ON CONVERSATION @conversation_handle
        MESSAGE TYPE [Task2Type] ('EXEC DBO.CUST2');
    COMMIT TRAN;
END;

You can create SQL Server Agent jobs to execute independent tasks like EXEC DBO.CUST1 and EXEC DBO.CUST2 and then trigger them from the main stored procedure and run concurrently.

Answer 3

I would say the answer is: No, you can't.

Yes, you can do it with Service Broker, and you can sort of do with Agent jobs. But both solutions are complex, and Agent jobs requires permission on server level. And you cannot easily detect when the job has completed. With Service Broker you can have the main procedure to wait until the the other executions has completed. But would need a special use case for it to be worth the complexity.

It is far easier to have a client program that submits to asynchronous calls.