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:

IF EXISTS(select * from sys.procedures WHERE name = 'Test_Table_Parameter')
    DROP PROCEDURE [Test_Table_Parameter]
GO
IF EXISTS(SELECT * FROM sys.types WHERE name = 'TestTableType')
    DROP TYPE TestTableType
GO
CREATE TYPE TestTableType AS TABLE 
(
    ID INT,
    Name NVARCHAR(100), 
    Type int,
    Description NVARCHAR(2000)
);
GO
CREATE PROCEDURE [dbo].[Test_Table_Parameter]
    @Tbl TestTableType READONLY
AS
    SELECT 
        [Type],
        [CountAll] = COUNT(*),
        [MaxID] = MAX([ID]),
        [MinID] = MIN([ID])
    FROM @Tbl
    WHERE ID > 5
    GROUP BY [Type]
    ORDER BY [Type] DESC
GO


IF EXISTS(select * from sys.procedures WHERE name = 'Test_Xml_Parameter')
    DROP PROCEDURE Test_Xml_Parameter
GO
CREATE PROCEDURE [dbo].[Test_Xml_Parameter]
    @Xml XML
as
    SELECT 
        [Type],
        [CountAll] = COUNT(*),
        [MaxID] = MAX([ID]),
        [MinID] = MIN([ID])
    FROM (
            SELECT 
                [ID] = VIRT.node.value('@ID', 'int'),
                [Type] = VIRT.node.value('@Type', 'int')
            FROM @Xml.nodes('/Data/Record') as VIRT(node)
        ) T
    WHERE ID > 5
    GROUP BY [Type]
    ORDER BY [Type] DESC
GO

As you can see I have where clause, grouping, three aggregation function and ordering. I have changed my executors to include a random variable Type that we can use to group the records.

private 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("Type", typeof(Int32));
    dt.Columns.Add("Description", typeof(string));

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

    param.Value = dt;

    return GetScalarSp(@"Test_Table_Parameter", new[] { param });
}
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}"" Type=""{2}"">{1}</Record>", i + 1, str, Random(2, 10));
    }
    sb.Append(@"</Data>");
    param.Value = sb.ToString();

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

The rest is just as in the previous test. And here is the table with my results:

The picture is quite clear. When I was passing 10 rows more efficient was the procedure with XML parameter, taking about 0.8 of the time needed to run the procedure with Table parameter. When I increased the number of rows to 50 their performance was about the same. And when I used 100 rows the clear wined was the procedure with the table parameter with about 25 percent better performance. So the conclusion is: the more data you pass to a stored procedure the more it make sense to do it through a table parameter.