SQL Tips for writing better TSQL query with performance in mind

A few months back, I had my chance to get my hands dirty with some old set of code written much before I even got into the IT space. My task was to add a bunch of new features on top the existing code base.  I had in fact spent a few hours looking at the exiting code (back-end in particular) and to my shock it had used things which will reduce query performance, increase complexity and attribute to poor maintainability. Over the time everybody ignored it until we started paying the price for the rush job done to get the product to market. The impact came mostly as customer complaints and not as audit wake up call and in either case we have to resolve it sooner.

 It’s often hard to convince the business owners the need to optimizing code for the future maintainability as from their stand point there is no return on investment in the short term. On the other hand it is much easier to educate the product maintenance team about the benefits of following some good guidelines which in most cases results in maintainable and optimized code. Here I want to articulate on the tips to improve the SQL performance just from a query perspective as in most cases the tuning is done using optimizing Indexes, reducing fragmentation, partitioning etc. The intention is to cover a few tips to follow while writing or modifying the existing stored procedures which may improve performance and reduce unwanted server resource consumption.

1. Use the actual column names in SELECT statement instead of using ‘*' Symbol

2. Use stored procedures or parameterised queries. (Facilitates SQL plan and data cache reuse)
3. Suppress number of rows affected message

SET NOCOUNT ON

4          4.  Keep Transact-SQL transactions as short as possible within a stored procedure.

5. Try to use fixed length data types in Where clause.

  

6. Offload tasks, like string manipulations, concatenations, row numbering, case conversions, and type conversions etc. to the front-end applications wherever possible.
7. Avoid using Temporary tables and  where ever possible use table variables instead (table variables are not a good a candidate in transaction scenarios)
8. Avoid searching using NOT equals operators (eg: NOT and <>) as they result in table and index scans instead of Seek.
9. Perform all your referential integrity checks and data validations using constraints (foreign key and check constraints) instead of triggers
10. Avoid dynamic SQL statements as much as possible. [use sp_executesql  instead of EXEC statement if dynamic sql use is unavoidable]
11. Use outer joins sparingly or where ever really necessary; inner joins are more efficient.
12. Use TRUNCATE instead of DELETE if you need to clear a whole table (Not possible if referential integrity constraints is present and mostly applicable for staging tables).
13. Instead of using SELECT COUNT(*) from <table_name> try using (In OLAP)

SELECT rows FROM sysindexes  WHERE id = OBJECT_ID('<table_name>') AND indid < 2 
[Good for OLAP systems; in small OLTP Databases there is no need to trust the sysindexes]

14. Prefer using EXISTS over OR , IN operators. [exceptions apply for smaller and limited subsets]
15. If you have “>= and <= “operators or in case of some “IN” operations try to see if BETWEEN  can be used as its more efficient.
16. Where possible, avoid string concatenation, as it is not a fast process.
17. If possible, try to avoid using data conversion functions in the WHERE clause.

e.g. SELECT   * FROM Requests WHERE  CONVERT(VARCHAR(12),created_on,101)=CONVERT(VARCHAR(12),getdate()-360,101)

18. A query with one or more OR clauses can sometimes be rewritten as a series of queries that are combined with a UNION ALL statement or using comma separated set (in SQL 2008 and above)
19. Always use SQL-92  syntax instead of SQL-89

20. Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
21. Do not use HAVING clause for filtering on non-aggregate scenarios (use Where Clause)
22. Try to avoid using SQL Server cursors, whenever possible.
23. Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
24. Try to use UNION ALL statement instead of UNION, whenever possible (union does remove duplicates hence its costly)
25. Don't use the prefix "sp_" in the stored procedure name
26. Use While loop instead of cursors.Also do not attempt recursive function calls as different SQL server had different default allowed recursion limits.

All the points mentioned are valid for SQL 2005 and above. There are some non-performance related SQL best practices that you can try when you are facing the SQL engine next time.

1. Minimize the use of NULLs in the result set

Use ISNULL or COALESCE functions to deal with them

2. All database objects should have a corresponding SQL script [Have schema scripts ready and stored in source control]
3. Use upper case for all T-SQL key words.
4. Give the prolog for  script/Stored Procedure with business logic
5. Always Prefix DBO to the Stored Procedure name.(if indeed you are using default schema)
6. Use user-defined functions to encapsulate code for reuse. (use this option carefully as it has the potential to create low performing query) 

Lastly do not forget to learn and harness the real power of Common table expressions. It is a great way to write clean and high performing code 

Create a custom web scrapper using html agility pack and XPath

Imagine you want to build a scrapper for a website the tools comes in handy are Html Agility pack and  a bit of knowledge on xpath.  Html Agility pack is a free to use HTML parser with very few dependencies and the main one is .Nets Xpath implementation.

The general implementation contains the following code to extract the html response from the url provided

var url= “http://en.wikipedia.org/wiki/Australia”;

var web = new HtmlWeb();

HtmlDocument responseHtmlDoc = web.Load(url);

//now start interrogating the htmlDocument using xpaths to get the data

responseHtmlDoc.DocumentNode.SelectSingleNode("//div[@class='test’]");

But now imagine you have to first post some data to retrieve the actual url to retrieve required data.  For example

You have to mimic the action of entering search criteria and button click to get set of urls  

Entering user and password and clicking on login button (that’s scary..) etc

If you are going for an asp.net website then it will have event validations and proper view state values set to perform the initial data post action. Let’s try to create a simple page scrapper which allows you to go against an asp.net forms based website and interrogate the html response to retrieve data you want.

The following steps will give an idea of how to implement custom scrapper

Step 1.    Download html agility pack from code plex [http://htmlagilitypack.codeplex.com/]. This will allow you to load the MSHTML,W3C HTML in an HtmlDocument data structure

Step 2.    Create a Custom WebClient class to make requests after manipulating cookie.

using System.Net;

internal class CookieAwareWebClient : WebClient

{

private CookieContainer cc = new CookieContainer();

private string _lastPage;

protected override WebRequest GetWebRequest(Uri address)

{

var r = base.GetWebRequest(address);

var wr = r as HttpWebRequest;

if (wr != null)

{

wr.CookieContainer = cc;

if (_lastPage != null)

{

wr.Referer = _lastPage;

}

}

_lastPage = address.ToString();

return r;

}

protected override WebResponse GetWebResponse(WebRequest request)

{

var response = base.GetWebResponse(request);

return response;

}

}

Step 3.    Inspect  the web pages viewstate value and event validation behaviors and write a viewstate value modification method

private static NameValueCollection GetViewState(string getResponse, string postcode)

{

if (string.IsNullOrEmpty(getResponse))return null;

var viewStateIndex = getResponse.IndexOf("__VIEWSTATE");

var eventValidationIndex = getResponse.IndexOf("__EVENTVALIDATION");

var collection = new NameValueCollection { { "__EVENTTARGET", "" },

{ "__EVENTARGUMENT", "" }, };

var viewState = getResponse.Substring(viewStateIndex + 37);

viewState = viewState.Substring(0, (viewState.IndexOf("/>") - 2));

collection.Add("__VIEWSTATE", viewState);

collection.Add("__VIEWSTATEENCRYPTED", "");

var eventValidation = getResponse.Substring((eventValidationIndex + 49));

eventValidation = eventValidation.Substring(0, eventValidation.IndexOf("/>") - 2);

collection.Add("__EVENTVALIDATION", eventValidation);

collection.Add("content_0$contentcolumnmain_0$txtPostcode", postcode);

collection.Add("content_0$contentcolumnmain_0$btnSearch", "Search");

return collection;

}

Step 4.    Let’s make a call to web URL and sent  the automated actions

using HtmlAgilityPack;

var webClient = new CookieAwareWebClient();

var uri = "http://someurl.aspx";

string getResponse = string.Empty;

using (StreamReader reader = new StreamReader(webClient.OpenRead(uri)))

{

getResponse = reader.ReadToEnd();

}

//call custom GetViewState method

var viewStateValues = this.GetViewState(getResponse, “mr. x”);

// Upload the NameValueCollection.

byte[] responseArray = webClient.UploadValues(uri , "POST", viewStateValues);

// Save the response string for future

var responseStringToBeStored = Encoding.ASCII.GetString(responseArray);

Now you can inspect the response “responseStringToBeStored” variable content and strip out links that can be used to scrape the page as mentioned in the beginning of the post.

Web Browser timezone specific date display in asp.net

It may sound like a simple requirement to display date and time localized to the logged in user but it gets really tricky if you have to implement it on an age old asp.net application.Applications built  with SQL Server 2005 and below with no time zone aware datatypes and data are mostly stored at the servers time zone (no UTC based storage adopted aswell). 

The problem statement was simple; though the server is situated in VICTORIA(AEST – Australian Eastern Standard Time) a user logging in from Perth (AWST – Australian Western Standard Time) should see the time-zone according to their browser time-zone. The server is located in Victoria and users around the world should be able to see the date-time according their web browser's time-zone. 

Lets see the problem with an example;
User from Sydney teleconference task scheduled at 10 am AEST(winter time). A user from Perth should see this as 8 am AWST but our good old system database will have the data stored as 10 am as server is in AEST time-zone.

There are two aspects to this problem, 
1. Getting the time zone information from the browser
2. Implement the time zone specific date and time displayed through out the system

This can be easily implemented by following the four steps
Step 1: Define a JavaScript function to create cookie to store browser time zone [TimezoneFinder.js file]

function setTimezoneCookie() {

    var timezone_cookie = "timezoneoffset";

    // if the timezone cookie not exists create one.

    if (!$.cookie(timezone_cookie)) { // check if the browser supports cookie       

        var test_cookie = 'test cookie';

        $.cookie(test_cookie, true);

        // browser supports cookie

        if ($.cookie(test_cookie)) {          

            $.cookie(test_cookie, null);  // delete the test cookie           

            $.cookie(timezone_cookie, new Date().getTimezoneOffset()); // create a new cookie

            location.reload();// re-load the page

        }

    }

    else {// if the current timezone and the one stored in cookie are different

          // then store the new timezone in the cookie and refresh the page.

        var storedOffset = parseInt($.cookie(timezone_cookie));

        var currentOffset = new Date().getTimezoneOffset();

        // user may have changed the timezone

        if (storedOffset !== currentOffset) {

            $.cookie(timezone_cookie, new Date().getTimezoneOffset());

            location.reload();

        }

    }

}

Step 2: Include script file and call the "setTimezoneCookie" function in the Master page

<script src="[path]/TimezoneFinder.js" type="text/javascript"></script>

<script type="text/javascript">

$(function () {

  setTimezoneCookie();

});

</script>

Step 3: Lets assume all the aspx codebehind files are inheriting from a common BasePage to allow pushing common UI specific logic. Now lets read the timezone cookie value and store it in a session variable.

protected override void OnPreLoad(EventArgs e)

{

  base.OnPreLoad(e);

 //Save the timezone information from cookie to a session variable

 Session["timezoneoffset"] = this.Request.Cookies.AllKeys.Contains("timezoneoffset",

s => s.ToLower())

? this.Request.Cookies["timezoneoffset"].Value : null;

}

Step 4: Final step will be to create a bunch of utility methods to help with the conversion. Lets create some extension method to hide the detail and give a clean syntax for the calling code.
[TimezoneConvertExtensions class]

public static class TimezoneConvertExtensions

{

    private static int? _serverTimezoneOffset = null;

    private static int ServerTimezoneOffset

    {

        get

        {

            return _serverTimezoneOffset.HasValue

                        ? _serverTimezoneOffset.Value

                        : (_serverTimezoneOffset = GetServerTimezoneOffset()).Value;

        }

    }

    private static int GetServerTimezoneOffset()

    {

        var timeZone = TimeZone.CurrentTimeZone;

        var offset = timeZone.GetUtcOffset(DateTime.Now);

        return offset.Hours * 60 + offset.Minutes;

    }

    public static DateTime ToClientTime(this DateTime dt)

    {

        if (dt == DateTime.MinValue) return dt; 

// read the value from session         

        var timeOffSet = HttpContext.Current.Session["timezoneoffset"];

        if (timeOffSet != null)

        {

            var offset = int.Parse(timeOffSet.ToString()) + ServerTimezoneOffset;

            dt = dt.AddMinutes(-1 * offset);

            return dt;

        }// if there is no offset in session return the datetime in server timezone           

        return dt.ToLocalTime();

    }

    public static DateTime ToServerTime(this DateTime dt)

    {

        if (dt == DateTime.MinValue) return dt;

        var timeOffSet = HttpContext.Current.Session["timezoneoffset"];

        if (timeOffSet != null)

        {

            var offset = int.Parse(timeOffSet.ToString()) + ServerTimezoneOffset;

            dt = dt.AddMinutes(1 * offset);

            return dt;

        }

        return dt.ToLocalTime();

    }

}

How to Use in code:
By default all the data will be saved with respect to the server timezone (here AEST ) .In other words the created datetime of a record will be according to db server timezone. 

These set of records can be easily localised and displayed in the browsers timezone  using the following code
someDate.ToClientTime() 
e.g. test using DateTime.Now.ToClientTime()

On the other hand If the system wants to save the user entered date and time then received datetime has to be explicitly converted to server timezone for saving. The following call will convert the data to server timezone before saving. (for example a conference appointment time saved from AWST timezone has to be saved in AEST timezone in the server so that it makes sense for a browser request from AWST at later point in time)
 e.g. userEnteredDate.ToServerTime()

How to retrieve service metadata for WCF services that use X509 certificates for client validation

Svcutil is wonderful tool and it comes handy when trying to create WCF client applications. It makes use of the MEX endpoint and retrieves the config and proxy classes.  

Where to look for svcutil.exe tool and its configuration in your machine (WIN 7 64bit):

 C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin

[select the right version "v7.0A"]

In Visual studio command shell, you can type as follows
Svcutil http://MyTestService/Test.svc

This will generate the service endpoint information as config and proxy as a .cs file.

This approach does not work for the WCF services that demands client authentication certificate while receiving the actual or metadata-exchange request. 

You may have kept the client authentication certificate in your “localmachine” or “currentuser” certificate store (mostly in “personal”) [You can do this either via MMC console or certification manager tool]

http://msdn.microsoft.com/en-US/library/e78byta0(v=VS.80).aspx

http://msdn.microsoft.com/en-us/library/bb950259(v=bts.10).aspx

In such cases if you try to execute this tool you will be receiving error as below

-------------------------------------------------------------------------

Svcutil https://MyTestService/Test.svc

WS-Metadata Exchange Error

URI: https://MyTestService/Test.svc

Metadata contains a reference that cannot be resolved: 'https://MyTestService/Test.svc'.

The HTTP request was forbidden with client authentication scheme 'Anonymous'

The remote server returned an error: (403) Forbidden.

-------------------------------------------------------------------------

This happens as the svcutil does not know where to look for the certificate. There is an easy way to get this working

1.      Locate the svcutil.exe and svcutil.exe.config
2.     Copy it to any of your folder let’s say c:\temp\
3.     Now modify the svcutil.exe.config to have the corresponding certificate store information (mention specific endpoint details and client credentials) 

<?xml version="1.0" encoding="utf-8"?>

<configuration>

  <system.serviceModel>

    <client>

      <endpoint behaviorConfiguration="wsHttpBehavior"

         binding="wsHttpBinding" bindingConfiguration="wsHttpMex"

         contract="IMetadataExchange" name="https"/>

    </client>

    <bindings>

      <wsHttpBinding>

        <binding name="wsHttpMex">

          <security mode="Transport">

            <transport clientCredentialType="Certificate" />

          </security>

        </binding>

      </wsHttpBinding>

    </bindings>

    <behaviors>

      <endpointBehaviors>

        <behavior name="wsHttpBehavior">

          <clientCredentials>

                  <clientCertificate findValue="<clientcertificatesubject>"

                  storeLocation="LocalMachine"

                  storeName="My"

                  x509FindType="FindBySubjectName" />          

          </clientCredentials>

        </behavior>

      </endpointBehaviors>

    </behaviors>

  </system.serviceModel>

</configuration>

4.  Now the following command will be working as the svcutil is now able to locate the certificate in LocalMachine\Personal certificate store.

c:\temp\Svcutil https://MyTestService/Test.svc

Solving the ViewState code cluttering in ASP.net Web Forms

If you have worked on asp.net forms then you would have noticed the issue with viewstate from a maintenance point of view.  If we need to temporarily keep a value during post backs then one of the ways to do it is to store into page or control viewstate. Most of the rush jobs will introduce viewstate with magic strings (e.g. ViewState[“TestId”]) through out the page and soon you will see the a maintenance monster getting ready to overpower you. One of the ways to isolate this problem is to re-factor this code (view state magic strings) to properties.By this technique of limiting viewstate usage to a property we can achieve type safety and localize the issue.

But the code might look like this and its a good start as this approach gives some type safety.

     public long TestId

        {

            get

            {

                if (ViewState["TestId"] != null)

                {

                    return System.Convert.ToInt64(ViewState["TestId"]);

                }

                else

                {

                    return 0;

                }

            }

            set

            {

                ViewState["TestId"] = value;

            }

        }

While looking at a nicer way to solve this issue i came across with a code project blog which talks about creating reusable attributes.By applying this attribute based programming model we will be able to push data onto viewstate in a much clean way as below.

// new way of defining a viewstate property will be

[ViewStateProperty]

public long TestId { get; set; }

Though the real inspiration is from the above mentioned article, some customizations are done to support the generic use and explained implementation details in the following 5 steps.

Step 1. Define IViewStateProperty interface which can later provide extensibility and scoping (only those user control marked with this interface will have the feature of ViewStateProperty attribute)

 public interface IViewStateProperty    {    }

Step 2. Define ViewStateProperty attribute

   [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]

    public class ViewStateProperty : Attribute

    {

        public string ViewStateName { get; private set; }

        public object DefaultValue { get; set; }

        public ViewStateProperty()

        {

            this.ViewStateName = string.Empty;

        }

        public ViewStateProperty(object defaultValue)

        {

            this.DefaultValue = defaultValue;

        }

    }  

Step 3. Define ViewStatePageBase Custom UI Page Base class

/// <summary>/// DEVNOTE: ViewStateProperty instances that are declared as 'private' may not //// be found on reflection (if the application is running in medium trust. E.g. /// when you are running on a shared hosting environment)/// </summary>

  public class ViewStatePageBase : System.Web.UI.Page

    {

        protected override void OnPreLoad(EventArgs e)

        {

            base.OnPreLoad(e);

            this.DoLoadViewStateProperties(this);

            this.LoadViewStatePropertiesRecursive(this.Controls);

        }

        protected override void OnPreRenderComplete(EventArgs e)

        {

            base.OnPreRenderComplete(e);

            this.DoSaveViewStateProperties(this);

            this.SaveViewStatePropertiesRecursive(this.Controls);

        }

        private void LoadViewStatePropertiesRecursive(ControlCollection controls)

        {

            foreach (Control ctrl in controls)

            {

                if (ctrl is IViewStateProperty)

                {

                    this.DoLoadViewStateProperties(ctrl);

                }

                LoadViewStatePropertiesRecursive(ctrl.Controls);

            }

        }

        private void SaveViewStatePropertiesRecursive(ControlCollection controls)

        {

            foreach (Control ctrl in controls)

            {

                if (ctrl is IViewStateProperty)

                {

                    this.DoSaveViewStateProperties(ctrl);

                }

                this.SaveViewStatePropertiesRecursive(ctrl.Controls);

            }

        }

        private void DoLoadViewStateProperties(Control ctrl)

        {

            var properties = ctrl.GetType().GetProperties(BindingFlags.Public 

                           | BindingFlags.NonPublic | BindingFlags.Instance)

                .Where(prop => Attribute.IsDefined(prop, typeof(ViewStateProperty), true))

                .Select(p => new ViewStatePropertyInfo()

                {

                    ViewStateName = ((ViewStateProperty) p.GetCustomAttributes (typeof(ViewStateProperty), true)

                                             .FirstOrDefault()).ViewStateName,

                    PropertyInfo = p

                });

            foreach (var property in properties)

            {

                var localName = String.Format("{0}_{1}"

                                                , ctrl.ClientID

                                                , String.IsNullOrEmpty(property.ViewStateName)

                                                    ? property.PropertyInfo.Name

                                                    : property.ViewStateName); 

                if (ViewState[localName] != null)

                {

                    property.PropertyInfo.SetValue(ctrl, ViewState[localName], null);

                }

            }

        }

        private void DoSaveViewStateProperties(Control ctrl)

        {

            var properties = ctrl.GetType().GetProperties(BindingFlags.Public 

                                        | BindingFlags.NonPublic | BindingFlags.Instance)

                .Where(prop => Attribute.IsDefined(prop, typeof(ViewStateProperty), true))

                .Select(p => new ViewStatePropertyInfo()

                {

                    ViewStateName = ((ViewStateProperty)p.GetCustomAttributes(typeof(ViewStateProperty), true)

                                         .FirstOrDefault()).ViewStateName,

                    PropertyInfo = p

                });

            foreach (var property in properties)

            {

                var localName = String.Format("{0}_{1}"

                                                , ctrl.ClientID

                                                , String.IsNullOrEmpty(property.ViewStateName)

                                                    ? property.PropertyInfo.Name

                                                    : property.ViewStateName);

                ViewState[localName] = property.PropertyInfo.GetValue(ctrl, null);

            }

        }

        private struct ViewStatePropertyInfo

        {

            internal string ViewStateName { get; set; }

            internal PropertyInfo PropertyInfo { get; set; }

        }

    }

Step 4. Apply layer super type pattern to pages (aspx.cs) and ensure they inherit from custom base class (ViewStatePageBase) instead of System.Web.UI.Page.

e.g. A Test Page will inherit from a custom ViestatePageBase as follows

public partial class TestPage : ViewStatePageBase

Step 5. To bring user controls to this equation try inheriting from a base class (UserControlBase) and implement with the IViewStateProperty  

public class UserControlBase : System.Web.UI.UserControl, IViewStateProperty

i.e. TestUserControl.ascx.cs will be as defined follows

public class TestUserControl : UserControlBase

Now you are all set to decorate properties with [ViewStateProperty] attribute and use code as below

[ViewStateProperty]

public long TestId { get; set; }