Entity Framework: Improving Performance
When adding or modifying a large number of records ( 10³ and more), the Entity Framework performance is far from perfect. The reasons are architectural peculiarities of the framework, and non-optimality of the generated SQL. Leaping ahead, I can reveal that saving data through a bypass of the context significantly minimizes the execution time.
Let’s consider how we can improve the EF performance.
1. Insert/Update with the standard means of Entity Framework
Let’s start with Insert. A standard way of adding new records to a DB is adding them to the context with the subsequent saving:
context.Orders.Add(order);
context.SaveChanges();
Each call of the Add method leads to an ‘expensive’ (in terms of execution) call of the DetectChanges internal algorithm. This algorithm scans all entities in the context, compares the current value of each property with a source value that is stored in the context, and updates links between entities, etc. Until the release of EF6, disabling DetectChanges for the time of adding entities to the content was the most popular way to improve performance:
context.Orders.Add(order));
context.Configuration.AutoDetectChangesEnabled = true;
context.SaveChanges();
Also, it is not recommended to store dozens of thousands of objects in the context, as well as to save data in blocks with saving context and creating a new context for each N objects, as it is shown here. Finally, the optimized AddRange method has been introduced in EF 6. The method improves performance up to the level of the Add+AutoDetectChangesEnabled binding:
context.Orders.AddRange(orders);
context.SaveChanges();
Unfortunately, the methods listed above do not solve the core problem, that is: during saving data into DB, a separate INSERT query is being generated for each new record!
INSERT [dbo].[Order]([Date], [Number], [Text])
VALUES (@0, @1, NULL)
The situation with Update is the same. The following code:
var orders = context.Orders.ToList();
//.. writing new data
context.SaveChanges();
leads to execution of a separate SQL query for each modified object:
UPDATE [dbo].[Order]
SET [Text] = @0
WHERE ([Id] = @1)
In the most simple cases, EntityFramework.Extended can help:
//update all tasks with status of 1 to status of 2
context.Tasks.Update(
t => t.StatusId == 1,
t2 => new Task { StatusId = 2 });
This code is executed through a bypass of the context and generates 1 SQL query. Obviously, this solution is not universal and serves only for the recording the same value into all target rows.
2. In search of solution for the problem
Since I hate reinventing the wheel, I started searching for the best practices of the bulk insert with EF. It seemed to be a typical task, but I failed to find a suitable solution ‘out of the box’. At the same time, SQL Server offers a range of quick insert techniques, such as the bcp utility and the SqlBulkCopy class. I will further consider the latter class in detail.
System.Data.SqlClient.SqlBulkCopy is an ADO.NET class for witting large volumes of data into SQL Server tables. It can use DataRow[], DataTable, or implementation of IdataReader as a data source.
It can:
- send data to the server block-by-block with support for transactions;
- execute column mapping from DataTable against the DB table;
- ignore constraints, foreign keys during insert (optional).
Cons:
- insert atomicity (optional);
- failure to continue work after exception;
- weak error processing options.
Let’s go back to our problem — absence of the SqlBulkCopy and EF integration. There is no conventional approach to solving this task, but there are several projects, such as:
It turned out practically not working. When I was studying Issues, I stumbled upon a discussion with Julie Lerman who described a problem similar to mine that remained unanswered.
Alive project, active community. No support for Database First, but developers promise to implement it.
As well, you can try 4 more ways, how to improve Entity Framework performance. Everything is on the one page:
3. Integration of Entity Framework and SqlBulkCopy
4. Advanced insert with MERGE
5. Performance comparison
6. Conclusion
Thank you for your time. Hopefully this article was useful for you.
