The Joy of Development

One of the most rewarding parts of my job is delivering new software that makes a difference for my clients. It’s part of the reason I’m still working in the industry, it makes me feel good.

The other thing I really enjoy, and something that has happened today, is the ability to make a program (or small part) run faster. It was just a simple query, with a few tables, a view and sub query or two. In one of our environments the query run blazingly fast, taking only 2-3 seconds. In all of our other environments (one of which is an exact replica from 2 days ago) took a long time too complete. By a long time, I mean it took more than 15 minutes before I lost patience and stopped the process.

So, what changes did I make? Simple, it was just a restructure of the query. As I mentioned before, there was a sub query. This sub query was used within the where clause. An example is:

WHERE Table1.Field1 In (SELECT Field2 FROM Table2.Field2 WHERE Enabled = ‘Y’)

became:

FROM Table1 INNER JOIN (SELECT Field2 FROM Table2 WHERE Enabled = ‘Y’) as Table3 On Table1.Field1 = Table3.Field2

By moving this into the From section and making it an Inner Join, I was able to help the optimiser make the decision to apply the filter earlier in the execution.

The result, ever environment now runs the query sub second.

You may ask yourself, how did I know where to look? The answer is all in the tools you use. Today, I was using Toad, and a simple “Explain” on the query quickly shows you where the execution cost is. SQL Management Studio and many other tools can all provide an execution plan that you can use. There are a few things that you should focus on when looking over an execution plan. The two I focus on the most are Cost and Full Table Scans.

Cost provides you with a figure relative to the whole query about how expensive that operation is. If an operation is excessively expensive then you should try and simplify it.

Full Table Scans generally occur when there are no suitable indexes in place. This means that a filter cannot occur on an index and instead “scans the whole table”. As you can imagine, on a large table this can be a very time consuming process.

There is plenty more information available over the web on this topic. This is just one of my favourite (and easy) fixes for a very common database performance issue.

* This is a very simplified example of the actual query