Tips to increase database performance

Here are some of the points that you should consider when you write queries:

  • Limit number of rows and columns
  • Use “Search argument able” conditions in “where” clause. They help query optimizers to use the index defined on column(s) effectively and have a higher chance of meeting index seek than index or table scan.
    “Search argument able” operators are :
    =, >, >=, <, <=, BETWEEN, LIKE (only those LIKE conditions that have a wildcard character as a suffix, eg: FirstName LIKE 'A%'
  • Do not use arithmetic operators directly on the “column name” in the “where” clause. Instead use logical workarounds to get performance benefits. eg:
    instead of

    WHERE HourlyRate * 8 <= 100

    use

    WHERE HourlyRate <= 100/8
  • Do not use functions on columns in the “where” clause. eg:
    a. Instead of

    WHERE DATEPART(YYYY,CreateDate)='2016'

    use

    WHERE CreateDate >= '01/01/2016' AND CreateDate <= '12/31/2016'
  • b. Instead of

    WHERE Left(LastName,1)='A'

    use

    WHERE LastName LIKE 'A%'
  • Use proper primary and Foreign Key constraints. They help the query optimizer select the best-suited execution plan for the query.
  • Here are some of the points that you should consider when designing indexes.

  • Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
  • Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.
  • Try to create indexes on columns that have integer values rather than character values.
  • If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
  • Continue Reading

    Distinct on multiple properties in a collection

    Problem:
    How do I get distinct members of a collection, based on multiple properties?

    Solution:
    Use MoreLinq to get distinct members of a collection, based on multiple properties.
    eg: For our application, each member in the household was supposed to have a unique combination of driver’s licence number and driver’s state.

     
    var query = household.DistinctBy(
                counter => new { counter.DriverLicenceNumber, counter.DriverLicenceState });
    
    Continue Reading

    Configure StructureMap for Asp.Net MVC

    Problem:
    How do I configure StructureMap for Asp.Net MVC?

    Solution:
    a. Install StructureMap using NuGet and the following command.

    install-package StructureMap.MVC5 
    

    Once StructureMap is installed, you’ll notice several additions to your web project including a new “DependencyResolution” folder.

    Dependency Injection

    b. Create a new interface. Implement this interface with a new class.

     public interface IDependency
        {
            string SayHelloWorld();
        }
        public class Dependency : IDependency
        {
            public string SayHelloWorld()
            {
                return "Hello World";
            }
        }
    

    c. Have the Controller work with this interface:

    public class HomeController : Controller
    {
        private readonly IDependency dependency;
        public HomeController(IDependency dependency)
        {
            this.dependency = dependency;
        }
        public ActionResult Index()
        {
            return Content(this.dependency.SayHelloWorld());
        }
    }
    

    d. Run your application.The “Concrete Dependency” is successfully injected in HomeController.
    Dependency Injection-Results

    We didn’t tell StructureMap how to resolve the “IDependency” interface, or let it know anything about the “Dependency” implementation. How did it figure this out?

    In the DefaultRegistry.cs file, there is a line of code that reads

    scan.WithDefaultConventions();
    

    This code will automatically attempt to resolve any interface named “IDependency” with an instance type named “Dependency”. That is, the same name, but without the “I” prefix. If instead the name of the class was “ConcreteDependency” then the code below will not work. in this case, you will have to specify the types explicitly.You would do that by adding.

    For<IDependency>().Use<ConcreteDependency>();
    

    It is commented out in the DefaultRegistry.cs code

    public class DefaultRegistry : Registry 
    {
            public DefaultRegistry() 
            {
                Scan(
                    scan => 
                    {
                        scan.TheCallingAssembly();
                        scan.WithDefaultConventions();
                        scan.With(new ControllerConvention());
                    });
                    //For<IDependency>().Use<ConcreteDependency>();
            }
    }
    
    public static class IoC
        {
            public static IContainer Initialize()
            {   
                return new Container(c => c.AddRegistry<DefaultRegistry>());
            }
        }
    
    Continue Reading

    Convert the interface of a class into another interface that the clients expects(Adapter Pattern)

    Adapter Pattern

    Adapter pattern:

        class Program
        {
            static IExpectedInterface dependency = new Adapter(new TargetClass());
            static void Main(string[] args)
            {
                dependency.MethodA();
            }
        }
        public interface IExpectedInterface
        {
            void MethodA();
        }
        public class Adapter : IExpectedInterface
        {
            public Adapter(TargetClass target)
            {
                this.target = target;
            }
            public void MethodA()
            {
                target.MethodB();
            }
            private TargetClass target;
        }
        public class TargetClass
        {
            public void MethodB()
            {
            }
        }
    

    Advantages of using a Adaptor pattern:

    • Adapter pattern lets classes work together that couldn’t otherwise because of incompatible interfaces.
    • The code is more maintainable.
    • most of the applications using third party libraries use adapters as a middle layer between the application and the 3rd party library to decouple the application from the library. If another library has to be used only an adapter for the new library is required without having to change the application code.
    Continue Reading

    Log state of a object for diagnostic purposes

    Problem:
    How do I log state of a object for diagnostic purposes?

    Solution:
    We extended the class provided at Serialize and Deserialize IEnumerable Objects by adding a ToXMLString() method. This method serializes the passed object parameter to an XML string. The object state is stored in the Data property of the exception and then logged using a logging framework of choice.

    var exception = new Exception();
    exception.Data.Add("GuestCard", new CustomXmlSerializer().ToXMLString(guestCard));
    //Log the exception using a logging framework of your choice. 
    

    Extended Class: (See Serialize and Deserialize IEnumerable Objects)

        using System.Text;
        using System.IO;
        using System.Xml;
    
        public class CustomXmlSerializer
        {
            public void WriteXml(object source, System.Xml.XmlWriter writer)
            {
                WriteXml(source, writer, null);
            }
            public string ToXMLString(object source)
            {
                var stringBuilder = new StringBuilder();
                var stringWriter = new StringWriter(stringBuilder);
                var xmlTextWriter = new XmlTextWriter(stringWriter);
                WriteXml(source, xmlTextWriter);
                return stringBuilder.ToString();
            }
        }
    
    Continue Reading

    Mock ASP.Net internals when unit testing ASP.Net MVC.

    Problem:
    ASP.NET MVC controller context depends on asp.net internals. How should these be mocked for unit tests?

    Solution:

    Here is a sample unit test class using MsTest and Moq which mocks HttpRequest and HttpResponse objects. Controller action get value from request and sets http header in response objects. Other http context objects could be mocked up in similar way.

    using System.Web;
    using System.Web.Mvc;
    using System.Web.Routing;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using Moq;
    
    [TestClass]
    public class SampleControllerTest
    {
        protected Mock<HttpContextBase> httpContextBaseMock;
        protected Mock<HttpRequestBase> httpRequestBaseMock;
        protected Mock<HttpResponseBase> httpResponseBaseMock;
    
        [TestInitialize]
        public void TestInitialize()
        {
            this.httpContextBaseMock = new Mock<HttpContextBase>();
            this.httpRequestBaseMock = new Mock<HttpRequestBase>();
            this.httpResponseBaseMock = new Mock<HttpResponseBase>();
            this.httpContextBaseMock.SetupGet(x => x.Request).Returns(
            this.httpRequestBaseMock.Object);
            this.httpContextBaseMock.SetupGet(x => x.Response).Returns(
            this.httpResponseBaseMock.Object);
        }
    
        protected SampleController SetupController()
        {
            var controller = new SampleController();
            var routeData = new RouteData();
            controller.ControllerContext = new ControllerContext(
            this.httpContextBaseMock.Object, routeData, controller);
            controller.Url = new UrlHelper(
            new RequestContext(this.httpContextBaseMock.Object, routeData), 
            new RouteCollection());
            return controller;
        }
    
        [TestMethod]
        public void IndexTest()
        {
            this.httpRequestBaseMock.Setup(x => x["x"]).Returns("1");
            this.httpResponseBaseMock.Setup(x => x.AddHeader("name", "value"));
    
            var controller = SetupController();
            var result = controller.Index();
            Assert.AreEqual("1", result.Content);
    
            this.httpRequestBaseMock.VerifyAll();
            this.httpResponseBaseMock.VerifyAll();
        }
    }
    
    public class SampleController : Controller
    {
        public ContentResult Index()
        {
            var x = Request["x"];
            Response.AddHeader("name", "value");
            return Content(x);
        }
    }
    
    Continue Reading

    Log exceptions in classic(asmx) web services using Soap Extension

    Problem:
    Log exceptions in classic(asmx) webservices without adding try-catch blocks throughout all the webservice.

    Solution:
    Exceptions can be easily logged in classic(asmx) webservices by using a Soap Extension.
    a. Create the soap extension. Override the call to ChainStream method where we get a new copy of the stream. Use the ProcessMessage switch to examine the Soap Envelope and log an exception using a logging framework of our choice(I have used Elmah logging framework here).
    b. Register the SoapExtension in the client application’s web configuration. The “type” property follows the format of
    NameSpaceName.ClassName, SoapExtension’sAssemblyName.

    namespace SoapExtensions
    {
        using System;
        using System.IO;
        using System.Web.Services.Protocols;
    
        using Elmah;
    
        public class ElmahSoapExtension : SoapExtension
        {
            private Stream oldStream;
            private Stream newStream;
    
            public string Request { get; private set; }
            public string Response { get; private set; }
    
            public override Stream ChainStream(Stream stream)
            {
                oldStream = stream;
                newStream = new MemoryStream();
                return newStream;
            }
    
            public override void ProcessMessage(SoapMessage soapMessage)
            {
                switch (soapMessage.Stage)
                {
                    case SoapMessageStage.BeforeSerialize:
                        break;
                    case SoapMessageStage.AfterSerialize:
                        Response = GetSoapEnvelope(newStream);
                        if (soapMessage.Exception != null)
                        {
                            var exception = soapMessage.Exception.GetBaseException();
                            exception.Data.Add("WebService Response", Response);
                            ErrorSignal.FromCurrentContext().Raise(exception);
                        }
                        CopyStream(newStream, oldStream);
                        break;
                    case SoapMessageStage.BeforeDeserialize:
                        CopyStream(oldStream, newStream);
                        Request = GetSoapEnvelope(newStream);
                        break;
                    case SoapMessageStage.AfterDeserialize:
                        break;
                }
            }
    
            private static string GetSoapEnvelope(Stream stream)
            {
                stream.Position = 0;
                var streamReader = new StreamReader(stream);
                var text = streamReader.ReadToEnd();
                stream.Position = 0;
                return text;
            }
    
            private static void CopyStream(Stream streamFrom, Stream streamTo)
            {
                var textReader = new StreamReader(streamFrom);
                var textWriter = new StreamWriter(streamTo);
                textWriter.WriteLine(textReader.ReadToEnd());
                textWriter.Flush();
            }
            public override object GetInitializer(LogicalMethodInfo methodInfo, SoapExtensionAttribute attribute)
            {
                return null;
            }
            public override object GetInitializer(Type WebServiceType)
            {
                return null;
            }
            public override void Initialize(object initializer)
            {
            }
        }
    }
    
    <?xml version="1.0"?>
    <configuration>
    	<system.web>
    		<webServices>
          <soapExtensionTypes>
            <add 
            type="SoapExtensions.ElmahSoapExtension, SoapExtensions" 
            priority="1" group="1" />
          </soapExtensionTypes>      
    		</webServices>
    	</system.web>
    </configuration>
    
    Continue Reading

    Performance Improvement for very important client

    This data is for all our clients that use our application. Based on the data below there is a significant performance improvement in the application performance in the latest release. Please see highlighted area below. The latest release took place on March 2016.

    March2016PerformanceImprovement

    One of our biggest clients was threatening to leave us for another provider. Its users were experiencing very long load times(more than a minute in some cases), timeouts etc. I profiled the application using JetBrains DotTrace. All the hotspots in the profiler trace pointed to a very complex stored procedure being called again and again in the application. Since the stored procedure was very complex and used by several other applications it was decided to not change it. Instead we decided to store the results of this stored procedure in session. This lead to the dramatic performance improvement as shown above.

        using System.Web;
        public class RentableItemsStrategy
        {
            public RentableItemsStrategy()
            {
            }
    
            public RentableTypes Get(int unitId, string dateNeeded, int leaseId)
            {
                var keyTemp = Key(unitId, dateNeeded, leaseId);
                if (HttpContext.Current == null || HttpContext.Current.Session == null)
                {
                    return null;
                }
                
                var rentableTypesForUnitIdDateNeededLeaseId = HttpContext.Current.Session[keyTemp] as RentableTypes;
                if (rentableTypesForUnitIdDateNeededLeaseId != null)
                {
                    return rentableTypesForUnitIdDateNeededLeaseId;
                }
                
                var xmlNode = new ServiceAgent.ResidentSvc().GetRentableItems(unitId, dateNeeded, leaseId);
                var rentableTypes = RentableTypes.FromXml(xmlNode);
                HttpContext.Current.Session[keyTemp] = rentableTypes;
                return rentableTypes;
            }
    
            private string Key(int unitId, string dateNeeded, int leaseId)
            {
                return string.Format("RentableTypes-{0}-{1}-{2}", unitId, dateNeeded, leaseId);
            }
        }
    
    Continue Reading