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

Why I switched from Windows to a Mac, and won’t go back

As a .NET developer I have spent most of my computing career with Microsoft at the helm. So here is my journey to this point and why I can’t see myself going back to Windows.

I started coding when I was 10, with a Windows 3.1 machine that my Dad had brought home from work (I assume they were already obsolete at this point). Since then I have used pretty much every Windows incarnation to date. With the revelation that the start menu made with Windows 95 and the stability brought from Windows 98. Windows ME (:shudder:), Windows 2000, XP, 8, 8.1 and now Windows 10.

To be honest, I quite like Windows 10 but the annoyances that existed with the previous versions are still there. Plus, I have had a few unfortunate circumstance over the last few years that have put me off Windows.

It started with Windows 8. The first version of Windows where I have had to Google how to do a shutdown. You can imagine my relief when they made the genius decision to put the shutdown button back on the start screen. However like many, I didn’t have the seamless upgrade to Windows 8.1 that I had hoped. I had some faith, that after Windows had installed the 170 updates I would be up and running with the new version.

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 Adult Coloring Books for Geeks

I think most people have fond memories of colouring from their childhood, there is something about making a black and white picture come to life with colour that is very satisfying. I first heard about adult coloring books about a year ago when looking at forms of meditation. As a child it is called colouring in, as an adult it is called mindfulness. As a software engineer it is good to have a hobby that doesn’t involve looking at a screen.

I started with The Mindfulness Coloring Book, and although it is probably good for some people I didn’t like the mindless random patterns. I think anyone with even a mild case of OCD would find this book frustrating as sometimes the lines don’t match up making alternating patterns impossible.

My wife has a couple of good colouring books such as Millie Marotta’s Animal Kingdom and Johanna Basford’s Enchanted Forest. The problems is I don’t want to colour in flowers, animals or random patterns. My 2 year old daughter loves colouring at the moment and I think it was colouring in with her that made me realise I like using realistic colours.

Continue reading

Why can’t all programming books look like this?

I have read quite a lot of programming books over the years. A lot of them have been really useful in learning a new programming language and some of them have been less than helpful. Unfortunately, the one thing all these books had in common was how dull they were to read. It’s not necessarily the fault of the author, programming is more of a practical subject and reading pages on pages of code can get a bit dry after a while. Some books do try and add a bit of humour to break up the monotony but even this can get a bit annoying after a while (yes, I mean you Head First).

Continue reading

Problems with VirtualBox + Vagrant on Windows 10

I am big fan of Vagrant. I first discovered Vagrant when I was looking for ways of creating a development environment that I could transfer between various computers (I had a desktop and laptop I regularly worked on).  I even toyed with the idea of installing a Linux distro on a fast USB 3 stick to carry round. It was then, while in my search for the perfect development environment that I discovered Vagrant. However I haven’t got Vagrant on Windows 10 working until now.

Vagrant was working fine the last time I used it, mainly for WordPress theme development. I haven’t touched it for about 6 months now but after typing vagrant up and waiting I was soon greeted with this:

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

The Big List of Free Pluralsight Courses for Developers

The Big List of Free Pluralsight Courses for DevelopersOne of the most important aspects of being a software developer is the ability to learn new skills quickly. Our industry is moving so quickly you have to keep learning new technologies so your skills don’t get old.

There are many great resources online for learning new skills but I have found Pluralsight to have the largest collection of quality videos out there. Most of the courses on Pluralsight are many hours long and made by well known developers such as Scott Allen, Troy Hunt, Scott Hanselman and Jon Skeet.

I should point out this isn’t a promotional post for Pluralsight, I am just happy customer.

What is a little less known about Pluralsight is that there many courses that are free to watch without an account. Here is a list of all the free Pluralsight courses I have found that would be useful for developers.

Continue reading

Analysing Google Play to find a profitable app idea – Part 5: PlayDrone

So far I have used the PlayDrone data to find out what the most downloaded free games, paid games and paid apps are. There are many useful queries that can be run to try and find a profitable app idea. For example, you could look at apps with lots of downloads but with a bad user rating. You could then look at the comments for that app and find out why the users are dissatisfied with it and make a better app! You could even get a list of all paid apps that have made at least £10,000 if you wanted. It could also be useful to look at apps that have failed, by running a query to find apps that have never been downloaded and then find out why, so your apps don’t suffer the same fate.

You can download a 735MB Json (JavaScript Object Notation) file that contains details of the 1.4 million apps on Google Play as of 31/10/2014 from the Internet Archive. I had some fun writing a console app that inserts details about all these apps into SQL, so that queries can be run against it. You can find the source code for this on my GitHub account with the imaginative name of PlayDrone2SQL.

Continue reading