Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Streaming rows of SQL Server data to a table valued parameter using a SQLDataReader


By:   |   Last Updated: 2011-03-30   |   Comments (5)   |   Related Tips: More > Table Valued Parameters

Problem

Before SQL Server 2008, passing multiple rows of data to a stored procedure was not a straightforward process. This involved additional steps such as creating an XML document from the data values and passing it to the procedure which inserts / updates the target data or bulk inserting to a temp table. Fortunately SQL 2008 offers the table valued parameters (TVPs), which makes our lives a little bit easier. In this tip I'll show you how to use a SqlDataReader to stream rows of data to a table-valued parameter from within a .NET client application. Support for TVPs exists beginning with Visual Studio 2008 and ADO.NET 3.5.

Solution

Task Description

Suppose you have to periodically update the list price and the sale price for some of the records in a product table using data from a staging table, which may be located on another SQL instance / server. (Download the code for this tip)

TSQL code

I used a database called "Test", with the recovery model set to "simple".

First of all, let's create the table type on which the table valued parameter relies.

CREATE TYPE [dbo].[MatrixData] AS TABLE(
    [ProductID] [int] NOT NULL,
    [Price] [money] NOT NULL,
    [SalePrice] [money] NOT NULL,
    PRIMARY KEY CLUSTERED ([ProductID] ASC));
    )

Then create the tables we will use. As you can see both the source and the target are indexed on the join column (ProductID). The ProductStaging table contains the source data (in our case ~1000 records) we'll use this to update the target ProductPrice table.

CREATE TABLE [dbo].[ProductStaging](
   [SKU] [varchar](50) NOT NULL,
   [ProductID] [int] NOT NULL,
   [Price] [money] NOT NULL,
   [SalePrice] [money] NOT NULL,
   CONSTRAINT [PK_ProductStaging_1] PRIMARY KEY CLUSTERED([ProductID] ASC)
   ) ON [PRIMARY];

In this scenario, we need to update the prices and sale prices stored in the ProductPrice table, which has the following structure. The ProductPrice table contains ~40.000 records.

CREATE TABLE [dbo].[ProductPrice](
   [ProductID] [int] NOT NULL,
   [Price] [money] NOT NULL,
   [SalePrice] [money] NULL,
   [Cost] [money] NULL,
   [Inventory] [int] NOT NULL DEFAULT 0,
   --Other columns here
   [UpdatedOn] [smalldatetime] NOT NULL DEFAULT GETDATE(),
   CONSTRAINT [PK_ProductPrice] PRIMARY KEY CLUSTERED([ProductID] ASC)
   ) ON [PRIMARY];

Here is the stored procedure which will take a parameter based on the MatrixData type. I'm also using another great feature SQL 2008 offers - the MERGE statement. In this example I'll only update the records that match between the source (ProductStaging table) and the target (ProductPrice table).

CREATE PROCEDURE [dbo].[DM_TVPUpdate] 
@srcData MatrixData READONLY
AS
SET NOCOUNT ON;
MERGE dbo.ProductPrice
USING @srcData AS Source
ON ProductPrice.ProductID = Source.ProductID
WHEN MATCHED
THEN UPDATE SET 
    ProductPrice.Price = Source.Price,
    ProductPrice.SalePrice = Source.SalePrice,
    ProductPrice.UpdatedOn = GETDATE();

.NET C# code

For simplicity I've used a basic console application which calls the price update routine. The .NET code below opens a SqlDataReader ("srcData") which selects from the ProductStaging table and passes this reader as a table valued parameter to the DM_TVPUpdate stored procedure.

Please note that the type of the "src" parameter is SqlDbType.Structured. This is, according to MSDN, "a special data type for specifying structured data contained in table-valued parameters."

public static void PriceUpdate(string retrieveSource, string connStr)
{
    using (SqlConnection conn = new SqlConnection(connStr), 
    conn_r = new SqlConnection(connStr))
    {
        conn.Open(); conn_r.Open();
        using (SqlCommand cmd = new SqlCommand("DM_TVPUpdate", conn), 
        cmd_r = new SqlCommand(retrieveSource, conn_r))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd_r.CommandType = CommandType.Text;
            using (SqlDataReader srcData = cmd_r.ExecuteReader())
            {
                SqlParameter src = cmd.Parameters.AddWithValue("@srcData", srcData);
                src.SqlDbType = SqlDbType.Structured;
                cmd.ExecuteNonQuery();
            }
        }
    }
}

You can use not only a SqlDatareader, but any object derived from DbDataReader - such as System.Data.OracleClient.OracleDataReader or System.Data.EntityClient.EntityDataReader - to stream rows of data to a table-valued parameter. System.Data.SqlClient also supports populating table-valued parameters from DataTable and System.Collections.Generic.IEnumerable objects.

The data you pass from the reader must match the TVP definition, i.e. the number of columns must be the same and the data types must be the same or be implicitly convertible.

In the main class of the console application you'll call the PriceUpdate routine like this:

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
static void Main(string[] args){
    string connStr = ConfigurationManager.AppSettings["DbConn"];
    string sourceDataTSQL = ConfigurationManager.AppSettings["sourceDataTSQL"];
    try{
        //...
        MyClass.PriceUpdate(sourceDataTSQL, connStr);
        //...
    }
    catch{
        //error trapping code here...
    }
    finally{
        // your code here...
    }
}

The parameter values are taken from the application's configuration file:

<CONFIGURATION>
<APPSETTINGS>
    <ADD value="data source=MyServer;initial catalog=Test;
    Integrated Security = SSPI" key="DBConn" />
    <ADD value="SELECT ProductID, Price, 
    SalePrice FROM ProductStaging" key="sourceDataTSQL" />
    <!--other values here-->
</APPSETTINGS>
</CONFIGURATION>

In this example both the staging table and the ProductPrice table are located in the same database; therefore I'm using only one connection string.

Under the Covers

To see what's happening behind the scenes, I've set up a server side trace which captures the TSQL SQL:BatchStarting event. As you can see in the picture below, the data from the reader is inserted into a table variable (@p2) having the MatrixData type. Afterwards @p2 is passed to the DM_TVPUpdate stored procedure as a parameter.

set up a server side trace which captures the tsql sql:batchstarting event

afterwards @p2 is passed to the dm_tvpu update stored procedure as a parameter

There are in our case ~1000 inserts to be done. If you try to use the profiler GUI and there are enough rows to insert, you may find out only that the trace "skipped rows".

To see the execution plan of the stored procedure which updates the price values, run this:

SELECT plan_handle, query_plan, objtype, dbid, objectid 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
WHERE objtype = 'Proc'
AND OBJECT_NAME(objectid, dbid) = 'DM_TVPUpdate';

Here is the execution plan "broken" in 2 pictures. The upper image represents the rightmost part of the plan:

the execution plan of the stored procedure
clustered index merge

The sys.dm_exec_query_stats DMV offers data regarding the logical reads and CPU time.

SELECT SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_physical_reads, qs.last_logical_reads,
qs.total_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_logical_reads DESC;

If you're using a table type for which the join column (ProductID) is not indexed, you'll obtain, for example, a bigger number of logical reads (on my machine: 4366 vs. 15) and a bigger worker time (on my machine: 24414 vs. 16601)

Please bear in mind that I worked in a test environment, with little additional workload, if any.

According to the documentation, TVPs are materialized as temporary tables in tempdb; therefore performance with large datasets should be good. When you pass a TVP to a stored procedure you pass in fact a reference to that temporary table. However you should consider that SQL Server does not maintain statistics on table-valued parameters, so thoroughly test this approach in your environment. Also you should monitor tempdb usage.

You can see the structure of the temp table by querying the sys.tables and sys.columns catalog views:

USE tempdb;
SELECT * FROM sys.tables;
SELECT * FROM sys.columns 
WHERE object_id IN 
(SELECT object_id FROM sys.all_objects WHERE type_desc = 'USER_TABLE');
Next Steps

You can download all of the code for this tip.

  • After downloading the zip file, restore the included database, open the solution in VS 2010 and change the connection string and log file location values in the App.config file according to your environment.
  • Program.cs is the "main" file. Trace.cs contains code regarding the error log file. Worker.cs contains the PriceUpdate method.
  • I used VS 2010; therefore you won't be able to open the solution with VS 2008. If you need to work with VS 2008, create a new "console application" project, copy the files I've sent in the new directory (Program.cs will be replaced) and open the solution. In the "solution explorer" pane right click the project name, choose Add -> Existing Item and add Worker.cs, Trace.cs and App.Config. Make sure that the namespace is OK (i.e. is the same as project name) in each .cs file.


Last Updated: 2011-03-30


get scripts

next tip button



About the author
MSSQLTips author Diana Moldovan Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, May 13, 2011 - 12:58:36 PM - Diana Moldovan Back To Top

Ben, the today's tip is about .NET Bulk Insert into SQL Server.


Monday, April 11, 2011 - 2:31:53 PM - Diana Back To Top

I've put this on my to-do list :)


Monday, April 11, 2011 - 10:14:51 AM - Ben Taylor Back To Top

Diana:

How about doing a similar topic using BulkCopy?


Monday, April 11, 2011 - 10:00:05 AM - Diana Back To Top

Thank you, Ben :)


Monday, April 11, 2011 - 9:26:30 AM - Ben Taylor Back To Top

Diana:

Nicely done. I'd like to see more writing from you.

Ben


Learn more about SQL Server tools