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:

CREATE TYPE TestTableType AS TABLE 
(
    ID INT,
    Name NVARCHAR(100), 
    Description NVARCHAR(2000)
);
GO
CREATE proc [dbo].[Test_Table_Parameter]
    @Tbl TestTableType READONLY
as
    SELECT 'Return'
GO

Parameter as VARCHARs

CREATE proc [dbo].[Test_Simple_Parameter]
    @Var1 NVARCHAR(2000) = NULL,
    @Var2 NVARCHAR(2000) = NULL,
    @Var3 NVARCHAR(2000) = NULL,
    @Var4 NVARCHAR(2000) = NULL,
    @Var5 NVARCHAR(2000) = NULL,
    @Var6 NVARCHAR(2000) = NULL,
    @Var7 NVARCHAR(2000) = NULL,
    @Var8 NVARCHAR(2000) = NULL,
    @Var9 NVARCHAR(2000) = NULL,
    @Var10 NVARCHAR(2000) = NULL,
    @Var11 NVARCHAR(2000) = NULL,
    @Var12 NVARCHAR(2000) = NULL,
    @Var13 NVARCHAR(2000) = NULL,
    @Var14 NVARCHAR(2000) = NULL,
    @Var15 NVARCHAR(2000) = NULL,
    @Var16 NVARCHAR(2000) = NULL,
    @Var17 NVARCHAR(2000) = NULL,
    @Var18 NVARCHAR(2000) = NULL,
    @Var19 NVARCHAR(2000) = NULL,
    @Var20 NVARCHAR(2000) = NULL
AS
    SELECT 'Return'
GO

This is the method I use to connect to the database:

private static long GetScalarSp(string spName, SqlParameter[] parameters)
{
    object returnObj = null;
    Stopwatch watch = new Stopwatch();
    using(var conn = new SqlConnection(CONNECTION_STRING))
    {
        var command = new SqlCommand(spName, conn);
        command.Parameters.AddRange(parameters);
        command.CommandType = CommandType.StoredProcedure;
        conn.Open();
        
        watch.Start();
        returnObj = command.ExecuteScalar();
        watch.Stop();
    }
    return watch.ElapsedMilliseconds;
}

As you can see I only measure the time for the execution of the command. And these are the methods I use to instantiate my calls:

private long RunXml(int rows)
{
    var param = new SqlParameter();
    param.ParameterName = "@Xml";
    param.SqlDbType = SqlDbType.Xml;
    var sb = new StringBuilder();
    sb.Append(@"<Data>");
    var str = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" + DateTime.Now;
    for(var i = 0; i < rows; i++)
    {
        sb.AppendFormat(@"<Record ID=""{0}"" Name=""{0}"">{1}</Record>", i + 1, str);
    }
    sb.Append(@"</Data>");
    param.Value = sb.ToString();

    return GetScalarSp(@"Test_Xml_Parameter", new []{param});
}
private long RunSimple(int rows)
{
    var list = new List<SqlParameter>();
    var str = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" + DateTime.Now;
    for (var i = 0; i < rows; i++)
    {
        list.Add(new SqlParameter("@Var" + (i + 1), str));
    }

    return GetScalarSp(@"Test_Simple_Parameter", list.ToArray());
}
public long RunTable(int rows)
{
    var param = new SqlParameter();
    param.ParameterName = "@Tbl";
    param.SqlDbType = SqlDbType.Structured;
    var dt = new DataTable();
    var str = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" + DateTime.Now;

    dt.Columns.Add("ID", typeof (Int32));
    dt.Columns.Add("Name", typeof(string));
    dt.Columns.Add("Description", typeof(string));

    for (var i = 0; i < rows; i++)
    {
        dt.Rows.Add(new object[] {i + 1, (i + 1).ToString(), str });
    }

    param.Value = dt;

    return GetScalarSp(@"Test_Table_Parameter", new[] { param });
}

Then my test looks like that:

[Test]
public void Test_All()
{
    const int iterationsEach = 100;
    const int iterationsAll = 100;

    double xmlAvg = 0;
    double tableAvg = 0;
    double simpleAvg = 0;
    var rows = 20;
    for (var j = 0; j < iterationsAll; j++)
    {
        for (var i = 0; i < iterationsEach; i++)
        {
            tableAvg += this.RunTable(rows);
        }
        for (var i = 0; i < iterationsEach; i++)
        {
            simpleAvg += this.RunSimple(rows);
        }
        for (var i = 0; i < iterationsEach; i++)
        {
            xmlAvg += this.RunXml(rows);
        }
        Console.WriteLine("Cycle " + (j + 1));
    }
    
    xmlAvg = xmlAvg / (iterationsAll * iterationsEach);
    tableAvg = tableAvg / (iterationsAll * iterationsEach);
    simpleAvg = simpleAvg / (iterationsAll * iterationsEach);



    Console.WriteLine("XML:{0} {1}%", xmlAvg , (int)(xmlAvg * 100 / tableAvg));
    Console.WriteLine("Table:{0} {1}%", tableAvg, 100);
    Console.WriteLine("Simple:{0} {1}%", simpleAvg , (int)(simpleAvg * 100 / tableAvg));
}

And finally the results are as follow:

As you can see the most inefficient way is passing a table but the difference between plain varchars and XML is less than 10 % of the table parameter average execution time or between 10% and 20% of the XML parameter average execution time.

A follow up article: http://blog.bodurov.com/Performance-comparison-between-passing-and-querying-XML-vs-Table-parameters-in-a-SqlServer-2008-Stored-Procedure