Simultaneous requests slow down asp net core API

Question

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!

--EDIT AFTER ADDITOINAL TESTING---

[HttpGet] [Route("Foo")] [Consumes("application/json")] [EnableCors("DefaultPolicy")] public IActionResult Foo([FromHeader] FooRequest request) { ///stuff using (var connection = new SqlConnection(_dataDBConnection)) { connection.Open(); ///This runs significantly faster using (SqlCommand command = new SqlCommand(@"dbo.spGetFoo", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@date", request.date.ToString("yyyyMMdd")); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { ///Add data to list to be returned } } } } ///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); }

--FINAL EDIT PLEASE READ--

People seem to be getting caught up on the DataAdapter and Fill portion instead of reading the full post. So, I'll include a final example here that provides the same issue above.

[HttpGet] [Route("Foo")] [Consumes("application/json")] [EnableCors("DefaultPolicy")] public async Task<IActionResult> Foo([FromHeader] FooRequest request) { ///stuff using (var connection = new SqlConnection(_dataDBConnection)) { await connection.OpenAsync(); ///This runs significantly faster using (SqlCommand command = new SqlCommand(@"dbo.spGetFoo", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@date", request.date.ToString("yyyyMMdd")); using (SqlDataReader reader = await command.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { ///Add data to list to be returned } } } } ///Parse the data from datatable into a List<object> and return ///response is a class containing an array of objects that returns all the data from the SQL request return new JsonResult(response); }

presumably you're saturating either the network or the database server, then; you have a query that returns 1k-4k rows; is it possible to not do that? or at least: not for every call? perhaps local caching, perhaps doing more at the server so you don't need to fetch everything back? — Marc Gravell
– Marc Gravell, Commented Oct 6, 2022 at 13:41
@MarcGravell, Without going too much into nature of the calls I'll say that each request is unique enough that caching wouldn't help. And that all of the data is needed. We have a PHP api that we're replacing with this asp.net core API, and the PHP doesn't seem to have this issue. So, presumably it's not a DB issue. — Anduril28
– Anduril28, Commented Oct 6, 2022 at 14:46
You should not fill a list or a datatable or an array or anything that 1) loops over data and 2) stores the data in the controller code. You should go directly from database to controller's result using IEnumerable only (this is sometimes called "object streaming"). Or you'll execute the 4K loop at least twice and eat 4K*item memory. This can be achieved with the yield keyword (or possibly Linq's Select). And you should use async code but I'd say that comes after. — Simon Mourier
– Simon Mourier, Commented Oct 15, 2022 at 7:10
You are poking at random stars in the dark. Use profiler, it can be: connection exhaust at sql/http, task leakages (no cancellation in them or GC pressure), cache misses/no cache, bad sql plan (the reason why stored procedure overpower ORM like EF Core which tends to make horrible plans out of horrible statistics), etc. Make a habit to run profiler when dealing with performance issues. — eocron
– eocron, Commented Oct 19, 2022 at 14:38

José Ramírez · Accepted Answer · 2022-10-07 19:09:18Z

2

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.

answered Oct 7, 2022 at 19:09

José Ramírez

2,5051 gold badge9 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anduril28 Over a year ago

Fill() method isn't something I haven't used for years now, and again, was a product of testing out different methods. It just so happened to be the one I left in when writing this post. I also used a SqlDataReader to similar results. I appreciate the advice, and I may take a look into Dapper as an alternative, but I don't think this is necessarily an answer to the question. I tried making the function async Task<IActionResult> and using an await connection.openasync() and read async to no avail

José Ramírez Over a year ago

It is fine if it is not a definite answer, no worries. If you have made your project fully asynchronous, then I suggest you monitor the DB itself and make sure that the query responds in less than 500ms. If that is the case, then there is still more work that can be done in the server, but if the query time at the database level rises with load, then most likely is your db engine not able to take the simultaneous load. Good luck with your queries.

Chen · Accepted Answer · 2022-10-07 06:00:02Z

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.

ASP.NET Core session doesn't queue requests one at a time :)

Roland Buergi · Accepted Answer · 2022-10-21 12:11:14Z

The reason this is slow is that the method is not async. This means that threads are blocked. Since Asp.Net has a limited thread pool, it will be exhausted after a while, and then additional requests will have to queue, which makes the system slow. All of this should be fixed by using async await pattern.

Since SQLDataAdapter does not provide any async methods, it could be easier to use a technology which provides such an async methods, e.g. EF Core. Otherwise you could start a new task for adapter.Fill, however, this is not a clean way of doing it.

Collectives™ on Stack Overflow

Simultaneous requests slow down asp net core API

3 Answers 3

2 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Related