Trying to execute multiple stored procedures in multiple calls in parallel from .net core WebApi

Question

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.

Answer 1

If your sproc is taking seconds to run then you need to optimize it. Have your DBA perf test your sproc as it should be really fast. In a couple of seconds you should be able to return 1000s of rows. Of course the assumption here is that you have indice on the tables you're querying to speed things up. Your DBA should help with this. Note also that if a single sproc call is fast but when you call them in parallel they are slow then you are most likely running into locking issues. Your DBA should help you diagnose and fix these issues.

As for Angular there are limits. It is dependent upon the HTTP protocol and browser being used. From my understanding Chrome with HTTP 1.1 limits you to 6 calls per domain. However HTTP/2 allows more. So making a bunch of calls in a row, even async, may still sequentially process them. This should be obvious from the dev tools though.

If you're running any sort of real database server like SQL Server then it can handle 1000s of requests and isn't the issue. Although, again, your schema can be. If you don't have proper indexing, high contention for tables and/or bad execution plans then perf will plummet. Your DBA needs to help optimize this stuff. It is also possible you're doing something wrong in the backend although the code you posted, while a little to async-happy for my taste, should be fine and will run fast. The debugger can actually show you the timing of your calls. I suspect your execute call to the DB is where perf dies indicating a problem with the sproc you're calling.

Answer 2

As they say, it depends.

I don't know what these procedures do, but the example you submitted appears to be a read procedure. But if they are write procedures, there is a possibility that they access the same resource and block each other.

But you could get this result even if they are read-only procedures, if all results in a massively parallel plan. Then one can complete in 5-7 seconds by means of brute force and using all CPUs in the machine. But if you submit six of them, they will have to share the CPUs, and it will take six times longer, at least.

You could look at the query plans. If you see lot of operators with small yellow circles with arrows in them, you have parallelism.