As backend developers, we often encounter performance issues due to inefficient queries. A single unoptimised query can slow down your entire application, increase cloud database costs, and frustrate users.
Here are powerful and practical techniques to optimise your queries in .NET (Entity Framework Core):
1. Use AsNoTracking() for Read-Only Queries
Use EF Core logging, MiniP
When retrieving data only for reading, use .AsNoTracking() to skip change tracking, reducing memory usage and improving speed.
var products = await dbContext.Products
.AsNoTracking()
.ToListAsync();
Why? EF won’t track changes, reducing overhead for read-heavy operations.
2. Select Only Required Columns
Fetching entire entities when you only need specific fields wastes resources.
Bad:
var users = await dbContext.Users.ToListAsync();
Good:
var userNames = await dbContext.Users
.Select(u => u.Name)
.ToListAsync();
Why? Smaller data loads mean faster queries and less memory usage.
3. Prevent N+1 Query Problems
Fetching related data within loops leads to multiple database hits (N+1 issue). Instead, use .Include() for eager loading:
var orders = await dbContext.Orders
.Include(o => o.OrderItems)
.ToListAsync();
🔧 Tip: Use .ThenInclude() for nested navigation properties.
4. Always Filter at Database Level
Use EF Core logging, MiniP
Applying .Where() after loading data into memory leads to performance issues.
Bad:
var orders = await dbContext.Orders.ToListAsync();
var filtered = orders.Where(o => o.Status == "Delivered");
Good:
var filtered = await dbContext.Orders
.Where(o => o.Status == "Delivered")
.ToListAsync();
Why? Let SQL handle filtering efficiently instead of your server.
5. Use Any() for Existence Checks
Checking for existence with .Count() > 0 scans the entire table, slowing performance.
Good:
bool hasOrders = await dbContext.Orders.AnyAsync();
6. Paginate Large Datasets
Returning thousands of records in one go is rarely needed. Always implement pagination:
var page = await dbContext.Users
.OrderBy(u => u.Id)
.Skip(10)
.Take(10)
.ToListAsync();
Why? This reduces memory usage and improves response time.
7. Use Proper Indexing
Ensure your database has indexes on frequently queried fields like foreign keys and search columns. This significantly improves lookup speed.
8. Cache Static or Rarely Changed Data
For data that doesn’t change often (e.g. country lists, categories), implement in-memory caching or distributed caching like Redis to reduce repeated DB hits.
9. Optimise LINQ Queries
Complex LINQ queries can generate inefficient SQL. Always check the generated SQL to ensure queries are fully translated and no client-side evaluations occur.
*10. Profile Your Queries *
profiler, or your SQL server’s execution plan analyser to detect:
• Slow queries
• Missing indexes
• Inefficient query translations
11. Use AsSplitQuery() for Multiple Includes
When you load multiple related collections, EF Core by default uses a single SQL query with JOINs. This can lead to Cartesian explosion, duplicating data and consuming lots of memory.
Solution: .AsSplitQuery()
var orders = await dbContext.Orders
.Include(o => o.OrderItems)
.Include(o => o.ShippingAddress)
.AsSplitQuery()
.ToListAsync();
Why? EF Core executes one query per Include, avoiding large JOIN result sets and improving performance in many scenarios.
When to use: For queries with multiple collections where JOINed queries create performance issues.
When to avoid: For simple includes, as multiple queries add slight latency.
Final Thoughts
Optimising queries is essential for:
• Fast user experiences
• Lower cloud costs
• Scalable and maintainable applications
What other query optimisation techniques do you use daily? Share in the comments to help others learn and grow.
Top comments (0)