Unleashing the potential of EF Core for Peak Performance!

Read operations

• Try to reduce projections: select only data that you’re going to use (important for complex entity objects).
• Use eager loading over lazy loading even when you don’t want to load a complex entity object in memory. It’s always possible to create separate query for the part you not needed. Usually lazy loading stays behind N+1 problem and it’s safer in long term perspective.
• Do you need change tracking for the query? If no, apply AsNoTracking() when you do not plan to use results for update/delete (readonly queries).
• Do you have performance issues with the query that contain sub-queries? Usually JOINs are faster than sub-queries and you can experiment (do not replace them blindly) with that.

Update operations

EF Core optimize insert queries automatically by batching updates when you call SaveChanges(). Let’s say you need to insert 100 users:

100 users / 42 (default batch size) = 3 insert requests to database

Batch size can be changed via MaxBatchSize(), but it’s optimal value and usually there no need to change it. For older versions of EF Core you can achive the same by saving your changes in batches.

Starting from EF Core 7 there’re ExecuteUpdate/ExecuteDelete methods which helps to reduce roundtrips to database:

// NOTE: Let’s say you need to update location for bunch of users
context.Users.ExecuteUpdate(s => s.SetProperty(e => e.Location, "Belgium"));

// NOTE: Let's do the same for single user 
context.Users
    .Where(x => x.Id == userId)
    .ExecuteUpdate(setter => setter.SetProperty(x => x.Location, "Australia"));
// NOTE: Let's say you want to delete suspisious user by email
context.Users
    .Where(x => x.Email == suspisiousUserEmail)
    .ExecuteDelete();

For older versions of EF Core you can do the same result by executing raw SQL query.

Pagination

Make sure to limit the size of collection for your output in order to avoid performance issues when you publish your changes to production environment. There you can go with either Offset or Keyset Pagination.

Offset Pagination is pagination with limit and offset. What to keep in mind:

• It’s easy to implement and intuitive.
• The “missing row” issue: when the record is deleted from previous page you might don’t see some record when you navigate in that time.
• higher offset the slower query: reads the previous records in order to reach the page.

db.Entities
    .OrderBy(x => x.Id)
    .Skip(offset)
    .Take(numberOfRecords)
    .ToList()

Keyset pagination is pagination with filtering previous records (data should be sorted). What to keep in mind:

• It’s more efficient in terms of performance: doesn’t read previous records.
• It doesn’t support random navigation.
• It needs a bit more implementation effors.
• The records should be sorted and make sure that Indexes are applied.
• You can put more than one filter condition (you need to put multiple condition when you have GUIDs as identifier).

db.Entities
    .OrderBy(x => x.Id)
    .Where(x => x.Id > lastItemIdFromPreviousPage) 
    .Take(numberOfRecords)
    .ToList()

Buffering and streaming

Buffering refers to loading query results in memory:

var entities = dbContext.Entities.ToList()
foreach(var entity in entities)
    ...

Streaming means that EF returns a single result each time instead:

foreach(var entity in dbContext.Entities)
    ...

Personally, I use more buffering then streaming. Even when I use streaming it’s combination of streaming + chunks.

Few tips about that part:

• Use chunks to retrieve large volume of data (less requests to database and don’t suck up the memory).
• Use batch operations over one record operation when it comes to add/update/delete.
• Avoid calling queries for single result record in loops.

Use indexes wisely

Every query can be optimized to be super fast, but need to not forgot about balance. I mean when index created on column read is optimized, but write is suffering at the same time. So, keep in mind:

• Create index only when slow query is spotted or you expect significant data volument increase.
• Query needs more than 10% of data then full scan (no index) can be more optimal solution.
• Keep statistic/use monitoring tools in order to spot slow queries.

Do you still have issues?

At this point it’s all about experimentation. Consider the following as recommendations:

1. Analyze SQL execution plan.
2. Rewise query and table structure itself.
3. Try to improve read operation by using Temp Table for reducing amount of data to process.
4. Try to improve read operation by using Temp Table for denormalization
5. Try to introduce caching tools like Redis for reducing number of requests to database.
6. Try to use stored procedure. That’s an edge case: I don’t like keeping any business related logic on database level.