SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 Table Valued Parameters Performance


SQL Server 2008 Table Valued Parameters Performance

Author
Message
Scott Zurolo
Scott Zurolo
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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.
Slick84
Slick84
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 1163
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
Scott Zurolo
Scott Zurolo
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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.
Slick84
Slick84
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 1163
Scott,

I think the incorrectly named parameters confused me.

You used @TableParamExample and @ProcTableParamExample.

Anyhow, thanks.

Regards,
S

--
Hehe
j nuñez
j nuñez
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1271 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!
skra
skra
SSC Eights!
SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)

Group: General Forum Members
Points: 840 Visits: 228
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search