Query Tuning (Part 4 - Ways To Improve Your Query Performance)

Hi everyone! Today's blog post will be focused around ways to improve your query performance and is part of my Query Tuning series.

You can find previous parts of the series here:

Query Tuning (Part 1 - Evaluating Query Performance)
Query Tuning (Part 2 - Query Execution Order)

Query Tuning (Part 3 - Graphical Execution Plans)

Now you have discovered that your queries are running slower than expected, you are aware of the theoretical query execution order and you have pinned down what exactly is causing the issue using the Graphical Execution Plans. The next step is to discover how to go about fixing the problem! The purpose of today's blog post is to explain in more detail what impacts performance and how you can avoid these situations from the offset. 

Search Arguments

Search Arguments are filter expressions that are used to limit the number of rows brought back as part of a result set. Search arguments are capable of using index seek operations, which greatly improve the performance of your query.

Note that for these examples, I put a non-clustered index on the column first using the following code:

An example of a query without using search arguments is:

This example uses the OrderDate column as an expression and produces the following execution plan:

However, note that if your filter expression isn't a search argument and none exist within the query, you will be using an index or table scan, which actually slows down performance. Be very careful! An example of the above query rewritten to boost performance is shown below:

Now the query above doesn't use AssetDate as an expression, it just does a comparison. The following execution plan is produced:

Make sure that you choose how you write your queries with care as these queries bring back exactly the same results but are implemented in different ways, yet one has much better performance than the other.

Joins

As you noticed in the blog post yesterday, using JOINs really can slow down your queries, especially if you are using OUTER JOINS. The best way around this problem is to reduce the number of JOIN clauses (WHERE and ON) to as few as possible. However, if this is not a viable solution you will have to seriously consider which JOINs you are using and try to use as few OUTER JOINs as possible.

Subqueries

Self-Contained Subqueries

Seeing as Self-Contained Queries do not rely on the outer query in order to bring back results, it means that there is very little query cost involved in executing them. 

Correlated Subqueries

However, correlated subqueries do rely on the outer query and if the outer query is returning a lot of rows, it means that the subquery is going to be processed many times in order to produce the final result set.

In order to avoid this from happening, try to use Self-Contained queries. However if that's not an available solution, use the ROW_NUMBER function instead. This way, you can find the exact amount of returning rows you need. However do note that if you do use the ROW_NUMBER function, it needs to be placed within a Common Table Expression.

User-Defined Functions

Scalar User Defined Functions

Scalar UDFs are not included in a graphical execution plan, so can be a hidden factor making your queries slow down. Make sure these are accounted for by using SET STATISTICS TIME ON in order to measure the total execution time. If you have optimised as much of the rest of the query as possible yet the time it takes for your query to run is still slower than expected, then it could be your User Defined Functions that are causing the problem. 

Table-Valued User Defined Functions

There are 3 different types of Table-Valued User Defined Functions which I have discussed in a previous blog post. To recap, they are:

• Inline 
• Multi-line
• CLR 

They all perform in different ways, therefore their individual query costs will vary greatly.

Inline

An Inline Table-Valued UDF is basically an optimised view that accepts parameters. Therefore, they run very quickly.

Multi-statement

A Multi-statement Table-Valued UDF works similarly to a stored procedure that populates data into a temporary table before querying against it. This means that if you're returning a large data set, that data set will need to be processing into a temporary table before any actions are performed against it.

CLR

A CLR Table-Valued UDF streams the result set as soon as it becomes available. This means that an outer query doesn't have to wait for the entire result set to be returned before it can start processing, it starts processing as soon as the first data row becomes available.

Typically, Inline statements are the best to use, followed by CLR and then Multi-statement. Try to write as much of your code as Inline as possible in order to make your queries that much faster.

Cursors

Generally, you should try and avoid using cursors as they have a big impact on performance. This is because they perform a minimum of a SELECT on every single row in the data set, which is very costly especially if you have many rows in the table. Instead, try to use set-based statements however if this isn't viable, try using a table-valued user defined function or a CLR stored procedure.

What Next...

You've tried to keep all of this in mind, yet still your queries are running slower than expected so what do you do next? My final blog post in this series is about using SQL Server Profiler and the Database Engine Tuning Advisor in order to capture those troublesome queries and easily tweak them to make them quicker, tools which I have found invaluable in the work place. Stay tuned!

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.

Happy Friday everyone :) Hope you all have a great weekend!