Welcome to today’s post.
In today’s post I will discuss and show how to improve performance in queries you run through the Entity Framework Core data context with DbSet entities from a data context, and how they are used with LINQ to produce a query result on the client end (which can be either a Web API or a client application).
For more details on how to use LINQ with Entity Framework Core, refer to my previous post, where I showed how to implement data access queries within a .NET Core application with Entity Framework Core and LINQ.
Today, I will be looking into the use of two methods:
- No tracking of queries
- Query optimization
Optimizing the Query
If you have written SQL queries before, you will know that there are ways in which we can optimise queries so that they run on the backend server with less resources being used to query data in the individual tables and combine data and selected fields between the tables involved within the query. This is more apparent when we run queries that use joins and aggregations.
For example, I have a query that is a WHERE that filter on the Title field.
Below is a query that gets all the books into a List then filters the in-memory data in the list:
return await _bookRepository.GetAll().ContinueWith(b =>
{
var list = b.GetAwaiter().GetResult();
return list.Where(b => b.Title.Contains(filter)).ToList();
});
The GetAll() method from the repository is the following:
public async Task<List<T>> GetAll()
{
return await _db.Set<T>().ToListAsync();
}
The SQL trace and timing for the above SQL filter is as follows:
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (116ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [b].[ID], [b].[Author], [b].[DateCreated], [b].[DateUpdated], [b].[Edition], [b].[Genre], [b].[ISBN], [b].[Location], [b].[MediaType], [b].[Title], [b].[YearPublished]
FROM [Books] AS [b]
BookLoan.Helpers.TaskDurationMeasure: Information: GetBooksFilter(): Task duration millisecs = 714
Alternatively, we can filter the data before converting it to a list. This is done with the Where() extension method:
return await _db.Books.Where(b => b.ID == 1).ToListAsync();
The SQL trace and timing for the above SQL filter is as follows:
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [b].[ID], [b].[Author], [b].[DateCreated], [b].[DateUpdated], [b].[Edition], [b].[Genre], [b].[ISBN], [b].[Location], [b].[MediaType], [b].[Title], [b].[YearPublished]
FROM [Books] AS [b]
WHERE [b].[ID] = 1
BookLoan.Helpers.TaskDurationMeasure: Information: GetBooksFilter(): Task duration millisecs = 485
With a filter that is not indexed, the performance may be worse. With the query below:
return await _db.Books.Where(b => b.Title.Contains(filter)).ToListAsync();
The SQL trace and timing for the above SQL filter is as follows:
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (159ms) [Parameters=[@__filter_0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT [b].[ID], [b].[Author], [b].[DateCreated], [b].[DateUpdated], [b].[Edition], [b].[Genre], [b].[ISBN], [b].[Location], [b].[MediaType], [b].[Title], [b].[YearPublished]
FROM [Books] AS [b]
WHERE (@__filter_0 = N'') OR (CHARINDEX(@__filter_0, [b].[Title]) > 0)
BookLoan.Helpers.TaskDurationMeasure: Information: GetBooksFilter(): Task duration millisecs = 1017
The last query is the least efficient with the filtering of data being performed on the server on an unindexed field. The middle query is the fastest, with the filter performed on the ID field, which is indexed. The first query was a full table scan before loading the data into memory for client-side filtering.
We can see that a filter applied on at least one indexed field before loading into memory is the most efficient query.
Use Query Tracking When Necessary
In entity framework core, there is an internal process that keeps track of entities that are involved in changes within a query. This tracking allows the data context to know which records in affected entities are marked for removal, addition, and update. When a record is added to an entity, the inclusion of an identity key into a new record is also tracked so that when the new record is committed, a new identity key is assigned to the record. When a record is removed from an entity, the tracker marks the record as Remove. When a record is updated, the tracker marks the record as Modified.
To apply no tracking to a query we use the extension method AsNoTracking() as shown:
public async Task<List<T>> GetAllNoTracking()
{
return await _db.Set<T>().AsNoTracking().ToListAsync();
}
In our method we can analyze the timing a query, tracking it with the StopWatch class:
using System.Diagnostics;
…
public async Task<List<BookViewModel>> GetBooks()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
long startMSecs = stopwatch.ElapsedMilliseconds;
_logger.LogInformation($"GetBooks(): Start Millisecs {startMSecs}");
List<BookViewModel> books = await _bookRepository.GetAll();
long endMSecs = stopwatch.ElapsedMilliseconds;
stopwatch.Stop();
_logger.LogInformation($"GetBooks(): End Millisecs {endMSecs}");
return books;
}
The SQL trace and timing for the above tracked query is as follows:
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (20ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [b].[ID], [b].[Author], [b].[DateCreated], [b].[DateUpdated], [b].[Edition], [b].[Genre], [b].[ISBN], [b].[Location], [b].[MediaType], [b].[Title], [b].[YearPublished]
FROM [Books] AS [b]
BookLoan.Services.BookService: Information: GetBooks(): End Millisecs 397
We can do likewise and analyse the timing for a query with no tracking:
public async Task<List<BookViewModel>> GetBooksNoTracking()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
long startMSecs = stopwatch.ElapsedMilliseconds;
_logger.LogInformation(
$"GetBooksNoTracking(): Start Millisecs {startMSecs}");
List<BookViewModel> books = await _bookRepository.GetAllNoTracking();
long endMSecs = stopwatch.ElapsedMilliseconds;
stopwatch.Stop();
_logger.LogInformation($"GetBooksNoTracking(): End Millisecs {endMSecs}");
return books;
}
The SQL trace and timing for the above non-tracked query is as follows:
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (29ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [b].[ID], [b].[Author], [b].[DateCreated], [b].[DateUpdated], [b].[Edition], [b].[Genre], [b].[ISBN], [b].[Location], [b].[MediaType], [b].[Title], [b].[YearPublished]
FROM [Books] AS [b]
BookLoan.Services.BookService: Information: GetBooksNoTracking(): End Millisecs 381
We can see a query with no tracking has a slightly better performance than a query that is tracked.
With no tracking queries, they return the best execution performance when the query results are read-only and have not been modified within the context.
The no tracking behavior can also be enabled at the context level within the application initialization of the data context in ConfigureServices():
services.AddDbContext<ApplicationDbContext>(options =>
{
options.UseSqlServer(Configuration.GetConnectionString(connStr));
options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}
);
We can go further and improve our stopwatch code to measure the performance of a query by implementing a task performance helper that runs the query as an action and calculates the time taken to perform the query action. We can first define an interface as follows:
public interface ITaskDurationMeasure
{
Task RunAsync(Func<Task> func);
long TaskDuration();
string MethodName { get; set; }
}
We then implement the class with a handler to process the query and measure the timing as follows:
public class TaskDurationMeasure : ITaskDurationMeasure
{
private readonly ILogger _logger;
private long _taskDuration;
public TaskDurationMeasure(ILogger<TaskDurationMeasure> logger,
string methodName = "Default")
{
this._logger = logger;
this.MethodName = methodName;
}
public string MethodName { get; set; }
public async Task RunAsync(Func<Task> func)
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
long startMSecs = stopwatch.ElapsedMilliseconds;
_logger.LogInformation($"{MethodName}(): Start Millisecs {startMSecs}");
try
{
await func();
_taskDuration = stopwatch.ElapsedMilliseconds;
stopwatch.Stop();
_logger.LogInformation(
$"{MethodName}(): Task duration millisecs = {_taskDuration}");
}
catch (Exception ex) when (ex is TimeoutException)
{
_logger.LogError("Error TaskDurationMeasure(): {0}. " +
"Message: {1}. Inner Message: {1} ",
ex.GetType().ToString(),
ex.Message,
ex.InnerException.Message);
}
}
public long TaskDuration()
{
return _taskDuration;
}
}
In the above query commands, we can apply the above helper as follows (where _taskDurationMeasure is assigned through our class constructor and injected from the above interface using dependency injection):
var measuredTask = _taskDurationMeasure;
measuredTask.MethodName = "GetBooks";
await measuredTask.RunAsync(async () =>
{
await _bookRepository.GetAll();
});
long rslt = measuredTask.TaskDuration();
The above helper can be used within an integration test suite to determine performance of queries and use assertions to pass queries that perform as expected.
With the above examples and techniques, you should now be more aware of the performance tuning of queries using DbSet and LINQ.
To summarize, we can use the no tracking of queries, and tune the queries so that the slowest part of the query is run on the backed server before the results are loading in-memory for additional client filtering.
As more ways to tune queries within an Entity Framework Core data context I will discuss these in a later post.
That is all for today’s post.
I hope you found this post useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
