The SP Benchmark code

All benchmarks are using the Northwind database on SqlServer. Northwind is shipped with every SqlServer installation as also the MSDE installation.

The Stored Procedure.
The stored procedure used is the following. Add this one to the Northwind database.

CREATE PROCEDURE pr_Orders_SelectMultiWCustomerEmployeeShipper
	@sCustomerID nchar(5),
	@iEmployeeID int,
	@iShipperID int
AS
SELECT 	*
FROM	Orders
WHERE	CustomerID = COALESCE(@sCustomerID, CustomerID)
	AND
	EmployeeID = COALESCE(@iEmployeeID, EmployeeID)
	AND
	ShipVia = COALESCE(@iShipperID, ShipVia)

The C# code.
The following code was used to run the benchmarks. There are two routines, one tests the dynamic query, the other tests the stored procedure. Call either one to see how they are performing. You have to change the server name in the connection string constant.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;

namespace Benchmarker
{
    public class Benchmarker
    {
        private const int   iAMOUNT_LOOPS=10000;
        private const string sEXAMPLE_CUSTOMERID="CHOPS";
        private const string sCONNECTION_STRING="data source=MyServer;initial catalog=Northwind;integrated security=SSPI;persist security info=False;packet size=4096";
        private const bool bPRINT_ROWCOUNT=false;

        public void Start()
        {
            BenchmarkSelfBuildQuery();
            //BenchmarkStoredProcedure();
            Console.ReadLine();
        }

        private void BenchmarkSelfBuildQuery()
        {
            Random rdmGenerator = new Random(unchecked((int)DateTime.Now.Ticks)); 

            Console.WriteLine("Dynamic query benchmark");
            DateTime daStartTime = DateTime.Now;
            Console.WriteLine("Benchmark started on: {0}.", daStartTime);
            int iMaxAmountRowsRetrieved = 0;
            for(int i=0;i<iAMOUNT_LOOPS;i++)
            {
                SqlString sCustomerID = SqlString.Null;
                SqlInt32 iEmployeeID = SqlInt32.Null;
                SqlInt32 iShipperID = SqlInt32.Null;

                // determine random value to check which values should be null. 0 means no value, 1 means shipper is NULL, 
                // 2 means shipper and employee are NULL, 3 means all are NULL
                int iNullValueDeterminer = rdmGenerator.Next(4);
                string sWhereClause="";
                SqlConnection scoCon = new SqlConnection(sCONNECTION_STRING);
                SqlCommand scmCom = new SqlCommand();
                SqlDataAdapter sdaAdapter = new SqlDataAdapter(scmCom);

                switch(iNullValueDeterminer)
                {
                    case 0:
                        // All parameters have a value
                        sCustomerID = sEXAMPLE_CUSTOMERID;
                        iEmployeeID = rdmGenerator.Next(1,10);
                        iShipperID = rdmGenerator.Next(1,4);
                        sWhereClause = " WHERE CustomerID=@sCustomerID AND EmployeeID=@iEmployeeID AND ShipVia=@iShipperID";
                        // add parameters
                        scmCom.Parameters.Add(new SqlParameter("@sCustomerID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, sCustomerID));
                        scmCom.Parameters.Add(new SqlParameter("@iEmployeeID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Current, iEmployeeID));
                        scmCom.Parameters.Add(new SqlParameter("@iShipperID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Current, iShipperID));
                        break;
                    case 1:
                        sCustomerID = sEXAMPLE_CUSTOMERID;
                        iEmployeeID = rdmGenerator.Next(1,10);
                        sWhereClause = " WHERE CustomerID=@sCustomerID AND EmployeeID=@iEmployeeID";
                        // add parameters
                        scmCom.Parameters.Add(new SqlParameter("@sCustomerID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, sCustomerID));
                        scmCom.Parameters.Add(new SqlParameter("@iEmployeeID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Current, iEmployeeID));
                        break;
                    case 2:
                        sCustomerID = sEXAMPLE_CUSTOMERID;
                        sWhereClause = " WHERE CustomerID=@sCustomerID";
                        // add parameters
                        scmCom.Parameters.Add(new SqlParameter("@sCustomerID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, sCustomerID));
                        break;
                    case 3:
                        // do nothing, they're already NULL;
                        break;
                }

                // create the query
                StringBuilder sbQuery = new StringBuilder("SELECT * FROM Orders");
                sbQuery.Append(sWhereClause);

                scmCom.CommandText = sbQuery.ToString();
                scmCom.Connection = scoCon;

                // run the query
                DataTable dt = new DataTable("Test");
                sdaAdapter.Fill(dt);
                if(bPRINT_ROWCOUNT)
                {
                    Console.WriteLine("Run no.: {0}", i);
                    Console.WriteLine("Amount of rows returned: {0}", dt.Rows.Count);
                }

                if(dt.Rows.Count > iMaxAmountRowsRetrieved)
                {
                    iMaxAmountRowsRetrieved=dt.Rows.Count;
                }

                if((i%100)==0)
                {
                    Console.WriteLine("Amount of runs done: {0}", i);
                }
            }
            DateTime daEndTime = DateTime.Now;

            Console.WriteLine("Benchmark ended on: {0}.\nTotal time: {1}.", daEndTime, (daEndTime - daStartTime));
            Console.WriteLine("Amount of runs: {0}. Max. amount of rows retrieved: {1}", iAMOUNT_LOOPS, iMaxAmountRowsRetrieved);
        }

        private void BenchmarkStoredProcedure()
        {
            Random rdmGenerator = new Random(unchecked((int)DateTime.Now.Ticks)); 

            Console.WriteLine("Stored procedure benchmark");
            DateTime daStartTime = DateTime.Now;
            Console.WriteLine("Benchmark started on: {0}.", daStartTime);
            int iMaxAmountRowsRetrieved = 0;
            for(int i=0;i<iAMOUNT_LOOPS;i++)
            {
                SqlString sCustomerID = SqlString.Null;
                SqlInt32 iEmployeeID = SqlInt32.Null;
                SqlInt32 iShipperID = SqlInt32.Null;

                // determine random value to check which values should be null. 0 means no value, 1 means shipper is NULL, 
                // 2 means shipper and employee are NULL, 3 means all are NULL
                int iNullValueDeterminer = rdmGenerator.Next(4);
                SqlConnection scoCon = new SqlConnection(sCONNECTION_STRING);
                SqlCommand scmCom = new SqlCommand("pr_Orders_SelectMultiWCustomerEmployeeShipper", scoCon);
                scmCom.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sdaAdapter = new SqlDataAdapter(scmCom);

                scmCom.Parameters.Add(new SqlParameter("@sCustomerID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, sCustomerID));
                scmCom.Parameters.Add(new SqlParameter("@iEmployeeID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Current, iEmployeeID));
                scmCom.Parameters.Add(new SqlParameter("@iShipperID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Current, iShipperID));

                switch(iNullValueDeterminer)
                {
                    case 0:
                        // All parameters have a value
                        sCustomerID = sEXAMPLE_CUSTOMERID;
                        iEmployeeID = rdmGenerator.Next(1,10);
                        iShipperID = rdmGenerator.Next(1,4);
                        break;
                    case 1:
                        sCustomerID = sEXAMPLE_CUSTOMERID;
                        iEmployeeID = rdmGenerator.Next(1,10);
                        break;
                    case 2:
                        sCustomerID = sEXAMPLE_CUSTOMERID;
                        break;
                    case 3:
                        // do nothing, they're already NULL;
                        break;
                }

                scmCom.Parameters["@sCustomerID"].Value = sCustomerID;
                scmCom.Parameters["@iEmployeeID"].Value = iEmployeeID;
                scmCom.Parameters["@iShipperID"].Value = iShipperID;

                // run the query
                DataTable dt = new DataTable("Test");
                sdaAdapter.Fill(dt);

                if(bPRINT_ROWCOUNT)
                {
                    Console.WriteLine("Run no.: {0}", i);
                    Console.WriteLine("Amount of rows returned: {0}", dt.Rows.Count);
                }

                if(dt.Rows.Count > iMaxAmountRowsRetrieved)
                {
                    iMaxAmountRowsRetrieved=dt.Rows.Count;
                }

                if((i%100)==0)
                {
                    Console.WriteLine("Amount of runs done: {0}", i);
                }
            }
            DateTime daEndTime = DateTime.Now;

            Console.WriteLine("Benchmark ended on: {0}.\nTotal time: {1}.", daEndTime, (daEndTime - daStartTime));
            Console.WriteLine("Amount of runs: {0}. Max. amount of rows retrieved: {1}", iAMOUNT_LOOPS, iMaxAmountRowsRetrieved);
        }
    }


    /// <summary>
    /// Starts up a test
    /// </summary>
    class Startup
    {
        [STAThread]
        static void Main(string[] args)
        {
            Benchmarker bm = new Benchmarker();
            bm.Start();
        }
    }
}