I’ve seen TVPs improve performance by orders of magnitude when used appropriately. This feature isn’t used as often as it should be so I’ll show how to implement a TVP using a C# application example and discuss TVP considerations.
Why Table-Valued Parameters Improve Performance
TVPs allow one to pass multiple rows or values at once to a query or stored procedure. By doing so, the query can leverage set-based operations to greatly improve performance compared to executing the query/proc many times. TVPs decrease network latency by reducing network round trips. Prior to TVPs, one had to employ workarounds like passing a string containing a delimited list of records (or XML) and parsing on the SQL side. Those techniques are at best a kludge. Instead TVPs are the right tool for the job in SQL Server 2008 and later.
TVPs (and table variables) are temp tables. TVPs and table variables are persisted in tempdb much the same way as regular temp tables (# prefix). The big differences are that TVPs and table variables are limited in scope to the current batch and do not have statistics. Also, table parameters/variables allow only indexes declared via primary key nor unique constraints; non-unique indexes are not supported.
When a TVP Is passed to SQL Server from a client application, the data is bulk-inserted into tempdb where it is made available to the SQL statement or stored procedure using the declared TVP. This bulk insert is handled by the client API transparently to the application and allows a large number of rows to be passed to SQL Server efficiently. In T-SQL, one can declare and load a table variable, where it can be used directly in a SQL statement or passed as a TVP to a stored procedure or query (invoked with sp_executesql).
The exact threshold where TVPs outperform single-row operations is much lower than you might expect. Your mileage may vary but, at least in my experience, it’s only a few rows. The performance tests I ran here show that the threshold where a TVP outperforms individual requests with a simple list of customers is only 4 rows. That said, I would not recommend TVPs for single-row operations due to the start-up overhead but it’s a good choice when the most common use case is multiple rows.
How to Use TVPs
In order to pass a TVP to a SQL statement or stored procedure, one must first create a user-defined table type in SQL Server. The table type defines the schema of the table SQL Server expects for the TVP. Listing 1 shows the CREATE DDL for the table type I use in the AdventureWorks2012 database to pass a list of customers to the stored procedure in Listing 2 that returns all orders for these customers. Note that the stored procedure TVP must be declared as READONLY.
Listing 1: Table type DDL
CREATE TYPE dbo.CustomerList AS TABLE( CustomerID int NOT NULL PRIMARY KEY );
Listing 2: Stored procedure to return orders for multiple customers
CREATE PROC dbo.usp_select_orders_for_multiple_customers @CustomerList dbo.CustomerList READONLY AS SELECT soh.CustomerID, soh.SalesOrderID FROM Sales.SalesOrderHeader AS soh JOIN @CustomerList AS cl ON cl.CustomerID = soh.CustomerID; RETURN @@ERROR;
TVPs are passed from a .NET application using parameter data type DbType.Structured. The actual parameter value can be an object of type DataTable, DbDataReader or IEnumberable<SqlDataRecord>. A DataTable is appropriate when the required TVP data is already in a DataTable object needed for other purposes. A DbDataReader is a good choice when the source data is the result of a query (note that any DbDataReader may be used, not just a SqlDataReader). For other cases, I recommend an IEnumerable<SqlDataRecord>. It is very easy to implement IEnumerable<SqlDataRecord>, especially if you already have an enumerable object like an array or collection containing the values you need to pass. See the code at the end of this article for an example class that implements IEnumerable<SqlDataRecord> for a TVP.
A benefit with DbDataReader and IEnumerable<SqlDataRecord> is that the source data can be streamed to SQL server without first loading all the TVP data into memory in either the client application or in SQL Server. This is a big consideration when a lot of data needs to be passed because you are not constrained by memory on ether the client or SQL Server side.
Listing 3 shows the stored procedure I use to compare performance with the TVP method. This proc takes only a single @CustomerID parameter so it must be called once for each customer.
Listing 3: Stored procedure to return orders for a single customer
CREATE PROC dbo.usp_select_orders_for_single_customer @CustomerID int AS SELECT soh.CustomerID, soh.SalesOrderID FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = @CustomerID; RETURN @@ERROR;
The complete C# console application I used for the performance tests is in Listing 4. The code demonstrates the 3 ways to pass a TVP from C# and logs timings for each invocation. As you can see in Figure 1, the TVP outperformed individual calls once more than 3 customers were passed. Note that I had to use a logarithmic y-axis scale because of the huge performance difference. For example, 10,000 individual calls averaged about 1.5 seconds whereas passing 10,000 customers via a TVP took less to 100 milliseconds.
Figure 1: TVP versus individual call performance
Listing 4: Passing TVPs in C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.SqlServer.Server;
using System.IO;
namespace TVPTest
{
class Program
{
private static string connectionString = @"Data Source=MyServer;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI";
private static StreamWriter logFile = new StreamWriter(@"TvpTest.log", false);
static void Main(string[] args)
{
// write column headers to log file
logFile.WriteLine("{0},{1},{2}"
, "Test Name"
, "Customers"
, "Durations");
// run each performance test 100 times
for (int i = 0; i < 100; ++i)
{
runPerformanceTest(1);
runPerformanceTest(2);
runPerformanceTest(3);
runPerformanceTest(4);
runPerformanceTest(5);
runPerformanceTest(10);
runPerformanceTest(25);
runPerformanceTest(50);
runPerformanceTest(100);
runPerformanceTest(500);
runPerformanceTest(1000);
runPerformanceTest(2500);
runPerformanceTest(5000);
runPerformanceTest(10000);
logFile.Flush();
}
logFile.Close();
Console.WriteLine("Press any key to close.");
Console.ReadKey();
}
private static void runPerformanceTest(int customerCount)
{
Console.WriteLine("{0} customers:", customerCount);
DataTable orderDataTable;
Stopwatch sw;
DateTime duration;
//individual call baseline
sw = Stopwatch.StartNew();
orderDataTable = getOrdersForCustomersIndividually(customerCount);
sw.Stop();
duration = new DateTime(sw.Elapsed.Ticks);
Console.WriteLine("\tIndividual call: {0} orders returned, duration is {1}"
, orderDataTable.Rows.Count, duration.ToString("HH:mm:ss.ffffff"));
logFile.WriteLine("{0},{1},{2}"
, "Individual call"
, customerCount
, duration.ToString("HH:mm:ss.ffffff"));
//TVP from DataTable
sw = Stopwatch.StartNew();
orderDataTable = getOrdersForCustomersUsingTvp(getCustomerListDataTable(customerCount));
sw.Stop();
duration = new DateTime(sw.Elapsed.Ticks);
Console.WriteLine("\tTVP DataTable: {0} orders returned, duration is {1}"
, orderDataTable.Rows.Count, duration.ToString("HH:mm:ss.ffffff"));
logFile.WriteLine("{0},{1},{2}"
, "TVP DataTable"
, customerCount
, duration.ToString("HH:mm:ss.ffffff"));
//TVP from DataReader
sw = Stopwatch.StartNew();
orderDataTable = getOrdersForCustomersUsingTvp(getCustomerListDataReader(customerCount));
sw.Stop();
duration = new DateTime(sw.Elapsed.Ticks);
Console.WriteLine("\tTVP DataReader: {0} orders returned, duration is {1}"
, orderDataTable.Rows.Count, duration.ToString("HH:mm:ss.ffffff"));
logFile.WriteLine("{0},{1},{2}"
, "TVP DataReader"
, customerCount
, duration.ToString("HH:mm:ss.ffffff"));
//TVP from IEnumerable
sw = Stopwatch.StartNew();
orderDataTable = getOrdersForCustomersUsingTvp(getCustomerListSqlDataRecords(customerCount));
sw.Stop();
duration = new DateTime(sw.Elapsed.Ticks);
Console.WriteLine("\tTVP IEnumerable: {0} orders returned, duration is {1}"
, orderDataTable.Rows.Count, duration.ToString("HH:mm:ss.ffffff"));
logFile.WriteLine("{0},{1},{2}"
, "TVP IEnumerable"
, customerCount
, duration.ToString("HH:mm:ss.ffffff"));
Console.WriteLine();
}
///
/// Call proc individually for each customer
///
/// Number of customers to return
/// DataTable of customer orders
private static DataTable getOrdersForCustomersIndividually(int customerCount)
{
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(@"dbo.usp_select_orders_for_single_customer", connection))
{
command.CommandType = CommandType.StoredProcedure;
var customerIdParameter = command.Parameters.Add("@CustomerID", System.Data.SqlDbType.Int);
var orderListDataAdapter = new SqlDataAdapter(command);
var orderDataTable = new DataTable();
for (int customerId = 11000; customerId < 11000 + customerCount; ++customerId)
{
customerIdParameter.Value = customerId;
orderListDataAdapter.Fill(orderDataTable);
}
return orderDataTable;
}
}
///
/// Call proc with TVP of CustomersIDs
///
/// List of customers
/// DataTable of customer orders
private static DataTable getOrdersForCustomersUsingTvp(Object customerList)
{
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(@"dbo.usp_select_orders_for_multiple_customers", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CustomerList", System.Data.SqlDbType.Structured).Value = customerList;
var orderListDataAdapter = new SqlDataAdapter(command);
var orderDataTable = new DataTable();
orderListDataAdapter.Fill(orderDataTable);
return orderDataTable;
}
}
///
/// Create a DataTable containing list of customers for TVP
///
/// Number of customers to return
/// DataTable with single CustomerID column
private static DataTable getCustomerListDataTable(int customerCount)
{
var customerListDataTable = new DataTable();
customerListDataTable.Columns.Add("CustomerID", typeof(int));
for (int customerId = 11000; customerId < 11000 + customerCount; ++customerId)
{
var row = customerListDataTable.NewRow();
customerListDataTable.Rows.Add(row);
row[0] = customerId;
}
return customerListDataTable;
}
///
/// Create a DataReader containing list of customers for TVP
///
/// Number of customers to return
/// DataReader with single CustomerID column
private static IDataReader getCustomerListDataReader(int customerCount)
{
var connection = new SqlConnection(connectionString);
var command = new SqlCommand(@"SELECT CustomerID FROM Sales.Customer WHERE CustomerID BETWEEN 11000 AND 11000 + @CustomerCount - 1;", connection);
command.Parameters.Add("@CustomerCount", System.Data.SqlDbType.Int).Value = customerCount;
connection.Open();
command.CommandType = CommandType.Text;
return command.ExecuteReader();
}
///
/// Create an IEnumerable<SqlDataRecord> object containing list of customers for TVP
///
/// Number of customers to return
/// IEnumerable<SqlDataRecord> with single CustomerID column
private static IEnumerable getCustomerListSqlDataRecords(int customerCount)
{
var customerList = new List();
for (int customerId = 11000; customerId < 11000 + customerCount; ++customerId)
{
customerList.Add(customerId);
}
return new CustomerListSqlDataRecords(customerList);
}
///
/// Implement IEnumerable<SqlDataRecord> to return a SqlDataRecord for each CustomerID
/// in the provided IEnumerable<int>
///
private class CustomerListSqlDataRecords : IEnumerable
{
private SqlMetaData metaData = new SqlMetaData("CustomerID", SqlDbType.Int);
private IEnumerable customerList;
public CustomerListSqlDataRecords(IEnumerable customerList)
{
this.customerList = customerList;
}
public IEnumerator GetEnumerator()
{
foreach (var customerId in customerList)
{
var record = new SqlDataRecord(metaData);
record.SetInt32(0, customerId);
yield return record;
}
}
System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
{
throw new NotImplementedException();
}
}
}
}