This is a follow up of my last performance related article where I compared the cost of passing XML vs. Table vs. plain Varchar parameters to a procedure without considering the cost of querying the data http://blog.bodurov.com/Performance-Test-for-the-cost-of-passing-parameters-to-a-SqlServer-2008-Stored-Procedure-as-XML-vs-Table-vs-Simple-Varchars.

In this test I added a query to the procedures to see how will the results look if we consider the query cost in the equation.

This is how my modified procedures look:

In this test I want to examine the performance implications of passing a relatively long set of data to a SQL Stored Procedure. I compare performance for passing that data as XML, as Table variable or a list of NVARCHAR variables. The test consists of 10 000 iterations. There is nothing in the procedure, so I am not testing the cost for querying the data but only the cost for passing the data to the SqlServer stored procedure. Following are my procedures.

Parameter as XML:

CREATE proc [dbo].[Test_Xml_Parameter]
    @Xml XML
as
    SELECT 'Return'
GO

Parameter as Table: