Accelerating SQL Bulk Inserts Using C# and EF Core

By
Fast SQL Bulk Inserts

Fast SQL Bulk Inserts With C# and EF Core

Whether you're building a data analytics platform, migrating a legacy system, or onboarding a surge of new users, there will likely come a time when you'll need to insert a massive amount of data into your database.

Inserting the records one by one feels like watching paint dry in slow motion. Traditional methods won't cut it.

So, understanding fast bulk insert techniques with C# and EF Core becomes essential.

Options for Bulk Inserts

In today's issue, we'll explore several options for performing bulk inserts in C#:

  • Dapper
  • EF Core
  • EF Core Bulk Extensions
  • SQL Bulk Copy

Code: User Class


public class User
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PhoneNumber { get; set; }
}

This isn't a complete list of bulk insert implementations. There are a few options I didn't explore, like manually generating SQL statements and using Table-Valued parameters.

EF Core Simple Approach


using var context = new ApplicationDbContext();

foreach (var user in GetUsers())
{
    context.Users.Add(user);

    await context.SaveChangesAsync();
}

Let's start with a simple example using EF Core. We're creating an ApplicationDbContext instance, adding a User object, and calling SaveChangesAsync. This will insert each record to the database one by one. In other words, each record requires one round trip to the database.

Output: EF Core Simple Approach

The results are as poor as you'd expect:

  • EF Core - Add one and save, for 100 users: 20 ms
  • EF Core - Add one and save, for 1,000 users: 260 ms
  • EF Core - Add one and save, for 10,000 users: 8,860 ms

I omitted the results with 100,000 and 1,000,000 records because they took too long to execute.

Dapper Simple Insert


using var connection = new SqlConnection(connectionString);
connection.Open();

const string sql =
    @"
    INSERT INTO Users (Email, FirstName, LastName, PhoneNumber)
    VALUES (@Email, @FirstName, @LastName, @PhoneNumber);
    ";

await connection.ExecuteAsync(sql, GetUsers());

Dapper is a simple SQL-to-object mapper for .NET. It allows us to easily insert a collection of objects into the database.

Output: Dapper Simple Insert

The results are much better than the initial example:

  • Dapper - Insert range, for 100 users: 10 ms
  • Dapper - Insert range, for 1,000 users: 113 ms
  • Dapper - Insert range, for 10,000 users: 1,028 ms

EF Core Add All and Save


using var context = new ApplicationDbContext();

foreach (var user in GetUsers())
{
    context.Users.Add(user);
}

await context.SaveChangesAsync();

However, EF Core still didn't throw in the towel. The first example was poorly implemented on purpose. EF Core can batch multiple SQL statements together, so let's use that.

Output: EF Core Add All and Save

Here are the benchmark results of this implementation:

  • EF Core - Add all and save, for 100 users: 2 ms
  • EF Core - Add all and save, for 1,000 users: 18 ms
  • EF Core - Add all and save, for 10,000 users: 203 ms

EF Core AddRange and Save


using var context = new ApplicationDbContext();

context.Users.AddRange(GetUsers());

await context.SaveChangesAsync();

This is an alternative to the previous example. Instead of calling Add for all objects, we can call AddRange and pass in a collection.

Output: EF Core AddRange and Save

The results are very similar to the previous example:

  • EF Core - Add range and save, for 100 users: 2 ms
  • EF Core - Add range and save, for 1,000 users: 18 ms
  • EF Core - Add range and save, for 10,000 users: 204 ms

EF Core Bulk Extensions


using var context = new ApplicationDbContext();

await context.BulkInsertAsync(GetUsers());

There's an awesome library called EF Core Bulk Extensions that we can use to squeeze out more performance.

Output: EF Core Bulk Extensions

The performance is equally amazing:

  • EF Core - Bulk Extensions, for 100 users: 1.9 ms
  • EF Core - Bulk Extensions, for 1,000 users: 8 ms
  • EF Core - Bulk Extensions, for 10,000 users: 76 ms

SQL Bulk Copy


using var bulkCopy = new SqlBulkCopy(ConnectionString);

bulkCopy.DestinationTableName = "dbo.Users";

bulkCopy.ColumnMappings.Add(nameof(User.Email), "Email");
bulkCopy.ColumnMappings.Add(nameof(User.FirstName), "FirstName");
bulkCopy.ColumnMappings.Add(nameof(User.LastName), "LastName");
bulkCopy.ColumnMappings.Add(nameof(User.PhoneNumber), "PhoneNumber");

await bulkCopy.WriteToServerAsync(GetUsersDataTable());

Last but not least, if we can't get the desired performance from EF Core, we can try using SqlBulkCopy.

Output: SQL Bulk Copy

However, the performance is blazing fast:

  • SQL Bulk Copy, for 100 users: 1.7 ms
  • SQL Bulk Copy, for 1,000 users: 7 ms
  • SQL Bulk Copy, for 10,000 users: 68 ms

Results and Takeaway

Here are the results for all the bulk insert implementations:

Method Size Speed

SqlBulkCopy holds the crown for maximum raw speed. However, EF Core Bulk Extensions deliver fantastic performance while maintaining the ease of use that Entity Framework Core is known for.

The best choice hinges on your project's specific demands:

  • If performance is all that matters, SqlBulkCopy is your solution.
  • If you need excellent speed and streamlined development, EF Core is a smart choice.

Comments

Post a Comment

Popular posts from this blog

IList vs ICollection vs IEnumerable

By
Image
https://www.linkedin.com/pulse/ienumerable-vs-icollection-idictionary-ilist-hasan-shahjahan/ What are IList, ICollection, and IEnumerable? Before we dive into the differences between these interfaces, let's first define what they are and what they do. IList: The IList interface is used to represent a collection of items that can be accessed by index. It is a more specific version of the ICollection interface, which means that it has all the same functionality as ICollection but with additional features that allow you to access items by their index in the list. ICollection: The ICollection interface is used to represent a collection of items that can be modified. It provides functionality for adding, removing, and counting items in the collection. IEnumerable:  The IEnumerable interface is used to represent a collection of items that can be iterated over. It provides functionality for iterating over the items in the collection but does not provide any functionality for modifying the...
Read more

Best practices for exception handling in C#”

By
Image
Mastering Exception Handling in C#: Best Practices Unveiled! Exception Handling in C#: Best Practices Unveiled! Exception handling is crucial for writing reliable and robust C# applications. Let's dive into the best practices along with code examples to understand how to handle exceptions effectively. 1. Catch Specific Exceptions: Always catch specific exceptions rather than catching generic exceptions like Exception . This allows for tailored handling of different types of exceptions. try { // Code that may throw specific exceptions } catch (FileNotFoundException ex) { // Handle file not found exception } catch (ArgumentException ex) { // Handle argument-related exception } catch (Exception ex) { // Catch any other exceptions Console.WriteLine($"Unhandled exception: {ex.Message}"); } 2. Use Finally Blocks for Cleanup: ...
Read more

1.Introduction to .NET Core and its architecture

By
Image
.NET Core is a free, open-source, cross-platform framework used for building modern, high-performance applications. It is a reimplementation of the .NET Framework and was first released in 2016. Since then, it has gained popularity among developers due to its flexibility, performance, and cross-platform support. The architecture of .NET Core The architecture of .NET Core is based on a modular design, which allows developers to choose the components they need for their applications. The framework is composed of several layers, each with its own set of functionality: The runtime layer : This layer includes the Common Language Runtime (CLR) and the .NET Standard Library. The CLR manages memory, garbage collection, and thread synchronization, while the .NET Standard Library provides a set of APIs common across all .NET implementations. The framework layer : This layer includes the Base Class Library (BCL), which provides a set of prebuilt classes for common programming tasks. It also inclu...
Read more