Entity Framework – improve query performance – limit roundtrips to database

Introduction

In the first post, I described query execution steps that can be potentially improved when the query is executed. In this third article, I will focus on the second phase named “Request to a database”. I will show you how to solve the following problem:

“Each create and update action send a SQL query in a separate round-trip. If your application requires 30ms to connect to a database and you create for example ten new objects, then application spends just 300ms on connecting to a database. The same problem exists for every read query that you make.”

Initially, I described only the issue with latency, but now I know that some batch operations can be faster processed by the SQLServer Server.

The only way to limit the number of roundtrips is to send many operations during one roundtrip to the database. There is no built-in mechanism in the Entity Framework to group queries and send them during one roundtrip to a database, but there are two external libraries that can help us with it:

  1. Entity Framework Plus – it’s a free library which has a “Query Future” mechanism that can help with improvements on a read side
  2. Entity Framework Extensions – it’s a paid library with a mechanism that can improve create and update side.

My entire series describe improvement with the Entity Framework 6, but It’s worth to mention that both described libraries support Entity Framework Core 2.x too.

Read side

The below LINQ query can be utilized when a list with paging is used. We fetch the total amount of row available in a database (mainly to generate pager control correctly), and we retrieve data to fulfil a grid control.

With the above code, two separate calls will be sent to a database. Now let’s change our LINQ query to use the Query Future mechanism from Entity Framework Plus library:

Then generated sql steatment looks like this:

Using Future or FutureValue method allow to add query to pending list and when the first future is send to a database all from pending list are send as well. I encourage you to take a look at the official documentation for this feature http://entityframework-plus.net/query-future.

This feature can minimalise the amount of roundtrip to the database but only when there are no dependencies between queries. It means that you cannot use Query Future in a scenario where the result from the first query is used in a second query.

Write side

If we take a look at write side we can have an improvement in the following two scenarios:

  • Batch operation –when hundreds or thousands of entities need to be created or updated
  • Bulk operation – when the same change needs to be done on a huge amount of entities

Both those can be handled by features available in the Entity Framework Extensions library.

Batch operation

Whenever you want to delete or update records, you need to retrieve them from the database first. Sometimes there are some scenarios where such behaviour is not efficient from the performance point of view because you need to retrieve a lot of data into a memory. For instance, when the client is removed all his related cases should be removed as well – we need to retrieve all cases related to a client just to mark them as deleted. The batch operation support from the Entity Framework Extensions allows creating a query that is executed directly in a database without a need to retrieve data first.

The below are LINQ queries that could be made to implemented described scenario:

Bulk operation

If you add or modify multiple entities of the same type, then the Entity Framework sends create or update SQL statement for each entity in a separate roundtrip to a database. In my opinion, there is nothing wrong with the entity framework behaviour because most of the systems modify many different entities and their number is not such big.

There are two ways to improve the performance of write operations made by the Entity Framework, and those both solutions limit the number of roundtrips and write operations are made outside the Entity Framework (Entity Framework does not generate SQL queries, but extensions do that). Let’s take a look at scenarios and ways to improve performance:

1. Bulk insert/ update/delete operation

It should be used only for bulk operations when you need to load or change a huge amount of records. The supported extensions allow sending changes in batch what improve the performance of the application. Once I had to refactor a code responsible for loading an excel content into the database, and the process took 1h with the entity framework after changing it to work the bulk insert extension processing time decreased to 5 mins. Before you use those extensions, think whether it’s a good approach for you because if you need a bulk insert and you have only one place then maybe it’s better to use SqlBulkCopy with ADO.NET instead of paying for the library.

2. Standard modyfication with SaveChanges() on steroids

You don’t make any bulk operations but your system has some performance issues, and you would like to improve create and update operations a bit. There is a way to speed up those two actions using BulkSaveChanges() extension.
This extension will create/update your data using a Merge SQL statement doing only two following roundtrips to a database.
The first call – retrieve schema of tables where data should be created/updated

The second call – send a merge statement (each per entity type) to a database.

The best part of this extension is that can be easily used, you just need to replace execution fo SaveChanges() method with the BulkSaveChanges() method and that’s all.

Before you use it on production, consider pros and cons to make a smart choice. I tested this feature in one system, and I noticed a gain of around 20% but finally, we didn’t decide to use it (we improved different part of the system, and there was no need to use it).

One Reply to “Entity Framework – improve query performance – limit roundtrips to database”

  1. Pingback: dotnetomaniak.pl

Leave a Reply

Your email address will not be published. Required fields are marked *