Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 Table Valued Parameters Performance


SQL Server 2008 Table Valued Parameters Performance

Author
Message
Scott Zurolo
Scott Zurolo
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 139
Comments posted to this topic are about the item SQL Server 2008 Table Valued Parameters Performance
Phil Factor
Phil Factor
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 2953
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
Simple Talk
finizi
finizi
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 36
And what about using sp_xml_preparedocument to parse XML ?
jude.pieries
jude.pieries
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 449
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
Florian Reischl
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 3934
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

And here are further articles from my blog:
Table-Valued Parameters - A Performance Comparison
Table-Valued Parameters

Hope this helps
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Florian Reischl
Florian Reischl
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 3934
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


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
einman33
einman33
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 512
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
WayneS
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7147 Visits: 10438
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Scott Zurolo
Scott Zurolo
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 139
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
Bradley Deem
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 1248
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search