Performance study in Microsoft.ApplicationDataBlock.SqlHelper

Performance study in Microsoft.ApplicationDataBlock.SqlHelper

Supported by Nova Outsourcing

I recently got interested in Microsoft.ApplicationDataBlock.SqlHelper.ExecuteNonQuery as it was mentioned by an Umbraco developer in my team. I find the method allows programmers to assign parameters in a quite flexible way. It is defined as following.

public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues);

As the parameter parameterValues is defined with params key word, the number of the parameter value is dynamic according to the parameter number defined in the stored procedure.

It is like a proxy to stored procedure in C# code. The parameters in parameterValues array should has the same sequence as that in stored procedure. Let’s say, we have a stored procedure defined as following.

ALTER PROCEDURE [dbo].[InsertT1]
    @Id int,
    @Description varchar(50)
AS
BEGIN
    insert into T1 (Id, Description)
    values(@Id,@Description)
END

The following code invoke SqlHelper.ExecuteNonQuery to invoke the stored procedure eventually.

static void ExecuteSqlHelperInCorrectOrder()
        {
            SqlHelper.ExecuteNonQuery(_connStr, "insertt1", 1, "ExecuteSqlHelperInCorrectOrder");
        }

The code above looks quite concise and flexible, right? We name it concise because it has the same sequence of parameters as that defined in stored procedure. We name it flexible because no matter how many parameters defined in the stored procedure, SqlHelper.ExecuteNonQuery can always contain them. However, there is no free lunch in the world. It brings us big convenience, but in the meanwhile, it steals performance.

I wrote a little helper function for invoking stored procedure in DevLib3. The code goes as following. It is not as concise as SqlHelper.ExecuteNonQuery. The parameters are passed to the stored procedure by invoking AddParameterWithValue method.

static void ExecuteFromSpHelper()
{
    using (SqlSPHelper sp = new SqlSPHelper(_connStr, "insertt1"))
    {
        sp.AddParameterWithValue("Id", 1);
        sp.AddParameterWithValue("description", "ExecuteFromSpHelper");
        sp.ExecuteWithNonQuery();
    }
}

Here is a complete code sample to compare the performance.

using System;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
using System.Diagnostics;
using Dev3Lib.Sql;


namespace TestProject
{
    class Program
    {
        static string _connStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
        static void Main(string[] args)
        {
            Stopwatch watch = new Stopwatch();

            watch.Start();
            ExecuteFromSpHelper();
            watch.Stop();

            Console.WriteLine(string.Format("ExecuteFromSpHelper executes for {0} ticks", watch.ElapsedTicks));

            watch.Restart();
            ExecuteFromSpHelper();
            watch.Stop();

            Console.WriteLine(string.Format("ExecuteFromSpHelper executes for {0} ticks", watch.ElapsedTicks));

            watch.Restart();
            ExecuteSqlHelperInCorrectOrder();
            watch.Stop();

            Console.WriteLine(string.Format("ExecuteSqlHelperInCorrectOrder executes for {0} ticks", watch.ElapsedTicks));

            watch.Restart();
            ExecuteSqlHelperInCorrectOrder();
            watch.Stop();

            Console.WriteLine(string.Format("ExecuteSqlHelperInCorrectOrder executes for {0} ticks", watch.ElapsedTicks));

            Console.Read();
        }

        static void ExecuteSqlHelperInCorrectOrder()
        {
            SqlHelper.ExecuteNonQuery(_connStr, "insertt1", 1, "ExecuteSqlHelperInCorrectOrder");
        }

        static void ExecuteSqlHelperInWrongOrder()
        {
            SqlHelper.ExecuteNonQuery(_connStr, "insertt1", "ExecuteSqlHelperInWrongOrder", 1);
        }

        static void ExecuteFromSpHelper()
        {
            using (SqlSPHelper sp = new SqlSPHelper(_connStr, "insertt1"))
            {
                sp.AddParameterWithValue("Id", 1);
                sp.AddParameterWithValue("description", "ExecuteFromSpHelper");
                sp.ExecuteWithNonQuery();
            }
        }
    }
}

The result goes as following.

ExecuteFromSpHelper executes for 394664 ticks
ExecuteFromSpHelper executes for 6973 ticks
ExecuteSqlHelperInCorrectOrder executes for 75625 ticks
ExecuteSqlHelperInCorrectOrder executes for 10209 ticks

 

From the result, we can judge that the performance of SqlSPHelper is better than that of SqlHelper’s. However, the invoking code of SqlHelper is more concise than that of SqlSPHelper’s.

Note: ExecuteFromSpHelper is invoked two times here because the connection pool will take time to be initialized in the first invoking. ExecuteSqlHelperInCorrectOrder is invoked two times here because the parameters information of the stored procedure will take time to be initialized in the first invoking.

I will appreciate if you have any feedback on the discussion.

Supported by Nova Outsourcing

 

分类: C#&.Net