Introduction

Performing bulk updates from a client application is a common function in many systems. A typical example would be bulk update functionality in an order management system with a common scenario being updating multiple order detail rows in a data grid at once. The user needs to update the price and quantity for 250 order line items. The user selects all of the records to update and changes the price and quantity field. The user then hits the submit button and the data is then updated one row at a time as a single transaction. The application loops through each row one by one calling an update stored procedure. The stored procedure is executed 250 times one for each row in the data grid. This would mean 250 separate calls to the database server. It would be more efficient to be able to perform this action as one call to the database as a single batch update.

The ability to pass in an array or table as a parameter to a stored procedure has been a long sought-after feature in SQL Server. Prior to SQL Server 2008 this was possible to some extent using XML. The client application would create an XML representation of the data and pass it into a stored procedure as a single XML parameter. SQL Server 2008 gives us a more viable solution to this problem with table valued parameters. Table valued parameters allows us to pass a table as a single parameter to a stored procedure.

I examined the performance of each approach using SET STATISTICS IO, SET STATISTICS TIME and examining the execution plan. Each stored procedure was run several times to ensure that the plan was in cache. The first approach I used was to pass the data into the stored procedure as an XML parameter and update the target table directly. Here is the stored procedure I used.

CREATE PROCEDURE [dbo].[UpdateTableViaXML]
 (
 @Data xml 
 )
AS
SET NOCOUNT ON
DECLARE @ErrMsg varchar(100),
 @ErrNo int,
 @ErrSeverity varchar(100) 
BEGIN TRY
 UPDATE PODetailTest 
 SET OrderQty = T.PO.query('OrderQty').value('.', 'smallint') ,
 UnitPrice = T.PO.query('UnitPrice').value('.', 'money')
 FROM @Data.nodes('Root/PurchaseOrderDetail') AS T(PO) 
 INNER JOIN PODetailTest t1 ON T.PO.query('PurchaseOrderDetailID').value('.', 'int') = t1.PurchaseOrderDetailID
END TRY
BEGIN CATCH
 SELECT @ErrMsg = 'An error occurred in stored procedure ' + ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE(),
 @ErrSeverity = ERROR_SEVERITY()
 RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

I created some test data and executed the stored procedure:

SET STATISTICS IO ON
GO
SET STATISTICS TIME on
GO
--Create Some Test Data
DECLARE @data XML
SET @data = (
 SELECT TOP 750 [PurchaseOrderDetailID],
                [DueDate],
                [OrderQty] + 25 as OrderQty,
                [UnitPrice] + 25 as UnitPrice
 from Purchasing.PurchaseOrderDetail 
 FOR XML PATH('PurchaseOrderDetail'), ROOT ('Root'), ELEMENTS
 )
GO
EXEC UpdateTableViaXML @data

My results were as follows:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'PODetailtest'. Scan count 0, logical reads 1500, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 2, logical reads 108526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 7938 ms, elapsed time = 8087 ms.

It took 108526 logical reads on the work table used to transform the XML into relational data and 7938 ms in CPU time. Looking at the execution plan SQL Server is executing an XMLReader table valued function for each column that is being translated from XML. This is the costliest operation in the query plan and the main performance bottleneck.

Another way is to load parse the XML data into a temp table fist and then update the target table joining the temp table with the source table:

CREATE PROCEDURE [dbo].[UpdateTableViaXMLTempTable]
 (
 @Data xml 
 )
AS
SET NOCOUNT ON
DECLARE @ErrMsg varchar(100),
 @ErrNo int,
 @ErrSeverity varchar(100) 
BEGIN TRY
 CREATE TABLE #Temp (
 PurchaseOrderDetailID int NOT NULL PRIMARY KEY,
 OrderQty smallint NOT NULL,
 UnitPrice money NOT NULL
 )
 INSERT INTO #Temp
 SELECT T.customer.query('PurchaseOrderDetailID').value('.', 'int') AS PurchaseOrderDetailID,
 T.customer.query('OrderQty').value('.', 'smallint') AS OrderQty,
 T.customer.query('UnitPrice').value('.', 'money') AS UnitPrice
 FROM @data.nodes('Root/PurchaseOrderDetail') AS T(customer) 
 UPDATE PODetailTest
 SET 
 OrderQty = #Temp.OrderQty,
 UnitPrice = #Temp.UnitPrice
 FROM #Temp
 INNER JOIN PODetailTest t1 on #Temp.PurchaseOrderDetailID = t1.PurchaseOrderDetailID
END TRY
BEGIN CATCH
 SELECT @ErrMsg = 'An error occurred in stored procedure ' + ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE(),
 @ErrSeverity = ERROR_SEVERITY()
 RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
GO
EXEC UpdateTableViaXMLTempTable @data

The actual target table update was much more efficient but the loading of the temp table from the XML was not. SQL Server still had to use the XMLReader table valued function for each column. The overall performance of the previous solution was slightly better. It appears again that the shredding of the XML into a table format is not very efficient.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Table '#Temp_______________________________________________________________________________________________________________0000000001AA'. Scan count 0, logical reads 752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 3, logical reads 162789, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 11719 ms, elapsed time = 11991 ms.

Table 'PODetailtest'. Scan count 1, logical reads 1509, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#Temp_______________________________________________________________________________________________________________0000000001AA'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 12 ms.

The last solution used SQL Server 2008 table valued parameters. Before using table valued parameters in a stored procedure there are some restrictions to be aware of:

• The type is declared as read only thus you cannot modify it within the stored procedure.
• A table type cannot be altered using ALTER TABLE. To modify a table type, it must be dropped and recreated.

To use a table valued parameter several things need to be in place first. The first step to create Table Valued Parameter is to create a "user defined table type" This type describes the structure of the table variable's data. The DDL is like this:

CREATE TYPE PODetailTableType AS TABLE (
 PurchaseOrderDetailID int NOT NULL,
 OrderQty smallint NOT NULL,
 UnitPrice money NOT NULL,
 PRIMARY KEY (PurchaseOrderDetailID)
 )
GO

Once the table type is created it can then be used in a stored procedure.

CREATE PROCEDURE [dbo].[UpdateTableViaTableValuedParameter]
 (
 @TableParamExample PODetailTableType READONLY
 )
AS
SET NOCOUNT ON
DECLARE @ErrMsg varchar(100),
 @ErrNo int,
 @ErrSeverity varchar(100) 
BEGIN TRY
 UPDATE PODetailTest
 SET OrderQty = t.OrderQty,
 UnitPrice = t.UnitPrice
 FROM @TableParamExample t 
 INNER JOIN PODetailTest t1 ON t.PurchaseOrderDetailID = t1.PurchaseOrderDetailID
END TRY
BEGIN CATCH
 SELECT @ErrMsg = 'An error occurred in stored procedure ' + ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE(),
 @ErrSeverity = ERROR_SEVERITY()
 RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
GO
DECLARE @ProcTableParamExample PODetailTableType 
INSERT INTO @ProcTableParamExample 
SELECT TOP 750 PurchaseOrderDetailID,
                                 OrderQty,
                                 UnitPrice
FROM PODetailtest 
EXEC UpdateTableViaTableValuedParameter @ProcTableParamExample

Notice that in the declaration of the table type is done with the READONLY keyword. This is required for declaring a table valued parameter in a stored procedure. If you need to modify the parameter you will need to dump the contents of the variable into a temp table or table variable. Now let's run this stored procedure and compare the performance:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'PODetailtest'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#2DF1BF10'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 4 ms.

The performance was much better than either of the XML methods. The resource-intensive step of translating the XML into relational data is bypassed.

Conclusion

Table valued parameters are one of the most useful T-SQL enhancements in SQL Server 2008. If you have applications that are using XML to facilitate this type of functionality or are developing an application that has this functionality as one of its requirements, you can gain a significant performance boost using table valued parameters.