InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 02:51:38 GMT20http://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxHello,Did You make any test which would measure the whole operation performance (CPU and memopry usage, IO reads and execution time):I. preparing XML in an external applicationII. executing a procedure with this XML as parameterversusI. preparing (populating with data) a DataTable on the application sideII. executing a procedure with this table as parameter (which also includes sending the whole data table through the network connection to the server and inserting rows to the table on the server side [II-B])?Time and resources used in the step II-B may be significant.With best regardsWed, 02 Jan 2013 03:51:12 GMTskrahttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxA lot of thanks to everybody for your contribution on this subject.This is a problem we has in our applications and SQL Server 2000.We are now considering the upgrade to SQL Server 2008.We will make a extensive analysis of performance on this kind of solution.Again, a lot of thanks!Thu, 15 Jul 2010 05:32:23 GMTj nuñezhttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxScott,I think the incorrectly named parameters confused me.You used @TableParamExample and @ProcTableParamExample.Anyhow, thanks.Regards,SMon, 12 Jul 2010 08:14:47 GMTSlick84http://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxIn the article right below the declaration of the UpdateTableViaTableValuedParameter stored procedure I populated the table valued parameter with data from a table named PODetailtest:DECLARE @ProcTableParamExample PODetailTableType INSERT INTO @ProcTableParamExample SELECT TOP 750 PurchaseOrderDetailID, OrderQty, UnitPriceFROM PODetailtest You may just need to scroll down to see it. I hope this helps clarify. Thanks for your interest.Mon, 12 Jul 2010 08:07:34 GMTScott Zurolohttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxScott,Did you decide to skip the section where you populated the Table Valued Parameter? In the script you create it, but I dont see where its populated? Please let us know.Thanks,SMon, 12 Jul 2010 07:59:57 GMTSlick84http://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxThank you for your input. The primary benefit of table valued parameters in my example is eliminating the overhead of multiple calls to the database. For each call there is overhead from ADO.net to set up the connection and configure the parameters and procedure call. Table valued parameters help reduce the repetitive creation of the ADO.net object plumbing for each call. For a true bulk insert operation, this feature may not be the optimal solution. Like most tools in the SQL Server toolbox table valued parameters may be a good fit in certain scenerios. I am using in production, stored procedures with table valued parameters on a database that is part of transactional replication. I have not seen any adverse effect on latency. It is a good thought to concider what side effects a new technology can have on something like replication. Thanks.Sat, 10 Jul 2010 09:24:39 GMTScott Zurolohttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxI’m sorry I don’t have a real example but I know when I see trouble at a distance Please don’t get me wrong, it’s not that I don’t like this feature but just that this will not be effective for a large table which expects 80-90 % read operation with “read committed”.Updates like this will not help replication (transaction replication ) as well, if I remember right transactions may get stuck at the distributor and may even cause deadlocks at the subscriber. Granular the transaction the more success it have of completing without causing significant pain to DB.Let me know if you want to take this conversation offline ; would love to share what I knowSat, 10 Jul 2010 02:42:44 GMTjude.pierieshttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxGreat article! Phil this technique is great when used with merge. Another thing to consider is the use of tvp's for optional parameter queries.Fri, 09 Jul 2010 20:38:12 GMTemmchildhttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxThank you. It is true. As the size of your data row grows, the performance boost from table valued parameters may decrease. If the data passed in contains several large fields such as varchar(Max), the single row insert approach may perform better. Thanks for pointing that out.Fri, 09 Jul 2010 09:20:30 GMTScott Zurolohttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxHi Scott[quote][b]m.scott.bryant (7/9/2010)[/b][hr]... rows of relatively long text... [/quote]Probably you want to have a look for the new SQL Server 2008 WRITE() function of VARCHAR(MAX) and VARBINARY(MAX):[u][url=http://florianreischl.blogspot.com/2010/01/concatenation-of-text-and-binary-data.html]Concatenation of Text and Binary Data in T-SQL[/url][/u]GreetsFloFri, 09 Jul 2010 09:20:29 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxScott - Thanks for the article. Reaffirms some of the information I found while investigating this option for some of our performance improvements.One other thing I discovered through my testing is table valued parameters are not a good solution if you need to pass rows of relatively long text. We had one instance where we had 5 strings that needed passed into the stored procedure for each new row. Using Table Valued Parameters to pass the data showed a huge degradation in performance over alternative solutions. Passing numeric values in Table Valued Parameters showed a huge increase in performance.The result of my testing showed (as always) that you need to consider all the alternatives, but if you are passing purely numeric data it is an excellent solution. As you add varchar() and nvarchar() columns, you need to analyze the performance and consider alternatives.Fri, 09 Jul 2010 07:49:15 GMTm.scott.bryanthttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxSome 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 )ASSET NOCOUNT ONDECLARE @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.PurchaseOrderDetailIDEND TRYBEGIN CATCH SELECT @ErrMsg = 'An error occurred in stored procedure ' + ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1)END CATCHGOFri, 09 Jul 2010 01:54:53 GMTsteven-673538http://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspx:-)Thank you . I get new things again.Thu, 08 Jul 2010 19:10:18 GMTkinzenthttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxNice article. Thanks for sharing with us.Thu, 08 Jul 2010 13:12:14 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxThanks 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.Thu, 08 Jul 2010 09:29:10 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspx[quote][b]Bradley Deem (7/8/2010)[/b][hr]Florian, thanks for the blog links those were great.[/quote]Thanks for the feedback :-)Thu, 08 Jul 2010 08:52:40 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxHey 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.Thu, 08 Jul 2010 08:41:14 GMTBradley Deemhttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxIn 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 datatableSqlCommand Cmd = new SqlCommand( "UspInsertOrderDetails",conn);Cmd.CommandType = CommandType.StoredProcedure;SqlParameter Param = Cmd.Parameters.AddWithValue( "@OrderDetails", OrderDetailsDT);Param.SqlDbType = SqlDbType.Structured;insertCommand.ExecuteNonQuery();Thu, 08 Jul 2010 07:56:09 GMTScott Zurolohttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxExcellent 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.Thu, 08 Jul 2010 07:41:06 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspx[quote][b]jude.pieries (7/8/2010)[/b][hr]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 acceptableWatch 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[/quote]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.Thu, 08 Jul 2010 07:08:17 GMTeinman33http://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxHi finizi[quote][b]finizi (7/8/2010)[/b][hr]And what about using sp_xml_preparedocument to parse XML ?[/quote]Is marked as deprecated since SQL Server 2005 and should not be used any more. Further, [ul][li]The old SQL Server 2000 XML functions have been restricted to [/li][li]Old XML features have been provided over extended procedures (like sp_xml_preparedocument). New XML support is a native feature of SQL Server[/li][li]New XML features should be much faster than old once.[/li][li]Old once have been restricted to 8000 characters[/li][/ul]GreetsFloThu, 08 Jul 2010 06:38:08 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxHi ScottNice article about a new technology that is way too less used till now (in my opinion).[b]@Phil_Factor[/b]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&lt;SqlDataRecord&gt; (my preference).Here's an article from SSC:[u][url=http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/]Streaming Data Into SQL Server 2008 From an Application[/url][/u]And here are further articles from my blog:[u][url=http://florianreischl.blogspot.com/2009/11/table-valued-parametes-performance.html]Table-Valued Parameters - A Performance Comparison[/url][/u][u][url=http://florianreischl.blogspot.com/2010/07/table-valued-parameters.html]Table-Valued Parameters[/url][/u]Hope this helpsFloThu, 08 Jul 2010 05:20:48 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxUsing 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 acceptableWatch 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.aspxThu, 08 Jul 2010 03:45:14 GMTjude.pierieshttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxAnd what about using sp_xml_preparedocument to parse XML ?Thu, 08 Jul 2010 01:53:11 GMTfinizihttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxThanks 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.Thu, 08 Jul 2010 01:39:58 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic948982-2737-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/TVP/70500/">SQL Server 2008 Table Valued Parameters Performance</A>[/B]Wed, 07 Jul 2010 22:04:46 GMTScott Zurolo