DEV Community

Cover image for 🚀 .NET Query Performance: Fixing Slow Queries with Practical Techniques
hamza zeryouh
hamza zeryouh

Posted on

🚀 .NET Query Performance: Fixing Slow Queries with Practical Techniques

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");
Enter fullscreen mode Exit fullscreen mode

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.

DotNet #EntityFramework #Performance #Backend #SoftwareEngineering #CleanCode #Optimization #Coding

Top comments (0)