Make SQL Bulk Insert/Update Faster— Speed Benchmark : Temp Table vs Table-Valued Parameters

In this article, I would like to share three ways of bulk insert/update for 100,000 records data, 50,000 insert and 50,000 update. Either way, bulk insert/update can be completed within a few seconds.

We can implement bulk insert/update using Entity Framework easily by calling SaveChanges() at the end. However, since Entity Framework is very slow for bulk operation as shown the previous article, I hope this article will be helpful for someone who is looking for a way to make bulk operation faster.

The code example below uses Microsoft’s AdventureWorks database. Let’s get started.

A. Temp Table -> UPDATE & INSERT

This operation creates temp table using SqlCommand and run update and insert in the same connection. This is the overview.

1. Create temp table in C#
2. Bulk copy data into temp table
3. Join updating table with temp table and run update
4. Delete updated records from temp table
5. Insert what is left in temp table

Code

B. Table-Valued Parameter -> MERGE

This operation passes data to stored procedure using Table-Valued Parameter and run merge statement in the stored procedure. This is the overview.

1. Create Table Type in database
2. Create stored procedure which takes Table Type parameter and runs merge operation.
3. Run stored procedure from C#

Code

C. Table-Valued Parameter -> UPDATE & INSERT

This operation passes data to stored procedure using Table-Valued Parameter and run update & insert statement in the stored procedure, instead of merge. This is the overview.

1. Create Table Type in database
2. Create stored procedure which takes Table Type parameter and runs update and insert operation.
3. Run stored procedure from C#

Code

Performance Comparison

The performance test was done against database which already has around 400,000 records. When comparing 100,000 records bulk update/insert, the one with MERGE was a little slower, but it was completed within 3 seconds.

I preferred to use Temp Table Update/Insert since stored procedure did not improve performance, and it’s a little work to keep track of stored procedure and table type in source control and keep them in sync to database.

Also, the difference between Temp Table and Table-Valued Parameter is that the data in Table-Valued Parameter cannot be changed. With Temp Table, you can delete data once updated/inserted, it could make a difference when data size is bigger.

Warning

I did not take table lock into consideration in this example. It is important not to update/insert large batch data set in one shot in order to avoid deadlock.