14 SQL Query Performance Tuning Tips

I am going to start this post with a bit of a disclaimer. At heart, I am a .NET developer and not a SQL expert so if there is anything below you disagree with, I welcome the constructive criticism in the comments! Hopefully these SQL query performance tuning tips will be helpful all the same.

These SQL tips and tricks have come from experience of optimising SQL queries over the last year. In several cases these changes have taken queries taking hours down to a few minutes or even seconds.

SQL optimisation is a bit of an art. What will work on one server and data set might not always work on another system, so please take the below with a pinch of salt and do your own analysis.

Cursors/Loops

Loops are a stable construct in any programmers tool box. SQL can do loops as well however they should be used with caution. In .NET we are used to looping over data. Dealing with data a row at a time can also make the logic simpler.

SQL has While loops and Cursors for this process. However, in most cases where I have seen cursors used they can be replaced with set based queries and joins. Loops and Cursors are fine for small one off tasks or batch operations but in my opinion, they have no place in a high performance systems.

If you are doing a large number of updates or inserts I would always recommend using a loop (see batching below).

Functions in the Where clause

Sometimes you need to transform the data before you can specify your filter criteria. This is sometimes done by using a function in the where clause. The most common place I have seen this is around dates. In some cases you aren’t bothered at what time something happened, just that it happened that day. So you will see something like this:

SELECT *
FROM dbo.SomeTable
WHERE CONVERT(DATE, CreatedDate) = '2017-01-10'

This is a problem. Especially if you are using user defined functions, as SQL can’t interpret what the value is going to be until after it calls the function. Dependent on your other criteria this could cause SQL to do a full table scan. It is therefore much better to use a date range in these cases.

SELECT *
FROM dbo.SomeTable
WHERE CreatedDate >= '2017-01-10' AND CreatedDate < '2017-01-11'

Doing the above makes it easy for SQL to interpret your query and therefore use the correct index. Note, I have used ‘2017-01-11’ instead of ‘2017-01-10 23:59:59’ as the later will miss off the last second of the day.

Continue reading

Creating a mandatory Anti-Forgery token

One of the things I love about ASP.Net, is that a lot of the hard work that is required for a creating a secure website has already been done for you. It usually only takes a couple of lines of code to add these features in which means there are no excuses for missing off important security measures.

Anti-Forgery Token

One of these features is the Anti-Forgery token and it can be added to your MVC website with just 2 lines of code. So what is an anti-forgery token? As the name suggests it is a token to prevent forgery! In the same way that someone might forge a signature to pretend to be someone else, it is possible for a malicious person to forge a request to your website without the request coming from your website.

So how is this done I hear you say? Well lets say you have a form on your website for changing user details such as name and email address, and a hacker wanted to change these to something else.

The hacker could create a form on another website which matches the request your website is expecting and post to the same URL. The entire form could be in hidden fields and posted via an Ajax request on page load making it invisible to the user.

If the user is already logged in to your website when the other website posts the form, your website treats it as a valid request and will change the user details to whatever the hacker wants.

So how do we get around this?

Well in the same way that 2 factor authentication works on something you know and something you have. The anti-forgery token works as the something you have (sorry about the poor analogy). The server places a hidden field with a populated anti-forgery token into your form. When a request is made to your website, the server checks for the presence of the anti-forgery token and if it doesn’t exist or doesn’t match the expected value an exception occurs.

As the hackers malicious form doesn’t know what the Anti-Forgery token is the request fails.

Continue reading

Best Practices for a RESTful API

Nowadays the web is powered by APIs. With applications being used on desktop and mobile, APIs are essential in allowing the code in backend systems to be reused. The most popular APIs from companies such as Facebook, Google, and Twitter use the RESTful API pattern.

Unlike other parts of your web site or app, your API should be designed to be used by programmers, like you. If you have ever used a badly designed API you will know how frustrating it can be to try and integrate with it. So what are some things you can do to make a good RESTful API.

Continue reading