Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Server 2008 Table Valued Parameters Performance Expand / Collapse
Author
Message
Posted Thursday, July 8, 2010 8:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Bradley Deem (7/8/2010)
Florian, thanks for the blog links those were great.

Thanks for the feedback



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 #949360
Posted Thursday, July 8, 2010 9:29 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: Today @ 3:30 AM
Points: 587, Visits: 2,533
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
Simple Talk
Post #949390
Posted Thursday, July 8, 2010 1:12 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 17,654, Visits: 15,506
Nice article. Thanks for sharing with us.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #949549
Posted Thursday, July 8, 2010 7:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 21, 2012 1:44 AM
Points: 25, Visits: 289

Thank you . I get new things again.


---------------------------------------
Thorn Bird...
Post #949675
Posted Friday, July 9, 2010 1:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:25 AM
Points: 1, Visits: 64
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
Post #949767
Posted Friday, July 9, 2010 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 30, 2012 8:41 AM
Points: 1, Visits: 49
Scott - 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.
Post #949961
Posted Friday, July 9, 2010 9:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi Scott

m.scott.bryant (7/9/2010)
... rows of relatively long text...

Probably you want to have a look for the new SQL Server 2008 WRITE() function of VARCHAR(MAX) and VARBINARY(MAX):
Concatenation of Text and Binary Data in T-SQL

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 #950030
Posted Friday, July 9, 2010 9:20 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
Thank 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.
Post #950031
Posted Friday, July 9, 2010 8:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 42, Visits: 352
Great article! Phil this technique is great when used with merge. Another thing to consider is the use of tvp's for optional parameter queries.


Post #950292
Posted Saturday, July 10, 2010 2:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:24 PM
Points: 36, Visits: 308
I’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 know
Post #950328
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse