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 3 of 3

«««1 2 3

SQL Server 2008 Table Valued Parameters Performance

Rate Topic

Display Mode

Topic Options

Author

Message

Scott Zurolo

Posted Saturday, July 10, 2010 9:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 11:41 AM
Points: 7, Visits: 139

Thank 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.

Post #950351

Slick84

Posted Monday, July 12, 2010 7:59 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163

Scott,

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,
S

--
Hehe

Post #950770

Scott Zurolo

Posted Monday, July 12, 2010 8:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 11:41 AM
Points: 7, Visits: 139

In 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,
UnitPrice
FROM PODetailtest

You may just need to scroll down to see it. I hope this helps clarify. Thanks for your interest.

Post #950779

Slick84

Posted Monday, July 12, 2010 8:14 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163

Scott,

I think the incorrectly named parameters confused me.

You used @TableParamExample and @ProcTableParamExample.

Anyhow, thanks.

Regards,
S

--
Hehe

Post #950782

j nuñez

Posted Thursday, July 15, 2010 5:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:46 AM
Points: 1,109, Visits: 279

A 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!

Post #953011

skra

Posted Wednesday, January 02, 2013 3:51 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 4:11 AM
Points: 711, Visits: 210

Hello,

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 application
II. executing a procedure with this XML as parameter

versus

I. preparing (populating with data) a DataTable on the application side
II. 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 regards

Post #1401744

« Prev Topic | Next Topic »

26 posts, Page 3 of 3

«««1 2 3

Permissions