A question that I have been asked several times is how to go about performance tuning with your query. Let’s use a simple scenario here: a colleague comes to you and says, “I’ve written this query and its taking forever! Can you help?” How should you respond?
Let me clarify that this is a general topic and there is no magic bullet in tuning any query in SQL server. The reason could be a hardware issue, a physical database design issue, or not having proper indexing. While important, those are not the factors we’ll be discussing today. The only thing that we are trying to discuss is how to approach tuning the performance of your query and tips for rewriting it.
First, as any other software, the SQL database is limited by its hardware’s calculation ability. Reducing the workload of SQL database, and therefore the workload of the hardware, is key to improving its performance.
Table size: the bigger a table is, or the more columns that must be visited in a query, the more resources needed by the calculation.
Two tips to pay attention to when writing a query
- Only retrieve the data you really need, rather than using the ‘*’ symbol to return all columns and filter the latter. It probably makes no difference when the target table is small, but for a large table, the performance difference is significant.
- Avoid double-dipping a larger table. Try to get all the jobs done in a single query visit whenever possible. If you can’t, persist the subset by using a temporary table and work from there.
Furthermore, query running is also dependent on things that are related to the database itself. Knowing how SQL is running its query and writing it according would avoid unnecessarily long query running times.
Create Indexes Properly: Making sure that the tables are indexed properly is one of the most important things that we can do when we are performance tuning our query. We won’t go to details about how to index our database properly, because that depends on the pattern we use the data and it’s an another broad topic that we can write a blog about.
Stick to set theory: SQL query processes its data in set. Sticking to set theory could avoid row by row running calculations which could cause performance issues. There are several practical tips to help us when we are writing a query.
- Avoid functions on the Left Hand-Side of the Operator. Applying functions to the left side of the operator may result in major performance issues. This is because when we use the function on the left side of the operator, the output of the function is evaluated at run time. Therefore, the server has to visit all the rows in the table to retrieve the necessary data row by row, even though that column has been indexed properly. Instead, we should use the function on the right side of the operator so the system can utilize an index to seek the data more efficiently.
- Consider using joins to replace correlated subqueries if possible. A correlated subquery is a type of subquery which depends on the outer query. It uses the data obtained from the inner query as a condition in its outer clause. This is row by row processing which we should avoid when writing a query. However, using correlated subqueries might be inevitable when we are looking into certain particular cases.
- Avoid Wildcard Characters at the beginning of a LIKE pattern. The use of the % wildcard at the beginning of the LIKE pattern will prevent the database from using a suitable index if such exists. Since the system doesn’t know what the beginning of the column is, it will have to perform a full table scan anyway. You should always consider whether a wildcard character at the beginning is really essential.
Concurrency control: When multiple processes access the same data at the same time, SQL Server often has to make one process wait. This results in sacrificing performance. The more queries running concurrently on an object, the more the database must process at a given time, which results in slower performance. It can be especially bad if deadlocks occur. That’s why it is important to understand SQL Server concurrency and how to prevent blocking or deadlocking.
Above are the basic concepts we should pay attention to when we write a query and are also good points to start with when we do performance tuning. Start by knowing the causes of poor performance and you’ll know how to prevent it when composing the query.