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.
ELINQ lets write optimized INSERT
queries using C#:
INSERT
Multiple Rows in a single statement. (Refer to your vendor documentation for an optimal batch size)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.In case of data update there are 3 important scenarios:
UPDATE ... WHERE
, which performs the update in a single query. Some databases, like SQL server, also support a more powerfull UPDATE with JOIN construct.And the last scenario - UPSERT
. INSERT
the new rows and UPDATE
existing. Most vendors support it, but with different syntax and capabilities:
INSERT ... ON DUPLICATE ...
- MySQL and Postgres. Sakila MySQL example:
// There is a store which might already exist in the database.
// Should we add it or update? (PK is not always the only UNIQUE KEY)
newOrExisting.LastUpdate = DateTime.Now;
var rows = DbContext.Database.Execute((Store store) =>
{
var view = store.@using((store.StoreId, store.AddressId, store.ManagerStaffId, store.LastUpdate));
INSERT().INTO(view);
VALUES(view.RowFrom(newOrExisting));
ON_DUPLICATE_KEY_UPDATE(() => store.LastUpdate = INSERTED_VALUES(store.LastUpdate));
});
Console.WriteLine($"{rows} rows affected");
The queries can be freely combined as demonstrated in the MERGE above.