Performance of Like vs Left/Right for Indexed and Non Indexed Columns

I have have heard and read many times over the years that Left or Right should always be used, where possible, instead of Like.  On the surface, this does sound logical since the Like command is often used for pattern searching and one could assume that the Like command is causing some extra work to be performed by the SQL server engine even though the predicate expressions are essentially equivalent. I personally have always assumed that they operated equivalently, however, I had never performed a performance analysis to infer the true answer.  Also, was curious to find if there was a difference in performance when applied specifically to an indexed column vs non-indexed column.

The names and data structures in this example have been changed to protect the innocent. For this test, assume [SOME_TABLE] has 530 million records and contains two columns: [INDEXED_COLUMN], and [NONINDEXED_COLUMN].  Indexed_Column has a clustered index and Nonindexed_Column does not have an index.  We will run a test using like and left against each column using a basic count(*) command.

Test 1 – Non Indexed Column

Output for queries 1 and 2 = 63 million.

  • Query 1 used like and  averaged 102 seconds over 5 measurements.
  • Query 2 used left and averaged 103 seconds over 5 measurements.

Execution plans for queries 3 and 4:like vs left no index

Test 2 – Indexed Column

Output for queries 1 and 2 = 87 million.

  • Query 1 used like and averaged 3 seconds over 5 measurements.
  • Query 2 used left and averaged 14 seconds over 5 measurements.

Execution plans for queries 1 and 2:like vs left index

Conclusion

You can see from the results that Like is actually considerably faster than Left when applied to an indexed column.  This is because Like is treated as a computed column, which can utilize indexes, while Left is a function which can not utilize indexes.  If an index is not involved then Like and Left perform the same.

There might be additional factors that impact performance differences which I am not considering such as other index types, and other data types.  I came across another blog (http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex) where a similar test was done.  Surprisingly, their results showed that Chardindex is the most efficient out of Like, Substring, Left/Right, and Charindex, and their results between Like and Left/Right are quite different than mine.  At some point I’d like to go through their code and identify exactly what is different.

Edit: The author reached out to me and mentioned that the main difference between our results is likely due to my analysis being ran on SQL Server 2008 R2 vs theirs being done on SQL Server 2014 and there have been changes to the query engine between versions.  I encourage anyone seeing this to check out Dave’s blog.

More about Indexes on Computed Columns: https://msdn.microsoft.com/en-us/library/ms189292.aspx

2 thoughts on “Performance of Like vs Left/Right for Indexed and Non Indexed Columns”

  1. Hiya. Thanks for referencing my blog in your article.

    Two things to try:
    1) my test was performed using SQL Server 2014. Microsoft altered the way it calculates its execution plan or something from versions 2012 & earlier to 2014 and later. They’ve definitely altered the cardinality estimator which affects query plan and performance. Your blog article currently doesn’t state what version of SQL Server you’re using, so that could have an impact.

    2) You have the source code I used. Try doing a blanket copy/paste into your SQL server and see what you get. 🙂

    1. Hey Dave, I will make it a habit of adding the SQL server version I am using into my posts. In my test I was using 2008 R2. I will try running your source code and report back.

Leave a Reply

Your email address will not be published. Required fields are marked *