Tips and tricks to improve SQL Server Query Performance

SQL Server Tutorials and Syntax
Post Reply
GURU
Posts: 1
Joined: Sun Jun 23, 2019 7:45 am

Sun Jun 23, 2019 8:39 am

Quick Tips and tricks to improve SQL Server Query Performance

Nullable Columns

Try to avoid using 'NOT IN' when comparing columns that are NULLABLE, try using 'NOT EXIST' instead. This is becasue When NOT IN is used in the query, SQL Server will check each of the results to see if it is null. Using NOT EXISTS wont do the comparison with nulls.

Always prefix objects with its schema name.

SQL Server will not search for the table outside of its own schema if the schema is provided. If the name is not provided, SQL Server will try to find it in all schemas.

Avoid using 'SELECT *'

Always specify the column names and avoid using 'SELECT *', this is because SQL Server will scan all of the 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.

Where possible, try to avoid using DISTINCT, GROUP BY and ORDER BY

Only use DISTINCT, GROUP BY and ORDER BY where necessary, when these operators are used SQL Server has to create a 'work' table to place the data to be organised that is requested by the query to return the final results.

SET NOCOUNT ON

Use SET NOCOUNT ON with DML operations (For example: INSERT, DELETE, SELECT, and UPDATE). Using SET NOCOUNT ON will improve the performance of the query as it will not count the number of rows affected. When using DML operations, SQL Server will return the number of rows affected by the query. In big queries with many joins this becomes a performance issue.

Do prefix your stored procedures names with 'sp_'

If the Stored Procedure is prefixed with 'SP_' SQL Server will check the MASTER database even if the schema is provided.

Avoid using Table Variables in Joins

Use CTEs (Common Table Expressions), Derived tables or Temporary Tables in joins instead of table variables. SQL Server will see Table variables as a single row and therefore produce a bad query plan when used and should be avoided.
Post Reply

Social Media