Over the past year, I've had to migrate multiple legacy applications to a new application. These new applications had their own database structure, so we needed to migrate the data from the legacy application to the new application.
With each migration, the quality of the code improved because of the experiences from the past migrations, and my overall knowledge.
Not only did the code quality improve, but there was also a very noticeable speed difference (in a good way) although the amount of data increased.
In this post, we'll incrementally refactor slow inserts into blazing fast inserts.
If you're only interested in the fastest technique, please navigate to SQL Bulk Copy.
We do this by inserting a collection of N customers (that have an id, a name, and some contact information) into a SQL table.
Manual insert for 1 customer: 10ms
Manual insert for 10 customers: 13ms
Manual insert for 100 customers: 39ms
Manual insert for 1_000 customers: 557ms
Manual insert for 10_000 customers: 17006ms
Manual insert for 100_000 customers: 23637ms
Manual insert for 1_000_000 customers: SKIPPED
Okay, that's already better but this has a drawback.
We have to escape the SQL command text manually, which doesn't look pretty.
And spoiler... we make it faster.
Don't forget to use the AddRange method, instead of iterating through the whole collection and adding the records one by one with the Add method. The AddRange method is significantly faster because it disables change detection automatically.
When we take a look at the generated SQL commands (by using SQL Extended Events) we notice that Entity Framework generates multiple SQL insert statements. Each statement inserts multiple customers at once, which seems to be the cause of our next speed gain.
We can notice that using Entity Framework, speeds up the inserts again.
I think this is because it inserts multiple records at once instead of record per record.
This can be verified by tweaking the Generating the SQL command text to reflect this way of working.
But we can quickly experience why Entity Framework chops the insert statements in batches.
When we try to insert more than 1000 records, SQL throws the error:
The number of row value expressions in the INSERT statement
exceeds the maximum allowed number of 1000 row values
We can work around this restriction, by using a Table Valued Parameter. For this solution, the first step is to create a User-defined Table Type.
We'll use (read, populate it with the customers) this table type later when we execute the SQL query.
Next, we must convert the customer list into a DataTable to be able to pass the data to the SQL query. Do not forget to assign the SQL parameter's type name to the name given to the above table type.
In the SQL query, we select all the customers assigned to the table type parameter and insert them into the customer table.
With this way, we execute one SQL query, which inserts all of the customers at once.
var cmdText =@" insert into dbo.Customers (Id, FirstName, LastName, Street, City, State, PhoneNumber, EmailAddress) select Id, FirstName, LastName, Street, City, State, PhoneNumber, EmailAddress from @customers";using(var connection =newSqlConnection(connectionString)){var command =newSqlCommand(cmdText, connection);var param = command.Parameters.AddWithValue("@customers",ToDataTable(customers)); param.TypeName ="dbo.CustomersTableType"; connection.Open();
command.ExecuteNonQuery();}
Results:
table-valued-parameter
Table Valued Parameter insert for 1 customer: 12ms
Table Valued Parameter insert for 10 customers: 13ms
Table Valued Parameter insert for 100 customers: 15ms
Table Valued Parameter insert for 1_000 customers: 49ms
Table Valued Parameter insert for 10_000 customers: 108ms
Table Valued Parameter insert for 100_000 customers: 2090ms
Table Valued Parameter insert for 1_000_000 customers: 12259ms
SQL has a built-in mechanism to import a large volume of data, called Bulk Insert. Luckily for us, dotnet supports a Bulk Insert with the SqlBulkCopy class.
Besides the visible performance advantage over the other solutions, we can also easily tweak the behavior with some Options.
To use the SqlBulkCopy, we need to create a new instance of the class and provide the SQL destination table.
When we write the customer list into the customers table, with the WriteToServer method on the SqlBulkCopy class, the method expects the collection to be a DataTable.
using(var copy =newSqlBulkCopy(connectionString)){
copy.DestinationTableName ="dbo.Customers";// Add mappings so that the column order doesn't matter
copy.ColumnMappings.Add(nameof(Customer.Id),"Id");
copy.ColumnMappings.Add(nameof(Customer.FirstName),"FirstName");
copy.ColumnMappings.Add(nameof(Customer.LastName),"LastName");
copy.ColumnMappings.Add(nameof(Customer.Street),"Street");
copy.ColumnMappings.Add(nameof(Customer.City),"City");
copy.ColumnMappings.Add(nameof(Customer.State),"State");
copy.ColumnMappings.Add(nameof(Customer.PhoneNumber),"PhoneNumber");
copy.ColumnMappings.Add(nameof(Customer.EmailAddress),"EmailAddress");
copy.WriteToServer(ToDataTable(customers));}
When we take another look at what this does under the hood (with SQL Extended Events), we notice that SqlBulkCopy generates the following SQL import statement.
We can see that there are multiple tactics to make a bulk insert fast(er), and why a technique is faster than another.
Not that we always must use the fastest solution out there, but I think it's good to know the options that can be used in different scenarios. For smaller collections, it's probably good enough to use the solution that offers the best developer's ergonomics.
By using a SQL Bulk Copy in our migration, we've won many hours that would otherwise be wasted while waiting on a migration run to complete.
If you know another way to insert a hundred-thousands of records in a fast way, feel free to send me a DM at @tim_deschryver.
I'd love to hear and learn from you.