SQL Server: Using Service Broker to parallely run several stored procedures

2 votes
1 answer
1381 views
                          I need to use Service Broker in order to run several stored procedures parallelly (instead of sequentially). Please be aware that I need to use Service Broker, not alternative solutions such as SSIS or SQL Agent Jobs. I use SQL Server 13.0 (2016), SSMS 19.0 as well as AdventureWorksDW2016 as a database.

Unfortunately, the very few implementations I could find online do not work for me. Also I have tried writing a script myself. I do not really get any issues, however, I have provided a table to store the starttime and endtime of my procedures, and unfortunately when I execute the procedures, I can see that they are being executing after the procedure before is finished.

My script looks like so: Do you see what I am doing wrong or do you perhaps have a good implementation online? The ones that I have found, did not really help me.

    -- enable Service Broker; might be enabled by default
    --alter database [AdventureWorksDW2016_2] set enable_broker with rollback immediate;
    
    --Create a Service Broker queue and service for the stored procedure:
    CREATE QUEUE parallel_proc_queue;
    CREATE SERVICE parallel_proc_service ON QUEUE parallel_proc_queue;
    
    --Create a stored procedure that sends a message to the Service Broker queue and executes the desired stored procedure
    CREATE PROCEDURE send_parallel_proc_message
      @proc_name VARCHAR(100)
    AS
    BEGIN
      DECLARE @conversation_handle UNIQUEIDENTIFIER
      
      BEGIN DIALOG CONVERSATION @conversation_handle
        FROM SERVICE parallel_proc_service
        TO SERVICE 'parallel_proc_service'
        ON CONTRACT parallel_proc_contract
        WITH ENCRYPTION = OFF;
      
      SEND ON CONVERSATION @conversation_handle
        MESSAGE TYPE parallel_proc_message
        (@proc_name);
      
      EXECUTE @proc_name;
      
      END CONVERSATION @conversation_handle;
    END 
    
    
    --Create a stored procedure that receives messages from the Service Broker queue and executes the stored procedure specified in the message:
    CREATE PROCEDURE parallel_proc_receiver
    AS
    BEGIN
      DECLARE @message_body VARCHAR(100)
      DECLARE @conversation_handle UNIQUEIDENTIFIER
      
      WHILE (1=1)
      BEGIN
        WAITFOR (
          RECEIVE TOP(1)
            @message_body = message_body,
            @conversation_handle = conversation_handle
          FROM parallel_proc_queue
        ), TIMEOUT 1000;
      
        IF (@@ROWCOUNT = 0)
          BREAK;
      
        EXECUTE @message_body;
      
        END CONVERSATION @conversation_handle;
      END
    END
    
    
    -- define the contract for the Service Broker conversation
    CREATE MESSAGE TYPE parallel_proc_message
      VALIDATION = WELL_FORMED_XML;
    
    CREATE CONTRACT parallel_proc_contract
      (parallel_proc_message SENT BY ANY);
    
    
    --Start the Service Broker conversation
    DECLARE @conversation_handle UNIQUEIDENTIFIER
    
    BEGIN DIALOG CONVERSATION @conversation_handle
      FROM SERVICE parallel_proc_service
      TO SERVICE 'parallel_proc_service'
      ON CONTRACT parallel_proc_contract
      WITH ENCRYPTION = OFF
    BEGIN CONVERSATION TIMER (@conversation_handle) TIMEOUT = 180000; -- 3 minutes
    
    
    --create table to measure time (to check if parallellity actually works)
    CREATE TABLE dbo.StoredProcedureLog (
        SPName NVARCHAR(255),
        StartTime DATETIME,
        EndTime DATETIME
    );
    
    
    --define stored procedures 
    CREATE PROCEDURE usp_GetSalesForCustomer
    AS
    BEGIN
        WAITFOR DELAY '00:00:02';
    	-- Log the start time of the stored procedure
        INSERT INTO dbo.StoredProcedureLog (SPName, StartTime)
        VALUES ('usp_GetSalesForCustomer', GETDATE());
    
        SELECT c.FirstName, c.LastName, SUM(f.SalesAmount) AS TotalSales
        FROM dbo.FactInternetSales f
        JOIN dbo.DimCustomer c ON c.CustomerKey = f.CustomerKey
        GROUP BY c.FirstName, c.LastName;
    
    	-- Log the end time of the stored procedure
        UPDATE dbo.StoredProcedureLog
        SET EndTime = GETDATE()
        WHERE SPName = 'usp_GetSalesForCustomer' AND EndTime IS NULL;
    END
    
    
    
    CREATE PROCEDURE usp_GetProductSales
    AS
    BEGIN
        WAITFOR DELAY '00:00:04';
        -- Log the start time of the stored procedure
        INSERT INTO dbo.StoredProcedureLog (SPName, StartTime)
        VALUES ('usp_GetProductSales', GETDATE());
    
        SELECT p.EnglishProductName, SUM(f.SalesAmount) AS TotalSales
        FROM dbo.FactInternetSales f
        JOIN dbo.DimProduct p ON p.ProductKey = f.ProductKey
        GROUP BY p.EnglishProductName;
    
    	-- Log the end time of the stored procedure
        UPDATE dbo.StoredProcedureLog
        SET EndTime = GETDATE()
        WHERE SPName = 'usp_GetProductSales' AND EndTime IS NULL;
    END
    
    
    CREATE PROCEDURE usp_GetTopSellingProducts
    AS
    BEGIN
        WAITFOR DELAY '00:00:06';
        -- Log the start time of the stored procedure
        INSERT INTO dbo.StoredProcedureLog (SPName, StartTime)
        VALUES ('usp_GetTopSellingProducts', GETDATE());
    
        SELECT TOP 10 p.EnglishProductName, SUM(f.SalesAmount) AS TotalSales
        FROM dbo.FactInternetSales f
        JOIN dbo.DimProduct p ON p.ProductKey = f.ProductKey
        GROUP BY p.EnglishProductName
        ORDER BY TotalSales DESC;
    
    	-- Log the end time of the stored procedure
        UPDATE dbo.StoredProcedureLog
        SET EndTime = GETDATE()
        WHERE SPName = 'usp_GetTopSellingProducts' AND EndTime IS NULL;
    END
    
    --Call the send_parallel_proc_message procedure for each stored procedure you want to execute in parallel:
    EXEC dbo.send_parallel_proc_message 'usp_GetSalesForCustomer';
    EXEC dbo.send_parallel_proc_message 'usp_GetProductSales';
    EXEC dbo.send_parallel_proc_message 'usp_GetTopSellingProducts';
    
    
    --Start the parallel_proc_receiver stored procedure to execute the stored procedures in parallel
    EXEC parallel_proc_receiver;
    
    --check start and endtime of procedures, starttime should be the same
    select * from StoredProcedureLog
                        
Asked by Jessy (21 rep)
Apr 4, 2023, 12:05 PM
Last activity: May 3, 2024, 08:40 PM
SQL Server: Using Service Broker to parallely run several stored procedures

Related Questions
