Index usage by %Like% operator – Query Tuning

  • Článek

Ok, so this one is interesting. I was reading an Internal DL (Distribution Group) post on tuning %Like% operator performance. I find some confusion in various places about index selectivity of ' %Like% ' operator, and I decided to do few test myself. Hopefully test details below can help a bit:

Problem Statement:
- While optimizing high CPU consuming queries on 3rd party application, verified that most of the queries are using '%LIKE%' operator.
- Interestingly enough, while some of these queries are going for "INDEX SEEK" while others are going for "INDEX SCAN". Why ?

Facts:
As per SQL BOL - (https://msdn.microsoft.com/en-us/library/ms179859(SQL.90).aspx)

  • 'LIKE' Determines whether a specific character string matches a specified pattern
  • During pattern matching, regular characters must exactly match the characters specified in the character string.

More importantly, wildcard char % can be placed in one of below Four ways:

1. SEARCH-STRING%
- The SEARCH-STRING% will perform INDEX SEEK and return data in least possible time.

2. %SEARCH-STRING
- When using %SEARCH-STRING  it's much less likely to use the index, however it still may at least perform an INDEX SCAN on a full or partial range of the index.

3. %SEARCH-STRING%  
- When using %SEARCH-STRING%  it's much less likely to use the index, however it still may at least perform an INDEX SCAN on a full or partial range of the index.

4. SEARCH%STRING
- The SEARCH%STRING will perform INDEX SEEK and generate an initial result set, containing the values that match first string i.e. SEARCH%. Next it will scan through the values to get second string i.e. %STRING

Lab-Test:
Product: SQL Server 2005 (SP2) x64
Database: Northwind
Table: dbo.Customers

Here’s the output from SP_HELPINDEX on dbo.Customers before we proceed. Please note, “Customer” column already has a clustered index on it.

sp_helpindx

[Example 1.] Select on … SEARCH-STRING%

blike_1

[Example 2.] Select on …  %SEARCH-STRING

blike_2

[Example 3.] Select on … %SEARCH-STRING%

 blike_3

[Example 4.] Select on … SEARCH%STRING

Note: SEARCH%STRING will return all values starting with 'S' and ending with 'A'

blike_4

The moral of the story?  You can tune/speed-up your ‘%Like%’ operator queries by making use of indexes. Just be aware of above limitations and write your queries accordingly.

Enjoy! and Thanks for reading.

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Comments

  • Anonymous
    November 29, 2009
    The wildcard character can also be placed in the middle of a string e.g. 'John%on' will match both 'Johnson' and 'Johnston'.

  • Anonymous
    November 30, 2009
    The comment has been removed