question

ZaynulAbadin-9847 avatar image
0 Votes"
ZaynulAbadin-9847 asked NavtejSaini-MSFT action

Concurrent Stored Procedure calls are causing delay while retuning data

While calling multiple Stored procedures from the .net framework API asynchronously, it is taking long time for the sql server to return data. But while calling the same stored procedures individually or one by one from API, sql server is returning data significantly faster.

To optimize the performance I have already enabled SET NOCOUNT ON, tried local variable to avoid parameter sniffing and also tried SET ARITHABORT ON, but eventually having the same outcome. I am using angularjs in frontend, .net framework for the api.

NOTE:

• There are 4 different SPs, which are not dependent on each other.

• All these SPs are doing read operations only, no write or update operation.

While calling the SPs asynchronously, they should be executed and return corresponding data independently. but that is not happening. When I am calling 1 sp it takes 3-4 second, while calling 2 it becomes 8 second, while calling 4, then it becomes 16-18 second for the overall process, even though the calls are asynchronous
I have provided 2 SPs along with API and repo
API code:

 public async Task<HttpResponseMessage> GetDataAPI(string param1, string param2, string param3, string param4, string param5, bool? Param6, bool? Param7, string param8, string param9, bool? param10, bool? param11, string param12, int? param13)
 {
     Model model = await _repo.GetData(param1, param2, param3, param4, param3, param4, param5, param6, param7, param8, param9, param10, param11);
     return Request.CreateResponse(HttpStatusCode.OK, overview);
 }

Repo code:

 public async Task<model> GetData(string param1, string param2, string param3, string param4, string param5, bool? Param6, bool? Param7, string param8, string param9, bool? param10, bool? param11, string param12, int? param13)
 {
     var _param1 = string.IsNullOrEmpty(param1) ? new SqlParameter("@param1", DBNull.Value) : new SqlParameter("@param1", param1);
     var _param2 = string.IsNullOrEmpty(param2) ? new SqlParameter("@param2", DBNull.Value) : new SqlParameter("@param2", param2);
     var _param3 = string.IsNullOrEmpty(param3) ? new SqlParameter("@param3", DBNull.Value) : new SqlParameter("@param3", param3);
     var _param4 = string.IsNullOrEmpty(param4) ? new SqlParameter("@param4", DBNull.Value) : new SqlParameter("@param4", param4);
     var _param5 = string.IsNullOrEmpty(param5) ? new SqlParameter("@param5", DBNull.Value) : new SqlParameter("@param5", param5);
     var _param6 = !param6.HasValue ? new SqlParameter("@param6", DBNull.Value) : new SqlParameter("@param6", param6);
     var _param7 = !param7.HasValue ? new SqlParameter("@param7", DBNull.Value) : new SqlParameter("@param7", param7);
     var _param8 = string.IsNullOrEmpty(param8) ? new SqlParameter("@param8", DBNull.Value) : new SqlParameter("@param8", param8);
     var _param9 = string.IsNullOrEmpty(param9) ? new SqlParameter("@param9", DBNull.Value) : new SqlParameter("@param9", param9);
     var _param10 = !param10.HasValue ? new SqlParameter("@param10", DBNull.Value) : new SqlParameter("@param10", param10);
     var _param11 = !param11.HasValue ? new SqlParameter("@param11", DBNull.Value) : new SqlParameter("@param11", param11);
    
     return await _databaseContext.Database.SqlQuery<model>("exec sp1 @param1, @ param2, @ param3, @ param4, @ param5, @ param6, @ param7, @ param8,@ param9,@ param10,@ param11", _param1, _param2, _param3, _param4, _param5, _param6, _param7, _param8, _param9, _param10, _param11).FirstOrDefaultAsync();
 }

Here is one of the SPs:

 CREATE PROCEDURE sp_List_code
 @nh nvarchar(100)=NULL,
 @ctr nvarchar(100)=NULL,
 @as nvarchar(100)=NULL,
 @st nvarchar(100)=NULL,
 @cnt nvarchar(100)=NULL,
 @sc nvarchar(100)=NULL,
 @ncm bit = Null,
 @rc nvarchar(100)=NULL,
 @io bit = null,
 @ti nvarchar(30) = Null,
 @gr int = Null,
 @pn bigint=1,
 @ps bigint=20AS
 BEGIN
 DECLARE @ats int;
 DECLARE @oc bit;
 DECLARE @pgn int;
 DECLARE @pgs int;
 DECLARE @shc nvarchar(50);
 Declare @t nvarchar(30);
 Declare @gi int;
 SET @ats = @as;
 SET @oc = @io;
 set @pgn=@pn;
 set @pgs=@ps;
 SET @shc=@sc;
 SET @t=@ti ;
 SET @gi=@gr; SET NOCOUNT ON; select  mi,mci,fn,ln,st,cnt,gd,ma1,zc,nth,ctr,phn
    ,cc,ats,CAST(tg as nvarchar(100)) as tg,
    CAST(cg as nvarchar(100)) as cg, rn,
    cast((A.tg-A.cg) as nvarchar(100)) as og   from(
   select distinct a.mi,mci,fn,ln,st,cnt,gd,ma1,zc,nth,ctr,phn
    ,cc,ats,a.tg, a.cg,
   ROW_NUMBER() OVER(order by a.mi) as rn
    from a
    where a.hsc in (SELECT * FROM string_split(@shc, ','))
         and (@st is null or @st=a.st)
    and (@nh is null or @nh= a.nth)
     and (@ctr is null or @ctr=a.ctr)
   and (@ats is null or  @ats=a.ats)
   and (@rc is null or @rc=a.rc)  and (@cnt is null or @ccnt=a.cnt)
   and (@ncm is null or @ncm=a.ncm)
   and (@oc is null or @oc=a.hog)
   and (@t is null or @t = a.ti)
   and (@gi is null or @gi = a.gi)
     )A    where A.rn > (@pgn-1)*@pgs and A.rn <= @pgn*@pgs
 END

I used option(recompile) also in sp but not got any luck... any suggestion would be helpful


azure-sql-databasedotnet-standard
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ZaynulAbadin-9847 We are checking this and will get back to you. Can you confirm if you are using Azure SQL PaaS or SQL server on VM or On prem versions.

0 Votes 0 ·

@ZaynulAbadin-9847 Please help us with above questions to take this forward.

0 Votes 0 ·

0 Answers