Trying to execute multiple stored procedures in multiple calls in parallel from .net core WebApi
Hi. I've been trying to find a way to execute stored procedures in SQL Server in parallel from a web API in .net core. The request are being done by an Angular project.
My method, in my webapi, in the lowest level that actually makes the call looks like this:
public static async Task<List<dynamic>> CollectionFromSp(this DbContext dbContext, string SpName, List<SqlParameter> SpParameters)
{
List<dynamic> result = new List<dynamic>();
SqlConnection connection = new SqlConnection(dbContext.Database.GetDbConnection().ConnectionString);
using (connection)
{
await connection.OpenAsync();
SqlCommand cmd = new SqlCommand(SpName, connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(SpParameters.ToArray());
using (var dataReader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
{
while (await dataReader.ReadAsync())
{
var dataRow = GetDataRow(dataReader);
result.Add(dataRow);
}
}
}
return result;
}
And this is how I would call it from a top layer:
public async Task<List<dynamic>> GetDataGastoTotalPorGrupo(DashboardParamsDTO parameters)
{
var lparams = new List<SqlParameter>()
{
{ new SqlParameter() { ParameterName = "@BankId" , Value = parameters.BankId, SqlDbType = SqlDbType.SmallInt } },
{ new SqlParameter() { ParameterName = "@ViewType" , Value = parameters.ViewType, SqlDbType = SqlDbType.Bit } },
{ new SqlParameter() { ParameterName = "@MonthId" , Value = parameters.MonthId, SqlDbType = SqlDbType.Int } },
{ new SqlParameter() { ParameterName = "@BrandId" , Value = parameters.BrandId, SqlDbType = SqlDbType.Int } },
{ new SqlParameter() { ParameterName = "@MonthIdFrom" , Value = parameters.MonthIdFrom, SqlDbType = SqlDbType.Int } },
{ new SqlParameter() { ParameterName = "@MonthIdTo" , Value = parameters.MonthIdTo, SqlDbType = SqlDbType.Int } }
};
List<dynamic> list = await DynamicConnection.CollectionFromSp(CTBdc, "dbo.usp_ResumenMensual_ListaGastoTotalPorGrupo", lparams);
CTBdc.Database.CloseConnection();
return list;
}
I have six requests made from my Angular project and each one in the end uses the first method "CollectionFromSp", but with different parameters.
The thing is, each one of these six requests takes about 5 or 7 seconds if I call them one by one from Postman, but when I call all six at the same time from my Angular project it takes about 40 seconds to finish everything, like they are being executed sequentially. In Chrome DevTools I can see the slowest one taking a lot more time. What's weird to me is that in SQL Server Profiler I can see all six "RPC:Starting" events appearing at the same time.
So, is this a normal behavior and I have to optimize my queries? Maybe my DB server is not strong enough to execute everything quick enough? Or am I doing something wrong, either in my backend or in the DB and it should be possible to execute everything in the 6 to 8 seconds that takes the slowest SP?
Thanks in advanced.