I noticed that my ASP.NET app, which imports CSVs and turns them into DB entities, was dramatically slowing down a certain way through the import process.

Version info: EFCore 2.2.3, .NET Core 2.0

It seemed to be getting stuck when querying the database for the CSV row, to check if the entity already exists. What's weird is that it's totally fine up to that point, and it stops at almost exactly 1000 queries, every single time. After this point there's a smattering of logs indicating threads are exiting, then it processes another handful, freezes again, rinse and repeat.

I've been investigating various theories, eventually I boiled it down to this example (it actually runs off in its own thread, but to simplify the example I moved it to a controller method):

Startup.cs

...
// We use an extended DbContext that defines the various DbSets as usual
services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(dbConnectionString));
...

SomeController.cs

private readonly DatabaseContext _context;

public SomeController(DatabaseContext context) 
{
    _context = context;
}

[Route("/SomeController/TestQueries")]
public async Task<JsonResult> TestQueries()
{
    await TestRepeatedQueries();
    return null;
}

private async Task TestRepeatedQueries()
{
    for (var i = 0; i < 10000; i++)
    {
        Debug.WriteLine($"Fetching for iteration {i}");
        _ = await _context.SomeTable.FirstOrDefaultAsync(); // Nothing fancy
        // It doesn't appear that table complexity is a factor
        // The problem occurs even with a simple table with an ID and a few integers
    }
}

On hitting the controller method, the logs show that it fires away merrily, querying the DB for around 1000 iterations, but then just stops. It seems that some random worker threads are exited, and after maybe 5 seconds or so, it blasts through another ~10 iterations, so on and so forth.

I'm not sure what I'm missing here, it almost seems like there's some query limit that's being hit, and it's taking some time for the resources to be freed up before being able to resume?

For any clarifications please ask!

Update

For some unknown reason, it's just started working correctly again - the only thing I did was switch to another branch to continue work, and open another copy of the project to test things as I thought of them. As soon as I ran the sample code it just blasted through all 10,000 iterations with no issue.

This doesn't explain what caused the issue and how to reproduce it but it makes me feel like there's some caching/buffering of requests somewhere that filled up possibly?

1 Answers

0
Community On

I'm belied it is related to the tracking entities. See: https://docs.microsoft.com/en-us/ef/core/querying/tracking . await _context.SomeTable.AsNoTracking().FirstOrDefaultAsync(); should helps. I'm fall in that specific situation doing an complex import. The track entity structures grow too much. Other approach is limit the amount of operations by DBContex instances.