About Me

My photo
Kozyatağı, İstanbul, Türkiye

Sunday, May 4, 2014

Asynchronous Message Execution

Asynchronous message execution is a common requirement of enterprise applications. Suppose that you have a web application which receives financial transaction requests, processes them (create accounting entries) and generates response.

Throughout the life cycle of a single transaction, many operations may need to be executed without blocking current execution of request.For example, some kind of logging operations, notifying other integrated applications, etc.

This kind of non-blocking execution of operations can be fulfilled by way of message pools. In our type of message pool, there is one message pool in which the client code inserts its messages synchronously, but not executing messages.The message pool is listened by dedicated listener threads and responsibility of message execution belongs to them.

In this model, adding a message to the pool and taking a message from the pool may occur at the same time due to multithread model. For this reason, all the operations on pool must be thread safe.

For implementing this kind of message pool model, we can use ConcurrentQueue collection class of .NET Framework which is thread safe. Any kind of asynchronous execution request is enqueued by client code. Listener threads dequeue message one at a time, and invoke the "Execute" function of the message, which is contracted by an interface.

Here is the C# code:

Contract of the any message in the pool:



 interface IThreadPoolMessage
    {
        void Execute();
    }
--
Implementation of MessagePool class


Code:
class MessagePool : IDisposable
    {
        int listenerThreadCount;
        // The queue into which the messages are inserted.
        ConcurrentQueue<IThreadPoolMessage> msgQueue;
        // The flag for notifying the threads in order to stop.
        bool continueProcessingMessages = true;
        List<Thread> workerThreadList;
        public MessagePool(int listenerThreadCount)
        {
            this.listenerThreadCount = listenerThreadCount;
            Initialize();
        }

        void Initialize()
        {
            msgQueue = new ConcurrentQueue<IThreadPoolMessage>();
            workerThreadList = new List<Thread>();
            continueProcessingMessages = true;
            for (int i = 0; i < listenerThreadCount; i++)
            {
                Thread th = new Thread(new ThreadStart(ListenPool));
                th.Name = string.Format("NewThreadPool Thread_{0}", i + 1);
                th.IsBackground = true;
                workerThreadList.Add(th);
                th.Start();
            }
        }

       
        void ListenPool()
        {
            IThreadPoolMessage msg;
            while (continueProcessingMessages)
            {
                if (msgQueue.TryDequeue(out msg))
                {
                    try
                    {
                        msg.Execute();
                    }
                    catch (Exception ex)
                    {
                        // Logla.
                    }
                }
            }
        }


        public void AddMessage(IThreadPoolMessage msg)
        {
            this.msgQueue.Enqueue(msg);
        }

        public void StopAllThreads()
        {
            this.continueProcessingMessages = false;
            while (!workerThreadList.TrueForAll(p => p.ThreadState == System.Threading.ThreadState.Stopped))
            {
                ;
            }
        }

        public bool HasAwatingMessage { get { return this.msgQueue.Any(); } }

        public void Dispose()
        {
            StopAllThreads();
        }
    }

Here is a sample message, which is implemented for determining primality of an integer.


Code:
class CalculateIsPrimeMessage : IThreadPoolMessage
    {
        int val;
        public CalculateIsPrimeMessage(int val)
        {
            this.val = val;
        }
        public void Execute()
        {
            bool isPrime = IsPrime(val);
            WriteToDb(val, isPrime);
        }

        void WriteToDb(int val, bool isPrime)
        {
            using (SqlConnection conn = new SqlConnection("Server=.;Database=DENEME;Trusted_Connection=YES"))
            {
                string cmdStr = string.Format("INSERT INTO dbo.PRIMES SELECT {0}, {1}", val, isPrime ? 1 : 0);
                using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        bool IsPrime(int val)
        {
            if (val % 2 == 0)
            {
                return val == 2;
            }
            else if (val % 3 == 0)
            {
                return val == 3;
            }
            else if (val % 5 == 0)
            {
                return val == 5;
            }
            for (int i = 7; i * i <= val; i += 2)
            {
                if (val % i == 0)
                {
                    return false;
                }
            }
            return true;
        }
    }
--
Here is the sample client code which used message pooling system:



Code:
 static void Main(string[] args)
        {
            MessagePool msgPool = new MessagePool(10);
            Stopwatch sw = Stopwatch.StartNew();
            for (int i = 2; i < 10000; i++)
            {
                IThreadPoolMessage msg = new CalculateIsPrimeMessage(i);
                msgPool.AddMessage(msg);
            }

            while (msgPool.HasAwatingMessage)
            {
                ;
            }
            msgPool.Dispose();
            sw.Stop();
            Console.WriteLine("Elapsed Milliseconds: {0}", (int)sw.Elapsed.TotalMilliseconds);
        }
--

Saturday, February 8, 2014

Reducing Memory Consumption of .NET Dictionary Class


.NET programmers generally use parameterless constructor of .NET Dictionary class. Actually, the class has various constructors. Here I am focused on the one, which takes the initial capacity as parameter.

Using public Dictionary(int capacity)constructor, reduces memory consumption of instance dramatically.

To measure the difference clearly, we create here 2 dictionaries of type Dictionary<long, string>;
n  One with unknown capacity,
n  One with known capacity

We add 1 million items to each of them. Here are the results:












Code:

using System;
using System.Collections.Generic;

namespace caDictionaryMemory
{
    class Program
    {
        const int length = 1000000;
        static void Main(string[] args)
        {
            Dictionary<long, string> dictWithCapacity = new Dictionary<long, string>(length);
            long lengthWithCapacity = AddItemsAndFindMemoryUsage(dictWithCapacity, length);

            Dictionary<long, string> dictWithoutCapacity = new Dictionary<long, string>();
            long lengthWithoutCapacity = AddItemsAndFindMemoryUsage(dictWithoutCapacity, length);
            Console.WriteLine("Memory Usage When Capacity Known: {0:0,0} KB", lengthWithCapacity);
            Console.WriteLine("Memory Usage When Capacity Unknown: {0:0,0} KB", lengthWithoutCapacity);
            Console.WriteLine();
            // Dummy usage of dictionaries to prevent Garbage Collector to collect them.
            Console.WriteLine("Item Count When Capacity Known: {0:0,0}", dictWithCapacity.Count);
            Console.WriteLine("Item Count When Capacity Unknown: {0:0,0}", dictWithoutCapacity.Count);
        }

        static long AddItemsAndFindMemoryUsage(Dictionary<long, string> dict, int count)
        {
            long beforeBytes = GC.GetTotalMemory(true);
            for (int i = 0; i < count; i++)
            {
                dict.Add(i, i.ToString());
            }
            long afterBytes = GC.GetTotalMemory(true);
            return (afterBytes - beforeBytes) / 1024;
        }
    }
}

The dictionary with unknown capacity uses 221% memory compared to known one.

Wednesday, January 8, 2014

Programmatic Analysis of SQL Server Execution Plan


Developers generally display the execution plan on SQL Server Management Studio visually. (Most known method: Select name of the procedure and press CTRL + L). SQL Server Database Engine serves execution plans in XML format. The images you see for plan in Management Studio is the interpretation of this XML string.

This XML formatted execution plan can be examined programmatically in order to check for certain operations.

Scenario: For a given stored procedure, I want to determine if the proc performs table scan or index scan operations on large tables.

In SQL Server connection, queries which are executed after "SET SHOWPLAN_XML ON" are not executed, but execution plan is created only.

Here is the class that retrieves Execution Plan XML of a stored proc.


Code:
static class ExecutionPlanRetriever
    {
        public static string Retrieve(string spName, string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = GetCommandWithParams(spName, connectionString))
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        SetShowPlanOn(conn);
                        SetCommandParamsByDefault(cmd);
                        cmd.CommandTimeout = 0;
                        string planStr = cmd.ExecuteScalar().ToString();
                        return planStr;
                    }
                }
                catch (Exception e)
                {
                }
            }
            return null;
        }

        static SqlCommand GetCommandWithParams(string spName, string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(spName, conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlCommandBuilder.DeriveParameters(cmd);
                return cmd;
            }
        }

        static void SetShowPlanOn(SqlConnection conn)
        {
            string cmdText = "SET SHOWPLAN_XML ON";
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        static void SetCommandParamsByDefault(SqlCommand cmd)
        {
            foreach (SqlParameter prm in cmd.Parameters)
            {
                object val = GetDefaultSqlTypeValue(prm.SqlDbType);
                if (val is DataTable)
                {
                    prm.TypeName = ((DataTable)val).TableName;
                }
                prm.Value = val;
            }
        }

        static object GetDefaultSqlTypeValue(SqlDbType dbType)
        {
            object val = null;
            switch (dbType)
            {
                case System.Data.SqlDbType.BigInt: val = 0;
                    break;
                case System.Data.SqlDbType.Binary: val = val = new byte[1] { 0 };
                    break;
                case System.Data.SqlDbType.Bit: val = 0;
                    break;
                case System.Data.SqlDbType.Char: val = 'A';
                    break;
                case System.Data.SqlDbType.Date: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.DateTime: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.DateTime2: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.DateTimeOffset: val = null;
                    break;
                case System.Data.SqlDbType.Decimal: val = 0;
                    break;
                case System.Data.SqlDbType.Float: val = 0;
                    break;
                case System.Data.SqlDbType.Image: val = null;
                    break;
                case System.Data.SqlDbType.Int: val = 0;
                    break;
                case System.Data.SqlDbType.Money: val = 0;
                    break;
                case System.Data.SqlDbType.NChar: val = string.Empty;
                    break;
                case System.Data.SqlDbType.NText: val = string.Empty;
                    break;
                case System.Data.SqlDbType.NVarChar: val = string.Empty;
                    break;
                case System.Data.SqlDbType.Real: val = 0;
                    break;
                case System.Data.SqlDbType.SmallDateTime: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.SmallInt: val = 0;
                    break;
                case System.Data.SqlDbType.SmallMoney: val = 0;
                    break;
                case System.Data.SqlDbType.Text: val = string.Empty;
                    break;
                case System.Data.SqlDbType.Time: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.Timestamp: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.TinyInt: val = 0;
                    break;
                case System.Data.SqlDbType.UniqueIdentifier: val = string.Empty;
                    break;
                case System.Data.SqlDbType.VarBinary: val = new byte[1] { 0 };
                    break;
                case System.Data.SqlDbType.VarChar: val = string.Empty;
                    break;
                case System.Data.SqlDbType.Variant: val = null;
                    break;
                case System.Data.SqlDbType.Xml: val = null;
                    break;
                default:
                    break;
            }
            return val;
        }
    }

Now, once the XML string is ready, I can search for certain operations by using XPath.
Assuming we have the list of large tables, we can determine risky scan operations as:

Code:
    class DbObjectName
    {
        public string Schema { get; set; }
        public string Name { get; set; }
    }

    static class ExecutionPlanChecker
    {
        static DbObjectName[] TableIndexScanRiskTables = new DbObjectName[] {
            new DbObjectName { Schema = "SAMPLE_SCHEMA", Name = "SAMPLE_TABLE" }, 
        };

        // Resource for scan types:
        //http://technet.microsoft.com/en-us/library/ms186954(v=sql.105).aspx
        static readonly string[] riskyOperationNames = new string[] { "Table Scan", "Index Scan", "Clustered Index Scan" };

        public static bool PlanHasRiskyScanOperation(string planXml)
        {
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(planXml);
            doc = StripNamespace(doc);
            foreach (var opName in riskyOperationNames)
            {
                if (HasRiskyScanOperation(doc, opName))
                {
                    return true;
                }
            }
            return false;
        }

        static XmlDocument StripNamespace(XmlDocument doc)
        {
            if (doc.DocumentElement.NamespaceURI.Length > 0)
            {
                doc.DocumentElement.SetAttribute("xmlns", string.Empty);
                XmlDocument newDoc = new XmlDocument();
                newDoc.LoadXml(doc.OuterXml);
                return newDoc;
            }
            else
            {
                return doc;
            }
        }

        static bool HasRiskyScanOperation(XmlDocument doc, string scanOperationName)
        {
            string pattern = string.Format("//RelOp[@LogicalOp='{0}']", scanOperationName);
            XmlNodeList nodeList = doc.SelectNodes(pattern);
            foreach (XmlNode node in nodeList)
            {
                if (NodeTargetsRiskyTable(node))
                {
                    return true;
                }
            }
            return false;
        }
        
        static bool NodeTargetsRiskyTable(XmlNode node)
        {
            foreach (var item in TableIndexScanRiskTables)
            {
                string pattern = string.Format(@"OutputList/ColumnReference[@Schema='[{0}]' and @Table='[{1}]']", item.Schema, item.Name);
                if (node.SelectNodes(pattern).Count > 0)
                {
                    return true;
                }
            }
            return false;
        }
    }

Here, the client code calls "PlanHasRiskyScanOperation" function by passing the execution plan.