Improve Performance of SQL Queries

This article explores effective strategies and techniques to optimize SQL queries, enhancing their performance and efficiency. By implementing these SQL query optimization tips, you can significantly improve the execution speed and overall performance of your database system.

The main goal of tuning queries is to decrease its working time by identifying fragments of SQL code that may cause poor query performance. The query runtime can be evaluated by monitoring key metrics of the particular execution plan via SQL Server Query Optimizer.

The SQL Server Query Optimizer utilizes a cost-based approach to optimize query execution. Each potential execution plan is assigned a cost, representing the computational resources required. The Query Optimizer's goal is to analyze the available plans and select the one with the lowest estimated cost. For complex SELECT statements, which can have numerous potential plans, the Query Optimizer doesn't exhaustively evaluate every combination. Instead, it employs sophisticated algorithms to identify an execution plan with a cost that closely approximates the minimum possible cost. By using this approach, the Query Optimizer efficiently finds an optimal plan for query execution.

Add Indexes

Adding proper indexes (that probably were missed on design stage by mistake) to database tables can significantly enhance query performance and efficiency.

In SQL Server, the query optimizer generates an execution plan when executing a query. If it identifies the absence of an index that could improve performance, it includes this information in the warning section of the execution plan. This suggestion highlights the specific columns that could benefit from indexing and provides insights into how performance can be enhanced after implementing the recommended indexes. By heeding these suggestions, you can optimize the execution of your SQL queries and achieve improved performance.

Do not Use Multiple OR in the WHERE Predicates

To improve performance of queries having multiple conditions, avoid using OR operator within a single WHERE predicate. SQL Server does not process OR operations efficiently, as it evaluates each component separately, which can result in poor performance. Instead, either split the query into separate parts with distinct search expressions or find alternative approaches to combine the conditions effectively. For example, the query:

SELECT FROM people WHERE first_name='John' OR last_name='Doe'

should be optimized as follows:

SELECT FROM people WHERE first_name='John' 
UNION 
SELECT FROM people WHERE last_name='Doe'

This trick allows SQL Server use the relates indexes, and the query will be optimized.

Reduce Number of JOINs

When including multiple tables in a query and performing joins, there is a risk of overloading the query and potentially creating an inefficient execution plan. The SQL query optimizer needs to determine the order of table joins, how to apply filters and aggregations, and other optimization factors when generating the execution plan.

To achieve more efficient query plans, reduce number of JOIN operators in the query. Remove redundant JOIN by breaking down a single query into multiple separate queries and later joining them. This approach helps streamline the query and remove components that may degrade the performance.

Do not Use Heading Wildcards

Wildcards act as a placeholder at the beginning/end of regular expressions in search or filtering conditions. Use wildcards at the end of a phrase only to make SQL Server extract the data faster through the corresponding indexes. For example:

SELECT FROM people WHERE first_name LIKE 'Jo%'

Some tasks may require to search by the last symbols of a phrase, for example phone numbers ending by "321". The straight forward approach is to use the heading wildcard "%321", however it is not optimized. The workaround is to create a computed column that is REVERSE of the original and search across it using trailing placeholders. For the task of searching phone numbers ending by "321" it can be done as follows:

CREATE TABLE people(
  id INT IDENTITY PRIMARY KEY, 
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  phone VARCHAR(50),
  reversed_phone AS REVERSE(phone) PERSISTED
)
GO

CREATE INDEX idx_reversed_phone ON people(reversed_phone)
GO

--searching for phones that end in 321
SELECT * FROM people WHERE reversed_phone LIKE '123%'

Avoid Using SELECT *

Some queries extract more data that it is required due to inaccurate design. Using SELECT * to extract all table columns leads to essential overhead on the large databases. Validate all of such queries to make sure you actually need the data from every column. Otherwise, specify the exact column list to make SQL Server retrieve only necessary data that will save the system resources.

If the same fields are extracted regularly, build the covering indexes on these columns. Index containing all the fields required by query and can significantly improve the performance.

Have questions? Contact us