Efficient Data Modification

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:

• Bulk Update, where 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 powerfull UPDATE with JOIN construct.
• Bulk Delete, same idea for delete case.

And the last scenario - UPSERT. INSERT the new rows and UPDATE existing. Most vendors support it, but with different syntax and capabilities:

• MERGE - SQL Server and Oracle.
• 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.

< BACK | HOME