7 Tips for Boosting SQL Server Query Performance

When I see performance optimizations to reduce response times for web applications, the changes are often done at the application layer or by checking the index existence on the database table column(s). There is very less attention paid to the SQL query tuning. Even expert architects and developers tend to forget that understanding how databases work internally and writing better SQL queries is very important in order to get better performance. Here are seven simple tips that will boost the performance of your SQL queries.

1. Owner/Schema Name

Always prefix object names (i.e. table name, stored procedure name, etc.) with its owner/schema name.

Reason: If owner/schema name is not provided, SQL Server’s engine tries to find it in all schemas until the object finds it. SQL Server engine will not search for the table outside of its owner/schema if the owner/schema name is provided.

2. The * Operator

Do not use the *  operator in your SELECT statements. Instead, use column names.

Reason: SQL Server scans for all column names and replaces the * with all the column names of the table(s) in the SQL SELECT statement. Providing column names avoids this search-and-replace, and enhances performance.

3. Nullable Columns

Do not use NOT IN when comparing with nullable columns. Use NOT EXISTS instead.

Reason: When NOT IN is used 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.

4. Table Variables and Joins

Do not use table variables in joins. Use temporary tables, CTEs (Common Table Expressions), or derived tables in joins instead.

Reason: 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. CTEs and derived tables perform better with joins compared to table variables.

5. Stored Procedure Names

Do not begin your stored procedure’s name with sp_.

Reason: When the stored procedure is named sp_ or SP_, SQL Server always checks in the system/master database even if the Owner/Schema name is provided. Providing a name withoutSP_  to a stored procedure avoids this unnecessary check in the system/master database in SQL Server.

6. Use SET NOCOUNT ON

Use SET NOCOUNT ON with DML operations.

Reason: When performing DML operations (i.e. INSERT, DELETE, SELECT, and UPDATE), SQL Server always returns the number of rows affected. In complex queries with a lot of joins, this becomes a huge performance issue. Using SET NOCOUNT ON will improve performance because it will not count the number of rows affected.

7. Avoid Using GROUP BY, ORDER BY, and DISTINCT

Avoid using GROUP BY, ORDER BY, and DISTINCT as much as possible

Reason: When using GROUP BY, ORDER BY, or DISTINCT, SQL Server engine creates a work table and puts the data on the work table. After that, it organizes this data in work table as requested by the query, and then it returns the final result.

Use GROUP BY, ORDER BY, or DISTINCT in your query only when absolutely necessary.

Conclusion

Complex and large applications usually create complex and complicated requirements. That leads us to write complex SQL queries. These simple changes to your SQL Server queries will make a huge difference in the response times. Thank you for reading my article. I hope it was helpful.