(EFCore Best Practices) Comparison of Select, Projection, Tracking and Execution in Database

Important Note

It is important to know that I shall use various methods for running a small query just once. But this is not the correct way of determining the performance of a query. A more sophisticated library such as BenchmarkDotNet is a better option for measuring performance because it can provide statistical indicators such as Mean, Standard Deviation, Median, etc.,

I am only demonstrating the various possibilities of running a simple sum calculation. The methods are arranged in the increasing order of recommendation.

Method 1 - Loading All the Entities

In our first method we shall run a select query and obtain all the columns, even the un-necessary ones.

we shall add an anchor link to the home page and bind it to a click event handler. You can obtain the completed project from the attached downloads. I will show the query that we shall run.

// -- the project is in the attached downloads 

public async Task OnGetSum_1Async()
{
  int sum = 0;

  var list = await _ctx.Items
                        .TagWith("My Sum_1 Query")
                        .ToListAsync();

  foreach (var item in list)
  {
    sum += item.Price;
  }

  Message = $"Sum = {sum}";

}

First this query obtains all the items. After that a foreach loop is run to add the price.

Run the project to open the home page and click the "Sum after SELECT*" anchor to allow the query to complete. Take note of the time taken by this query. In my example it takes 26 milliseconds.

// -- the query takes 26 ms 

info: 20-Sep-22 09:59:40.279 RelationalEventId.CommandExecuted[20101]
 (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (26ms) [Parameters=[], CommandType='Text']
      -- My Sum_1 Query

      SELECT [i].[ID], [i].[Name], [i].[Price]
      FROM [Items] AS [i]

Video Explanation (see it happen!)

Please watch the following youtube video:

Method 2 - No Tracking

Tracking can be disabled for readonly scenarios - and it can have lead to an improvement. Let us now run this query.

// -- the project is in the attached downloads 

 public async Task OnGetSum_2Async()
{
  int sum = 0;

  var list = await _ctx.Items
                        .AsNoTracking()
                        .TagWith("My Sum_2 Query")
                        .ToListAsync();

  foreach (var item in list)
  {
    sum += item.Price;
  }

  Message = $"Sum = {sum} (My Sum_2 Query)";

}

We have used the AsNoTracking method to disable the overhead due to tracking.

Open the home page and click the "Sum - No Tracking" anchor and examine the logs.

The query now takes 3 milliseconds - which is better than the time in the previous method.

// -- the query takes 3 ms 

info: 20-Sep-22 10:08:25.278 RelationalEventId.CommandExecuted[20101]
 (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text']
      -- My Sum_2 Query

      SELECT [i].[ID], [i].[Name], [i].[Price]
      FROM [Items] AS [i]

Method 3 - Loading Only the Price

Next let us restrict the selection to just the Price column. This is the query that shall execute.

// -- the project is in the attached downloads 

public async Task OnGetSum_3Async()
{
  int sum = 0;

  var list = await _ctx.Items
                        .Select(m => m.Price)
                        .TagWith("My Sum_3 Query")
                        .ToListAsync();

  foreach (var price in list)
  {
    sum += price;
  }

  Message = $"Sum = {sum}";

}

We have used the Select method to extract only the price column from the database.

Open the home page and click the "Sum after SELECT price" anchor and examine the logs.

The query now takes 1 millisecond - which is better than the time in the previous method.

// -- the query takes 1 ms 

info: 20-Sep-22 10:08:25.278 RelationalEventId.CommandExecuted[20101]
 (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text']
      -- My Sum_3 Query

      SELECT [i].[Price]
      FROM [Items] AS [i]

Method 4 - Sum in Database

Database operations proceed most efficiently if they take place in the database. For example a sum query in the database might be better instead of first querying all the records and then using a loop to add them up.

Consider this query where SumAsync is used to perform the sum totalling in the database.

// -- the project is in the attached downloads 

public async Task OnGetSum_4Async()
{
  int sum = 0;

  sum = await _ctx.Items
                        .TagWith("My Sum_4 Query")
                        .SumAsync(m => m.Price);

  Message = $"Sum = {sum} (My Sum_4 Query)";

}

You can run this query and measure the time taken. Thanks!