5 Tips to Improve SQL Query Performance

Introduction

In this article, I will share few SQL Tips for Boosting our SQL Query Performance. We nobody likes delay in response, right !!. Here are some simple changes that we have to make our query perform faster. The following is the flow of this write-up,

• Use Schema Name
• Always Select ONLY required Columns
• Use NOT EXISTS for NULLABLE Columns
• Avoid Table Variables in Joins
• Avoid GROUP BY, ORDER BY, and DISTINCT

Use Schema Name

It is a good practice to Prefix the SQL objects like Tables, Views, Stored Procedures, Functions and others with a meaningful Schema Name. When the Schema Name is not provided, the SQL Server engine tries to find it in all schemas until the object finds it. Whereas when the Schema Name is provide, SQL Server engine will not search outside of the Schema.

Always Select ONLY required Columns

The another good practice to improve the query performance is by Selecting ONLY the required column names in the Query (Select Statements), rather than using a * operator. When we * operator, the SQL Server scans for all columns and replace the * with all columns of that table in the query. Providing required column names avoids this search-and-replace, and improves the query performance.

Use NOT EXISTS for NULLABLE Columns

Do not use NOT IN when comparing with nullable columns. Use NOT EXISTS instead. When we use NOT IN in the query even if the query doesn’t return rows with null values, SQL Server will check each result to see if it is null or not. Using NOT EXISTS will not do the comparison with nulls.

Avoid Table Variables in Joins

Even though table variables are very fast and efficient in a lot of situations, the SQL Server engine sees it as a single row. Due to this, they perform horribly when used in joins. Do not use table variables in joins. Use temporary tables, Common Table Expressions (CTEs), or derived tables in joins instead. CTEs and derived tables perform better with joins compared to table variables.

Avoid GROUP BY, ORDER BY, and DISTINCT

As much as possible, avoid using GROUP BY, ORDER BY and DISTINCT . When we use these, he SQL Server engine creates a work table and puts the data on the work table. After that, it organizes this data in the work table as requested by the query, and then it returns the final result. This does not mean that we should not use GROUP BY, ORDER BY and DISTINCT, we can use these in query only when absolutely necessary.

Conclusion

These simple changes to our SQL Server queries will make a huge difference in the response times. I hope you all found this article useful. Let’s discuss some other Tips to improve the performance in another articles.

Consider to read other articles of Mine,

• Identifying object dependencies in SQL Server using SP_DEPENDS
• Select Names Starting With Vowels in MS SQL Server
• Display Line Numbers in a SQL Server Management Studio Query Window (SSMS)

Consider to watch this video