Some tips for improving the performance of SQL Server queries

When I observe performance optimizations for online apps, I often see them done at the application layer or by validating the index existence on a database table field column (s). The optimization of SQL queries receives very little attention. Even experienced architects and developers have a tendency to overlook the need of understanding how databases function internally and developing better SQL queries in order to get greater speed. Here are some quick strategies to help you improve the speed of your SQL queries.

1. Owner/Schema Name

Always include the owner/schema name in the name of an object (i.e. table, stored procedure, etc.).

Reason: If the owner/schema name isn’t specified, SQL Server’s engine searches all schemas until it finds it. If the owner/schema name is provided, the SQL Server engine will not look for the table outside of its owner/schema.

2. The * Operator

In your SELECT statements, do not use the * operator. Use column names instead.

Reason: SQL Server looks for all column names in the SQL SELECT statement and substitutes the * with all of the table(s) column names. This search-and-replace is avoided by providing column names, which improves performance.

3. Nullable Columns

When comparing nullable columns, do not use NOT IN. Instead, use NOT EXISTS.

Reason: SQL Server will verify each result to determine if it is null or not when NOT IN is used in the query (even if the query does not produce rows with null values). The comparison will not be done with nulls if NOT EXISTS is used.

4. Table Variables and Joins

In joins, don’t use table variables. Instead, in joins, use temporary tables, CTEs (Common Table Expressions), or derived tables.

The reason for this is that, despite the fact that table variables are very fast and efficient in many cases, the SQL Server engine treats them as a single row. As a result, when utilized in joins, they perform horribly. When compared to table variables, CTEs and derived tables perform better with joins.

5. Stored Procedure Names

Do not use the sp_ prefix in the name of your stored procedure.

Even if the Owner/Schema name is provided, SQL Server always checks in the system/master database when the stored procedure is named sp_ or SP_. In SQL Server, giving a stored procedure a name without SP_ eliminates this superfluous check in the system/master database.

6. Use SET NOCOUNT ON

With DML operations, use SET NOCOUNT ON.

Reason: SQL Server always returns the number of rows affected when running DML operations (i.e. INSERT, DELETE, SELECT, and UPDATE). This becomes a big performance issue in sophisticated queries with a lot of joins. Because SET NOCOUNT ON does not count the number of rows affected, it improves efficiency.

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

As far as possible, avoid using GROUP BY, ORDER BY, and DISTINCT.

The SQL Server engine creates a work table and places the data on it when you use GROUP BY, ORDER BY, or DISTINCT. The data is then organized in a work table as requested by the query, and the final result is returned.

In your query, only use GROUP BY, ORDER BY, or DISTINCT if it’s absolutely necessary.

Conclusion

The requirements for complex and large applications are frequently extensive and difficult. As a result, we have to construct sophisticated SQL queries. Simple tweaks to your SQL Server queries will significantly improve response times. Thank you for taking the time to read my post. I hope you found it useful.