Avoid LIKE Like A Plague

What is the best thing about using LIKE operator in T-SQL? It is very convenient to use in doing some few pattern searching.

Many of us are using LIKE just for this reason alone. For example, running the following query

SELECT first_name
FROM _name
WHERE first_name LIKE ‘%electra%’

on a several thousand row table would easily give you result in a blink of an eye (assuming that a non-clustered index was created on first_name column).

But let us try to go deeper as to how SQL Server executes the LIKE queries:

— Contains Search
SELECT first_name
FROM _name
WHERE first_name LIKE ‘%electra%’

Execution Plan Used = Index Scan

— Ends With, Where Last Character Can Be Any Character
SELECT first_name
FROM _name
WHERE first_name LIKE ‘%ann_’

Execution Plan Used = Index Scan

— Begins With Search
SELECT first_name
FROM _name
WHERE first_name LIKE ‘electra%’

Execution Plan Used = Index Seek

We know that every time SQL Server executes a query, its Query Optimizer tries its best to determine the best execution plan that it can do. It will always attempt to use an Index Seek wherever/whenever possible.

On the 1st and 2nd example, SQL Server failed to determine or find an appropriate index to use. Thus, it opted to use an Index Scan. In an Index Scan, SQL Server scans all the rows in an index. This means that SQL Server has to scan each row in an index to determine if the row qualifies in your search criteria. If you are searching for just 3 rows within a million row table, and those records are in the 8th, 9th and 10th, SQL Server will have to scan up to the millionth record of the index before it can return all the rows that you need. Now that is quite unacceptable to most of us. However, Index Scan isn’t bad at all. There are times where an Index Scan is faster then Index Seek. Every time you see in your execution plan that SQL Server is using Index Scan, take a look at the number of rows it is processing. If it is just a few thousands rows, then you are fine and Index Scan can be really fast.

On the 3rd example, SQL Server was able to determine that it can use an index effectively by using Index Seek. Index Seek, as we know means that SQL Server’s Query Optimizer was able to find a useful index to locate the records we are searching. If we are searching for 3 records in a million row table, SQL Server will return the rows in a blink of an eye.

So how would this information help us?

Often, when we develop apps, we tend to use a lot the LIKE operator. During this stage, we seldom find performance problems related to LIKE operator as typically, our tables are not well populated with large amount of data. In most cases, we often have a few hundreds of rows or less in our tables during development stages. Perhaps we are thinking that since we have created an index, then we are ok.

We will only notice the performance problems associated with LIKE when the tables gets populated heavily with real data.

Hope this helps us in understanding when and how to use the LIKE operator. Hey, you may want to explore SQL Server’s Full Text Searching.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s