Faster Performance for Insert and Update in Entity Framework Core

Today in this guidelines section, we will cover a few useful guidelines to get Faster Insert and Update in Entity Framework Core.

Although the performance of SQL or entity framework query is a vast subject and has many aspects which are important.

However, this guideline details some common guidelines for making your read query faster and helping you achieve efficient updating.

Indexing the Fields in SQL

• A powerful method for facilitating effective database searches is indexing.

• Here, you searched through the sorted index first in order to increase performance rather than running a full database scan (scanning through everything).

• It’s the most effective way to manage the database scan.

• Allows you to effectively manage the scanning of each document. Please be aware that every single document in a collection will undergo a thorough scan if there are no indexes.

• It gives a query the ability to focus results set using the index.

• Indexing improved the efficiency of queries.

Indexes as AntiPattern in SQL query

• Don’t create Indexes that are not used or used rarely.

• Build indexes on only fields that are used often in the query.

• Use Composite indexes for filtering multiple columns.

• Avoid duplicate indexes.

Schema Modelling for Database

Your DB Schema modeling normalization plays important role in the performance of EFCore queries.

You need to perform Normalization and denormalization of domain entities and create them appropriately even before you could load the data the first time.

The benefits of schema modeling are as below,

• Optimize the query performance.

• Help you focus on reading and writing only selected domain entities.

• Eliminate heavy use of joins or evaluation of queries.

Break your class into small class entities.

The below example gives a small glimpse of what is expected by schema modeling.

Below Employee class is broken into small entities like Employee, Contact, SocialSecurity, etc.

Read Optimization – Don’t Load the entire Entity in the Application Memory

An Entity consists of multiple properties which are internally mapped to multiple columns in a respective Database table.

Below Example loads entire Employee entities.

Let’s say an example: If Employee has 100 columns then EF will load all 100 columns even if they are not used.

 foreach (var employee in _context.Employee)
            {
                /// Do something here 
                Console.WriteLine("EmployeeID : " + employee.EmployeeId);
            }
            _context.SaveChanges();

It’s not recommended to load the DB Context entity entirely in memory due to the below issues,

• It could cause huge memory problems if you are loading the entire Entity set (if not aware of the size of the set).  This brings all the Employee row data to the client side causing performance issues.

• Loading unnecessary data could add additional load for processing the queries.

We can optimize the above code by using the Select below,

Content-Based Optimization – Limit your query resultset

• Another optimization technique is to limit the resultset by means of some matching filter.

• Use paging or Filter matching criteria can be derived using business requirements, if possible.

• Content-based loading will let you Load only data that is much sufficient to serve the purpose. Exa UI-based application.

Update Optimization: Use of BulkUpdate

EFCore doesn’t support bulk updates as of now.

But below is the example that developer tends to write.

Here we are going to add preceding zeros to all employee’s ID fields using EFCore.

     foreach(var employee in _context.Employee)
            {
                employee.EmployeeId = employee.EmployeeId.PadRight(4, '0');
                Console.WriteLine("EmployeeID : " + employee.EmployeeId);
                _context.SaveChanges();
            }
            

In the above code,

• The first database roundtrip is performed by code _context.Employee brings all employee data on client-side code execution.

• The second database round trip is performed to save the changes. However 2nd roundtrip can be further optimized into a few more round trips depending on the database provider type and the amount of data.

• _context.SaveChanges() is being called for every record which could affect the performance.

The above code can be further optimized by calling SaveChanges at the end of foreach loop.

The second option can be further optimized as below by calling a raw stored procedure

_context.Employee.FromSqlRaw("UPDATE [EmployeeDB] SET [EmployeeId] =  RIGHT( '0000' + EmployeeId, 4)");

Hope you find these guidelines useful. We will see some more guidelines in the next updates. Hence kindly stay tuned!

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.