SQL Server 2008 Table Valued Parameters Performance

Question

SQL Server 2008 Table Valued Parameters Performance

Scott Zurolo

SSC Enthusiast

Points: 117
More actions
July 7, 2010 at 10:04 pm

#224677

Comments posted to this topic are about the item SQL Server 2008 Table Valued Parameters Performance

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Phil Factor SSC-Insane Points: 20244 More actions · Answer 1

Thanks for that. An interesting read, and I like seeing performance figures.

In the non-XML solution you recommend, how does one get the bulk updates from the client application to the server, and into the input table in one database call? In your scenario you are '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.' I can see how to create the XML to do the update and call the stored procedure in the first two examples, but I don't understand how to stock the user defined table type with the data so easily. Would this be an ASP.NET bulk insert or a multiple-value insert? (INSERT INTO tempTable (FirstCol, SecondCol) VALUES (1,'First'), (2,'Second'), (3,'Third'), (4,'Fourth'), (5,'Fifth') ....) If this is the case, wouldn't the 'traditional' approach of using a temporary table be just as fast? Apologies in advance if I've missed something.

Best wishes,
Phil Factor

finizi Grasshopper Points: 15 More actions · Answer 2

And what about using sp_xml_preparedocument to parse XML ?

jude.pieries Default port Points: 1438 More actions · Answer 3

Using XML as a input parameter

-I wouldn’t use a XML as a input parameter to update a table; mainly to avoid the XML reader operation.

-This approach will lock the updating table for extensive period which is not acceptable

Watch out if you are attempting to update a large table using a table value parameter, the chances are that you will lock the updating table for a lengthy period of time causing longer transaction response times on your application which won’t be acceptable.

Some details on table –value parameters

http://msdn.microsoft.com/en-us/library/bb510489.aspx

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi Scott

Nice article about a new technology that is way too less used till now (in my opinion).

@Phil_Factor

To send data from .NET client side to a table-valued parameter, you can use either a DataTable (I don't like this due to its huge overhead) or a IEnumerable<SqlDataRecord> (my preference).

Here's an article from SSC:

Streaming Data Into SQL Server 2008 From an Application[/url]

And here are further articles from my blog:

Table-Valued Parameters - A Performance Comparison

Table-Valued Parameters

Hope this helps

Flo

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Hi finizi

finizi (7/8/2010)
And what about using sp_xml_preparedocument to parse XML ?

Is marked as deprecated since SQL Server 2005 and should not be used any more. Further,

The old SQL Server 2000 XML functions have been restricted to
Old XML features have been provided over extended procedures (like sp_xml_preparedocument). New XML support is a native feature of SQL Server
New XML features should be much faster than old once.
Old once have been restricted to 8000 characters

Greets

Flo

einman33 SSCrazy Points: 2813 More actions · Answer 6

jude.pieries (7/8/2010)
Using XML as a input parameter
-I wouldn’t use a XML as a input parameter to update a table; mainly to avoid the XML reader operation.
-This approach will lock the updating table for extensive period which is not acceptable
Watch out if you are attempting to update a large table using a table value parameter, the chances are that you will lock the updating table for a lengthy period of time causing longer transaction response times on your application which won’t be acceptable.
Some details on table –value parameters
http://msdn.microsoft.com/en-us/library/bb510489.aspx

Do you have an example of an update statement holding a table lock where the sole reason for the lock escalation was the use of the xml parsing? I have seen normal pessimistic locking behaving when shredding xml in the data tier for use in +1 crud scenarios.

WayneS SSC Guru Points: 95460 More actions · Answer 7

Excellent article. The only thing I think would have been nice to add (and which has been covered in this discussion forum) is how to pass the data into the table from the application.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Scott Zurolo SSC Enthusiast Points: 117 More actions · Answer 8

In the calling ASP.NET application you can create a ADO.NET datatable object and populate the datatable with the data rows from the grid. The datatable would be passed into the stored procedure as a single parameter. The parameter would be declared as DB type Structured. The code would look something like this:

DataTable OrderDetailsDT

//Add data to the datatable

SqlCommand Cmd = new SqlCommand( "UspInsertOrderDetails",conn);

Cmd.CommandType = CommandType.StoredProcedure;

SqlParameter Param = Cmd.Parameters.AddWithValue(

"@OrderDetails", OrderDetailsDT);

Param.SqlDbType = SqlDbType.Structured;

insertCommand.ExecuteNonQuery();

Bradley Deem SSCrazy Points: 2565 More actions · Answer 9

Hey Scott, thanks for the article. Florian, thanks for the blog links those were great.

I'm curious how the XML method would perform when used with XML Schema Collections. I'm guessing it'd be better, but not as good as TVP.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 10

Bradley Deem (7/8/2010)
Florian, thanks for the blog links those were great.

Thanks for the feedback 🙂

Phil Factor SSC-Insane Points: 20244 More actions · Answer 11

Thanks Scott, I must read up on my ADO.NET! Thanks too, Florian. I missed those blog posts. I must try it out. I can see this making a great difference to performance.

Best wishes,
Phil Factor

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Nice article. Thanks for sharing with us.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kinzent SSChasing Mays Points: 621 More actions · Answer 13

🙂

Thank you . I get new things again.

---------------------------------------
Thorn Bird...

Steven Bates Grasshopper Points: 17 More actions · Answer 14

Some of the poor performance is down to your xpath and the use of both .vaue and .query.

This is somewhat quicker. (Though it is never going to be as quick as using a table variable parameter)

ALTER PROCEDURE [dbo].[UpdateTableViaXML3]

(

@data xml

)

AS

SET NOCOUNT ON

DECLARE @ErrMsg varchar(100),

@ErrNo int,

@ErrSeverity varchar(100)

BEGIN TRY

UPDATE PODetailTest

SET OrderQty = T.PO.value('(OrderQty/text())[1]', 'smallint') ,

UnitPrice = T.PO.value('(UnitPrice/text())[1]','money')

FROM @data.nodes('Root/PurchaseOrderDetail') AS T(PO)

WHERE T.PO.value('(PurchaseOrderDetailID/text())[1]','int') = PODetailTest.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

Steven

SQL Unlimited - SQL Server Performance Tuning Consultants