Performance Issues with Large Data Sets in Entity Framework Core 6 Using AsNoTracking
I'm writing unit tests and Could someone explain I've searched everywhere and can't find a clear answer... I'm experiencing significant performance issues when querying large datasets using Entity Framework Core 6. I have a method that retrieves records from a database table containing over 1 million entries, and I'm using `AsNoTracking()` to improve performance since I don't need to track changes to the entities. However, the execution time is still unacceptably long. Here is a simplified version of my code: ```csharp public async Task<List<MyEntity>> GetLargeDataSetAsync() { using (var context = new MyDbContext()) { return await context.MyEntities.AsNoTracking().ToListAsync(); } } ``` When I run this code, the execution takes around 15-20 seconds to complete. I’ve tried adding pagination using `.Skip()` and `.Take()`, but this just pushes the issue further down the line and doesn’t solve the slowness when fetching larger chunks of data. In SQL Server Management Studio, the same query with no filters returns results in under 1 second. I also confirmed that there are no network issues since the database is hosted locally. I tried using projections to only select the fields I need, which did reduce the data size, but the performance still lags. Here’s what I attempted: ```csharp public async Task<List<MyEntityDto>> GetLargeDataSetAsync() { using (var context = new MyDbContext()) { return await context.MyEntities.AsNoTracking() .Select(e => new MyEntityDto { Id = e.Id, Name = e.Name }) .ToListAsync(); } } ``` The performance improved somewhat, but it's still not as fast as I expected. I also checked my indexes and ensured that they are appropriately configured in SQL Server. Is there something I might be missing regarding how EF Core handles large datasets or any additional optimizations I can apply to speed up the retrieval process? Any advice would be appreciated! My development environment is Linux. This issue appeared after updating to C# stable. This issue appeared after updating to C# 3.9. Is this even possible? I'm on Linux using the latest version of C#. I'm open to any suggestions.