Speed Benchmark — Table Valued Parameter

In the previous article below, I covered the performance benchmark between Dapper and Entity Framework Core. Dapper was much faster to insert records, and now I am curious if I can make it even faster using Microsoft SQL’s Table Valued Parameter. Let me share with you what I found out using AdventureWorks Database.

Speed Benchmark — Dapper vs EF Core 3

By the way, this is the code I used when I inserted a list of Product object using Dapper in the previous article. I’m comparing this code to what I’ll describe below.

Overview

Table Valued Parameter is basically a way to pass DataTable as a parameter in your stored procedure, instead int, string, datetime, etc. In this case, I’ll create a list of Product in DataTable, and pass it to spInsertProducts stored procedure. There are only 4 steps to achieve this goal.

1. Create Table Type in SQL.
2. Create a Stored Procedure in SQL.
3. Create a DataTable in C#
4. Execute a Stored Procedure in C# using Dapper

Step 1. Create Production.NewProductType Table Type. It will show up in Programmability -> Types -> User-Defined Table Types

Step 2. Create Production.sp_CreateProducts Stored Procedure. Please note that ‘@newProducts’ parameter’s type is Production.NewProductType which is created in step 1. Also, we need READONLY.

In SELECT statement, FROM is ‘@newProducts’ since records we want to insert are passed to this stored procedure

Step 3 & 4, Create a list of Product data in DataTable, and execute the stored procedure created in step 2 using Dapper.

Result was astonishing. Table Valued Parameter was much faster!! It took about 5 seconds to insert a list of 20,000 product. However, with Table Valued Parameter & Stored Procedure, it took less than 1 second!!! When compared to EF Core, the result is even more significant.