Efficient Data Modification With Entity Framework Core

-

The rule of dumb tells that for optimal performance we need to make as few database requests as possible. This is especially relevant for insert and update scenarios, where we sometimes need to work with thousands of objects. Sending those objects to the database one by one is usually significantly slower than in a batch. (Spoiler - interactive examples of what's discussed below).

The number of possible cases is pretty small, but our options and best approach might differ significantly. So, the cases:

INSERT

For this case we have 2 possible optimizations

  • Insert multiple rows in a single statement (batch insert)
  • INSERT INTO ... SELECT ... construct, which inserts data from another table (bulk insert)

UPDATE

  • UPDATE ... WHERE / UPDATE JOIN - so called bulk update. Pay attention, this is not the conventional update where we want one value to be replaced with another one. This optimization makes the same change on multiple rows. And the gain is that it's performed in "one shot", without pulling the rows to application server.
  • DELETE ... - same idea for the delete case (bulk delete).

Upsert

Upsert means UPDATE or INSERT in a single statement. In cases when a table has more than 1 unique constraint (in addition to PK), a plain INSERT can fail on duplicate keys. In those cases, we usually want to ignore, replace, or combine some existing fields with new values. Most vendors support this capability, but use different syntax and provide slightly different features.

  • MERGE - SQL Server and Oracle. In fact this is the official standard. In its simplistic form, it lets to specify what to do WHEN MATCHED , i.e. when there is a unique key collision; and what to do WHEN NOT MATCHED, i.e. there is no collision so we can INSERT safely.
  • INSERT ... ON DUPLICATE ... - MySQL and Postgres. The syntax is much simpler but lets you handle only the most common cases (compared to the feature-packed MERGE).

Summary

Batch/bulk updates are usually an order of a magnitude faster than working with entities one by one. Optimizing those scenarios is usually a low hanging fruit improvement. Don't miss the opportunity! Here you can find the interactive examples.

Comments

Post a Comment

Popular posts from this blog

Hierarchical Query in SQL with C# and Entity Framework Core

-
Image
Hierarchies are very common in our lives and therefore in the problems we need to solve. The most familiar cases are organizations, products built from parts and other products, file systems etc. When an hierarchy is stored in a database table, it's usually represented by rows referencing parent rows, recursively. Suppose we want to query all the employees (directly or indirectly) reporting to a mid-level manager. Can we do that in SQL? Of course there is a naive solution to select employees directly reporting to that manager, take their ids, perform the query again, etc. But clearly it's not efficient. Fortunately SQL has a standard support for hierarchical queries in a single and efficient query. The following example demonstrates an hierarchical query using C# and EF Core using ELINQ : int ? managerId = null ; //external parameter - "highest" manager id DbContext.Staffs.Query(() =>
Read more

SQL: SELECT DISTINCT on Some Columns

-
Image
In some cases we want to ensure uniqueness only on a subset of row columns. There are many different solutions proposed, some of them are simple and others are less. I didn't find a generic one. So let's state the problem first, then analyze it and define a generic solution. Problem DISTINCT is not a function, we cannot "apply" it on a subset of columns. Some folks try to substitute it with GROUP BY . But GROUP BY requires that all the SELECT ed columns will be either grouped by or aggregated. Let's assume we use a FIRST / MIN / MAX aggregate. In this case all the columns that are not grouped by will have same values. I can hardly believe this is desired. Analysis Let's understand better why the original DISTINCT approach does not work. We get too many rows, since all those not important columns are returned with their multiple values, therefore we have duplicates on the im
Read more