Optomizing an Script to simoutanously/parallel executes the same SP with diff param

Question

Good day,

I have an SSRS report executing an SP that executes multiple SP
I had a while loop with temp tables which i changed to param tables and buidling dynamic "union" query

Please any ideas on optimizing the below much faster, it ranges for a month so i end up having 31 * 2 dynamic t-sql exec SP for @SQLTest & @SQLTestUnits > both executes the same SP with different param and resultsets stored separately

I cant touch the main sp "usp_Client_Base" > looking for ways to improve performance from my current sp
I read the while are bad hence why im using it to build the dynamic qry

Create SP Test:
SET @TempDate = @DateFrom
SET @Ccnt = 0
SET @CNT = DATEDIFF(D,@DateFrom,@DateTo)
SET @SQLTest = ''
SET @SQLTestUnits = ''
SET @CcntTest = 0

 WHILE @CcntTest < (@Cnt +1)  
 BEGIN  
  
 SET **@SQLTest** = @SQLTest  + ' EXEC [dbo].[usp_Client_Base] ''' + CONVERT(VARCHAR(200),@PoolXML) +''',Null,''' + CONVERT(VARCHAR(11), @Date, 106) +''', Null, '''', Null, Null, Null,0, 1, 0,0,0,Null,0,0'  
 SET **@SQLTestUnits** = @SQLTestUnits  + ' EXEC [dbo].[usp_Client_Base] ''<objectlist><object type="CodeLookup" code="HOLD" parentChild="0" grouping="0"/></objectlist>'',Null,''' + CONVERT(VARCHAR(11), @Date, 106) +''', Null,'''',Null, Null, Null,0,1,0,0,0,Null,0,0'  
  
 SET @CcntTest = @CcntTest + 1  
 SET @TempDate = DATEADD (D , @CcntTest, @DateFrom )  
 END  
  
 --INSERT INTO @Client_Test  
 EXEC sp_executesql @SQLTest  
 --INSERT INTO @Client_TestUnits  
 EXEC sp_executesql @SQLTestUnits  

Possible to simultaneously/parallel from within the script to "sp_executesql @SQLTest & @SQLTestUnits" without locking one another?
Please any ideas on other methods, Thank you!

Answer 1

If you want to do parallel processing from within SQL Server, there are two options:

1. A CLR stored procedure that uses the threading in .NET.
2. Send messages on a Service Broker queues and have an activation procedure with multiple queue readers.

Both will have to count as advanced, and selecting any of them is not a step you take lightly.

A more normal SQL approach is to rewrite the stored procedure(s) so that they can process all inputs at once, avoiding the need for the loop.

Answer 2

Thank You Very much!

I should make a copy of the original SP without affecting other solutions using "usp_Client_Base"
and create a slim faster version thereof

Answer 3

Thank You for assisting
Those approaches will be difficult to implement

Editing the existing SP, it loops for all days of date range due the sp_Client_Base calculating AsAtDay (Snapshot) "@apl " for each day and wont be able to insert a date range

Any other ideas plz?