MicrosoftMicrosoftthanks for the reply and I'm using 8. But I have faced a strange scenario I going to describe it below.
While calling multiple Stored procedures from the .net framework API asynchronously, it is taking long time for the azure sql to return data. But while calling the same stored procedures individually or one by one from API, azure sql 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:
ALTER PROCEDURE [dbo].[sp1]
@param1 bit=NULL,
@param2 nvarchar(100)=NULL,
@param3 bit=NULL,
@param4 nvarchar(100)=NULL,
@param5 bit = Null,
@param6 nvarchar(100)=NULL,
@param7 nvarchar(50) = Null,
@param8 nvarchar(100) = Null,
@param9 bit = Null,
@param10 nvarchar(30) = Null,
@param11 int = Null
AS
BEGIN
SET NOCOUNT ON;
--Set arithabort on;
declare @parameter1 int , @parameter2 int, @parameter3 int
select @parameter1 = count(distinct a.col1)
from table1 a
where
((col2) in (SELECT * FROM string_split((@param4), ',')))
and (@param1 is null or @param1= a.col12)
and (@param3 is null or @param3=a.col16)
and (@param6 is null or @param6=a.col18)
and (@param2 is null or @param2=a.col17)
and (@param5 is null or @param5=a.col3)
AND (@param7 is null or @param7 = a.col7)
AND (@param8 is null or @param8 = a.col19)
AND (@param9 is null or @param9 = a.col21)
and (@param10 is null or @param10 = a.col25)
and (@param11 is null or @param11 = a.col26)
select
@parameter2 = ISNULL(SUM(a.col13),0),
@parameter3 = ISNULL(SUM(a.col8),0)
from table2 a
where ((col2) in (SELECT * FROM string_split((@param4), ','))) and
(@param2 is null or @param2 = a.col12) and
(@param6 is NULL or @param6=a.col9) AND
(@param7 is null or @param7=a.col16) and
(@param8 is null or @param8=a.col11) and
(@param1 is null or @param1 = a.col3) and
(@param5 is null or @param5 = a.col6) and
(@param3 is null or @param3 = a.col4) and
(@param9 is null or @param9 = a.col5)
and (@param10 is null or @taxId = a.col14)
and (@param11 is null or @grouper = a.col17)
SELECT @parameter1 AS column1, @parameter2 AS column2, @parameter3 as column3
END
I have used option(recompile) as well in SP. Do you have any suggestion why resource are being shared when going to make async call to the SP
