Efficient Data Modification With Entity Framework Core
Join the DZone community and get the full member experience.Join For Free
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. With tools like Entity LINQ we can easily write efficient data modification queries.
Let's analyze the possible cases:
- Upsert (
INSERT Case Optimizations
- Insert multiple rows in a single statement. Assuming you have a collection of promotions. The following snippet inserts them in a single query
INSERT INTO ... SELECT ...- so called bulk insert. In case the data is already in the database, it is much cheaper to avoid data pulling altogether. Potentially, the operation can be performed inside the database. The following snippet copies addresses from one table to another according to some criteria without a single byte to leave the database:
UPDATE Case Optimizations
- Bulk update works when there is a need to update multiple rows in the same table. There is a special SQL construct for this case -
UPDATE ... WHERE, which performs the update in a single query. Some databases, like SQL server, also support a more powerful UPDATE ... JOIN construct. Below, we update all the tax configurations without pulling them to the application server:
- Bulk delete, same idea for delete case:
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 providing 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. we can
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
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.
Published at DZone with permission of Konstantin Triger. See the original article here.
Opinions expressed by DZone contributors are their own.