SQL Query Performance Tips
Overview
The following TIER® Feed may be helpful to all of those users who write SQL Queries when designing forms or reports. The tips below will provide a brief overview of considerations to make when writing and optimizing SQL queries or procedures.
Tips
Avoid multiple joins in a Single Query, whenever possible
Try to avoid writing a SQL query using multiple joins that include outer joins, cross apply, outer apply and other complex sub queries. It reduces the choices for the SQL Server Optimizer to decide the join order and join type. Sometimes, the Optimizer is forced to use nested loop joins, irrespective of the performance consequences for queries with excessively complex cross apply or sub queries.
Eliminate cursors from the Query
Try to remove cursors from the query and use set-based queries; set-based query is more efficient than cursor-based. If there is a need to use cursors, then avoid dynamic cursors as it tends to limit the choice of plans available to the query Optimizer. For example, a dynamic cursor limits the Optimizer to using nested loop joins.
Creation and Use of Indexes
Indexes have a way of “magically” reducing data retrieval time. However, indexes can have an adverse effect on DML operations, which can degrade overall query performance, especially if part of a large(r) procedure. Although the SQL Server Optimizer can suggest needed indexes, it is important to understand that not all indexes will be beneficial, and some can be detrimental to query performance.
Statistic Creation and Updates
Statistics are used by the SQL Server Optimizer to determine the best plan to retrieve the data being requested by the query. If statistics are out-of-date, the Optimizer may not necessarily be using the most efficient plan to retrieve the data. When reviewing query execution plans, compare the estimated
number of rows to be retrieved against the actual number of rows retrieved. This will assist in determining if statistics are out-of-date and need to be updated.
Revisit Schema Definitions
Make sure that appropriate FOREIGN KEY, NOT NULL and CHECK constraints are in place where needed. Availability of the right constraint in the right place can help to improve the query performance. For example, FOREIGN KEY constraints can help to simplify joins by converting some outer joins or semi joins to inner joins, and the CHECK constraint also can help by removing unnecessary or redundant predicates.