Simultaneous requests slow down asp net core API-CodePudding

TLDR; I have an ASP.NET Core 5.0 API that's sitting at AWS. It makes a large call to MSSQL db to return ~1-4k rows of data. A single request is fine, taking ~500ms, but when multiple requests come in about the same time (4-5), the request slows to ~2000ms per call. What's going on?

There's not much more to state than what I have above. I open a connection to our DB then initialize a SqlCommand.

using (var connection = new SqlConnection(dbConnection))
connection.Open();
using (SqlCommand command = new SqlCommand(strSQLCommand))

I've tried both filling a datatable with SqlDataAdapter and using a SqlDataReader to fill up a custom object, I get similar slow downs either way. As stated above the query returns ~1-4k rows of data of varying types. And Postman says the returned Json data is about 1.95MB of size after decompression. The slowdown only occurs when multiple requests come in around the same time. I don't know if it's having trouble with multiple connections to the db, or if it's about the size of the data and available memory. Paging isn't an option, the request needs to return that much data.

This all occurs within a HttpGet function

[HttpGet]
[Route("Foo")]
[Consumes("application/json")]
[EnableCors("DefaultPolicy")]
public IActionResult Foo([FromHeader] FooRequest request)
{
///stuff

DataTable dt = new DataTable();
using (var connection = new SqlConnection(_dataDBConnection))
{
    timer.Start();
    connection.Open();
    using (SqlCommand command = new SqlCommand(
        "SELECT foo.name, bar.first, bar.second, bar.third, bar.fourth 
         FROM dbo.foo with(nolock)
         JOIN dbo.bar with(nolock) ON bar.name = foo.name
         WHERE bar.date = @date", connection))
    {
        command.Parameters.AddWithValue("@date", request.Date.ToString("yyyyMMdd"));
        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            adapter.Fill(dt);
        }
    }

    timer.Stop();

    long elapsed = timer.ElapsedMilliseconds;
}

///Parse the data from datatable into a List<object> and return

///I've also used a DataReader to put the data directly into the List<object> but experienced the same slowdown.

///response is a class containing an array of objects that returns all the data from the SQL request
return new JsonResult(response);
}

Any insights would be appreciated!

CodePudding user response：

I think your idea is correct, it shouldn't be a database problem.

I think that Session can be one suspect. If you use ASP.NET Core Session in your application, requests are queued and processed one by one. So, the last request can stay holt in the queue while the previous requests are being processed.

Another can be bits of MVC running in your pipeline and that can bring Session without asking you.

In addition, another possible reason is that all threads in the ASP.NET Core Thread Pool are busy. In this case, a new thread will be created to process a new request that takes additional time.

This is just my idea, any other cause is possible. Hope it can help you.

CodePudding user response：

First thing to note here is that your action method is not asynchronous. Second thing to note here is that using adapters to fill datasets is something I hadn't seen for years now. Use Dapper! Finally, that call to the adapter's Fill() method is most likely synchronous. Move to Dapper and use asynchronous calls to maximize your ASP.net throughput.