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 Saturday, July 10, 2010 9:24 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 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
Posted Monday, July 12, 2010 7:59 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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


--
Post #950770
Posted Monday, July 12, 2010 8:07 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 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
Posted Monday, July 12, 2010 8:14 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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


--
Post #950782
Posted Thursday, July 15, 2010 5:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Wednesday, January 2, 2013 3:51 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:08 AM
Points: 711, Visits: 218
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 »

Add to briefcase «««123

Permissions Expand / Collapse