The use of functions in the WHERE clause of SQL queries can significantly impact performance. This is especially true when SQL Server must calculate the function for each row in the table. Therefore, using raw data in your WHERE clause is generally more efficient whenever possible. In this post, we’ll compare the use of CHARINDEX and LIKE in the WHERE clause, supported by real-world statistics.
When Functions Complicate Things
Consider a scenario where you need to fetch all persons whose last name starts with ‘D’ from the
Person table in the
AdventureWorks2019 database. You might think of using the
CHARINDEX function or the
Here are the queries:
SET STATISTICS IO ON -- Query with Charindex SELECT * FROM AdventureWorks2019.Person.Person WHERE CHARINDEX('D', LastName) = 1; -- Query with Like SELECT * FROM AdventureWorks2019.Person.Person WHERE LastName LIKE 'D%';
The first query uses the
CHARINDEX function to find the position of ‘D’ in the
LastName column and checks if it’s the first character. The second query uses the
LIKE keyword to match any last names that start with ‘D’.
We executed these queries and analyzed the statistics:
- CHARINDEX query:
- Logical reads: 3880
- Physical reads: 0
- Read-ahead reads: 3864
- LIKE query:
- Logical reads: 1717
- Physical reads: 0
- Read-ahead reads: 0
LIKE condition clearly performs significantly fewer logical reads than the
CHARINDEX function, demonstrating that avoiding using functions in the WHERE clause is more efficient whenever possible.
Here is the execution plan for the query where we can clearly see that the second query with Like is performing better than the query with CHARINDEX.
The Problem with CHARINDEX
CHARINDEX in the WHERE clause forces SQL Server to execute this function for every row in the table. If the table has many rows, this can be time-consuming and lead to a significant performance hit. Furthermore, it prevents SQL Server from effectively using indexes on the columns in the function, potentially leading to a full table scan.
The Efficiency of LIKE
In contrast, the
LIKE keyword can be far more efficient, especially when used with an index on the
LastName column. The
LIKE 'D%' condition is sargable, meaning that SQL Server can effectively use an index to optimize the operation. It only needs to search the index tree for entries that start with ‘D’, which is significantly quicker than examining every row in the table.
LIKE provides a performance benefit over
CHARINDEX for checking the first character, it’s important to note that this method won’t work if you’re checking for a character in any position other than the first. For example, if you need to find last names where ‘D’ appears as the second character, you would need to use
LIKE '_D%' wouldn’t work as expected.
Additionally, the performance difference is negligible if there is no index on the
This comparison, backed by real-world data, demonstrates the significant impact that using functions in the WHERE clause can have on the performance of your SQL Server queries. By understanding the implications and knowing how to use more efficient alternatives where possible, you can optimize your queries and improve the overall efficiency of your SQL Server operations. Always consider the data type, collation, and indexing strategy when writing your SQL queries to ensure optimal performance.
You can always reach out to me on Twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)