SQL Server 2008 Table Valued Parameters Performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Scott Zurolo » SQL Server 2008 Table Valued Parameters...

26 posts, Page 2 of 3

««1 2 3 »»

SQL Server 2008 Table Valued Parameters Performance

Rate Topic

Display Mode

Topic Options

Author

Message

Florian Reischl

Posted Thursday, July 08, 2010 8:52 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

Bradley Deem (7/8/2010)

Florian, thanks for the blog links those were great.

Thanks for the feedback Smile

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

Phil Factor

Posted Thursday, July 08, 2010 9:29 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, May 19, 2013 4:00 AM
Points: 533, Visits: 2,285

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

SQLRNNR

Posted Thursday, July 08, 2010 1:12 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

Nice article. Thanks for sharing with us.

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

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

kinzent

Posted Thursday, July 08, 2010 7:10 PM

SSC 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

steven-673538

Posted Friday, July 09, 2010 1:54 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:45 AM
Points: 1, Visits: 53

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

m.scott.bryant

Posted Friday, July 09, 2010 7:49 AM

Forum 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

Florian Reischl

Posted Friday, July 09, 2010 9:20 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, 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

Post #950030

Scott Zurolo

Posted Friday, July 09, 2010 9:20 AM

Forum 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

emmchild

Posted Friday, July 09, 2010 8:38 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:04 PM
Points: 34, Visits: 282

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

jude.pieries

Posted Saturday, July 10, 2010 2:42 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:58 PM
Points: 30, Visits: 210

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 »

26 posts, Page 2 of 3

««1 2 3 »»

Permissions