Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Scott Zurolo » SQL Server 2008 Table Valued Parameters...
Add to briefcase
26 posts, Page 1 of 3
123»»»

SQL Server 2008 Table Valued Parameters Performance Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Scott Zurolo
Posted Wednesday, July 07, 2010 10:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 11:41 AM
Points: 7, Visits: 139
Comments posted to this topic are about the item SQL Server 2008 Table Valued Parameters Performance
Post #948982
Phil Factor
Posted Thursday, July 08, 2010 1:39 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:10 AM
Points: 535, Visits: 2,295
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
Post #949052
finizi
Posted Thursday, July 08, 2010 1:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 08, 2012 2:32 AM
Points: 1, Visits: 27
And what about using sp_xml_preparedocument to parse XML ?
Post #949057
jude.pieries
Posted Thursday, July 08, 2010 3:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:12 AM
Points: 30, Visits: 216
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
Post #949125
Florian Reischl
Posted Thursday, July 08, 2010 5:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #949182
Florian Reischl
Posted Thursday, July 08, 2010 6:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #949245
einman33
Posted Thursday, July 08, 2010 7:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, 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.
Post #949263
WayneS
Posted Thursday, July 08, 2010 7:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 6,388, Visits: 8,290
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
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
Post #949285
Scott Zurolo
Posted Thursday, July 08, 2010 7:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 11:41 AM
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();
Post #949296
Bradley Deem
Posted Thursday, July 08, 2010 8:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, June 17, 2013 10:18 AM
Points: 551, Visits: 1,153
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.
Post #949347
« Prev Topic | Next Topic »

Add to briefcase
26 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.