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.
