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.
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
Reason: When the stored procedure is named
SP_, SQL Server always checks in the system/master database even if the Owner/Schema name is provided. Providing a name without
SP_ to a stored procedure avoids this unnecessary check in the system/master database in SQL Server.
6. Use SET NOCOUNT ON
SET NOCOUNT ON with DML operations.
Reason: When performing DML operations (i.e.
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
ORDER BY, and
DISTINCT as much as possible
Reason: When using
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.
ORDER BY, or
DISTINCT in your query only when absolutely necessary.
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.