How to Optimize .NET Core API Performance When Fetching Large Datasets?
I'm dealing with I tried several approaches but none seem to work..... I'm working on a personal project and Currently developing a .NET Core API that retrieves large datasets from a SQL Server database. To improve performance, I’ve implemented pagination and caching, but the response times are still higher than expected, especially when the dataset exceeds 100,000 records. I started with a simple query to fetch data: ```csharp public async Task<List<MyEntity>> GetEntities(int pageNumber, int pageSize) { using (var context = new MyDbContext()) { return await context.MyEntities .OrderBy(e => e.Id) .Skip((pageNumber - 1) * pageSize) .Take(pageSize) .ToListAsync(); } } ``` This approach works well for smaller datasets, but performance degrades significantly with larger tables. To tackle this, I turned to Entity Framework's `AsNoTracking()` method to disable change tracking for read-only queries, which has shown some improvement: ```csharp public async Task<List<MyEntity>> GetEntitiesNoTracking(int pageNumber, int pageSize) { using (var context = new MyDbContext()) { return await context.MyEntities .AsNoTracking() .OrderBy(e => e.Id) .Skip((pageNumber - 1) * pageSize) .Take(pageSize) .ToListAsync(); } } ``` While profiling the application, I noticed that database calls were still taking a considerable amount of time. Consequently, I’ve also started experimenting with SQL views to return only the columns necessary for my API response, instead of retrieving entire entities. This has been beneficial, but I'm uncertain about the trade-offs. Additionally, I’ve set up Redis caching to store frequently accessed data, but I’m unsure whether my cache invalidation strategy is effective. Right now, I’m using a sliding expiration policy, which seems to be okay, but I wonder if a fixed expiration would be better. I’m curious if anyone has insights on further optimizing performance, specifically with handling large datasets in .NET Core APIs. Are there any best practices or design patterns that you have found effective? Should I consider using stored procedures or raw SQL queries for better performance? Any advice or experience sharing would be greatly appreciated! This is part of a larger application I'm building. Any ideas what could be causing this? For context: I'm using C# on Ubuntu. Has anyone else encountered this? This is for a REST API running on Windows 10. Any feedback is welcome!