Concurrent Stored Procedure calls are causing delay while retuning data

Zaynul Abadin 1 Reputation point
2021-04-05T21:11:03.473+00:00

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 Database
.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,396 questions
{count} votes