Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question is related to my old question. The below query was taking 10 to 15 seconds to execute:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE (Charindex('123456789',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0) 

In some articles I saw that using CAST and CHARINDEX will not benefit from indexing. There are also some articles that say using LIKE '%abc%' will not benefit from indexing while LIKE 'abc%' will:

http://bytes.com/topic/sql-server/answers/81467-using-charindex-vs-like-where http://stackoverflow.com/questions/803783/sql-server-index-any-improvement-for-like-queries http://www.sqlservercentral.com/Forums/Topic186262-8-1.aspx#bm186568

In my case I can rewrite the query as:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE [company].dbo.[customer].[Phone no]  LIKE '%123456789%'

This query gives the same output as the previous one. I have created a nonclustered index for column Phone no. When I execute this query it runs in just 1 second. This is a huge change compared with 14 seconds previously.

How does LIKE '%123456789%' benefit from indexing?

Why do the listed articles state that it will not improve performance?

I tried rewriting the query to use CHARINDEX, but performance is still slow. Why does CHARINDEX not benefit from the indexing as it appears the LIKE query does?

Query using CHARINDEX:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [Company].dbo.[customer]
 WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 ) 

Execution plan:

enter image description here

Query using LIKE:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [Company].dbo.[customer]
 WHERE[Company].dbo.[customer].[Phone no] LIKE '%9000413237%'

Execution plan:

LIKE query plan

share|improve this question
1  
Please post the execution plans for both the Charindex and LIKE '%...' queries. –  Martin Smith Jul 24 '13 at 11:56
    
@MartinSmith i have added execution plan –  IT researcher Jul 24 '13 at 12:13
1  
What is the estimated rows coming out of the scan in both of them? You can see this by hovering over the arrows. Also how many rows are actually returned and how many rows in the customer table? –  Martin Smith Jul 24 '13 at 12:14
3  
The queries are different. One includes [Phone No] in the SELECT list, and this probably has an impact on which index SQL Server chooses. Try making sure the rest of the query is identical. –  Aaron Bertrand Jul 24 '13 at 12:35
    
@AaronBertrand I have updated the query and execution plan in question.Now both queries do the same. Before i posted the query by mistake.If i not include [Phone No] in the SELECT list then also same performance exists –  IT researcher Jul 24 '13 at 13:00
add comment

2 Answers

How does LIKE '%123456789%' benefit from indexing?

Only a little bit. The query processor can scan the whole nonclustered index looking for matches instead of the entire table (the clustered index). Nonclustered indexes are generally smaller than the table they are built on, so scanning the nonclustered index may be faster.

The downside, is that any columns needed by the query that are not included in the nonclustered index definition must be looked up in the base table, per row.

The optimizer makes a decision between scanning the table (clustered index) and scanning the nonclustered index with lookups, based on cost estimates. The estimated costs depend to a great extent on how many rows the optimizer expects your LIKE or CHARINDEX predicate to select.

Why do the listed articles state that it will not improve performance?

For a LIKE condition that does not start with a wildcard, SQL Server can perform a partial scan of the index instead of scanning the whole thing. For example, LIKE 'A% can be correctly evaluated by testing only index records >= 'A' and < 'B' (the exact boundary values depend on collation).

This sort of query can use the seeking ability of b-tree indexes: we can go straight to the first record >= 'A' using the b-tree, then scan forward in index key order until we reach a record that fails the < 'B' test. Since we only need to apply the LIKE test to a smaller number of rows, performance is generally better.

By contrast, LIKE '%A cannot be turned into a partial scan because we don't know where to start or end; any record could end in 'A', so we cannot improve on scanning the whole index and testing every row individually.

I tried rewriting the query to use CHARINDEX, but performance is still slow. Why does CHARINDEX not benefit from the indexing as it appears the LIKE query does?

The query optimizer has the same choice between scanning the table (clustered index) and scanning the nonclustered index (with lookups) in both cases.

The choice is made between the two based on cost estimation. It so happens that SQL Server may produce a different estimate for the two methods. For the LIKE form of the query, the estimate may be able to use special string statistics to produce a reasonably accurate estimate. The CHARINDEX > 0 form produces an estimate based on a guess.

The different estimates are enough to make the optimizer choose a Clustered Index Scan for CHARINDEX and a NonClustered Index Scan with Lookups for the LIKE. If you force the CHARINDEX query to use the nonclustered index with a hint, you will get the same plan as for LIKE, and performance will be about the same:

SELECT
    [Customer name],
    [Sl_No],
    [Id]
FROM dbo.customer WITH (INDEX (f))
WHERE 
    CHARINDEX('9000413237', [Phone no]) >0;

The number of rows processed at runtime will be the same for both methods, it's just that the LIKE form produces a more accurate estimation in this case, so the query optimizer chooses a better plan.

share|improve this answer
    
Do you happen to know if the PATINDEX() > 0 form uses the special statistics as well as the LIKE one? –  Andriy M Jul 24 '13 at 14:15
    
@AndriyM No, PATINDEX results in a guess too. The 'issue' is not so much with the CHARINDEX or PATINDEX itself as it is with the > 0 logic. It's just a little too opaque way of expressing the logic for the optimizer to understand. –  Paul White Jul 24 '13 at 14:18
    
@PaulWhite You have told that "If you force the CHARINDEX query to use the nonclustered index with a hint, you will get the same plan as for LIKE, and performance will be about the same." So please tell how i can force index in my case.I just want to try it once. –  IT researcher Jul 25 '13 at 6:17
    
@ITresearcher The answer now shows the index hint used with CHARINDEX. –  Paul White Jul 29 '13 at 3:13
add comment

SQL Server maintains statistics on substrings in string columns in the form of tries that are usable by the LIKE query but not by the CHARINDEX.

See the String Summary Statistics section for more about this.

A couple of important caveats are that any escaping of wildcards must be done with the proprietary square bracketing technique rather than the ESCAPE keyword and that for strings longer than 80 characters only the first and last 40 characters are used.

WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 ) 

will just use the standard guess for an inequality predicate that 30% of the rows will be returned.

The LIKE query (in your case) presumably estimates much fewer rows will match the predicate.

Note that the leading wildcard still prevents an index seek. An entire index is still scanned but it uses a different one that is narrower than the clustered index. The narrower index doesn't cover all columns used by the query so the second plan requires a key lookup to retrieve the missing columns.

This plan is extremely unlikely to be chosen with the 30% estimate. SQL Server will consider it is cheaper to scan the entire clustered index and avoid that many lookups. See this article on the tipping point for additional examples.

share|improve this answer
    
i am not clear with your explanation. Are you saying that using like is better than charindex? –  IT researcher Jul 24 '13 at 13:03
3  
@ITresearcher - Yes, potentially, instead of just using a blanket guess of how many rows will match the condition (30%) it can look at the LIKE pattern supplied and the string summary statistics and derive a more accurate estimate. Armed with that it might choose a different and more appropriate plan. –  Martin Smith Jul 24 '13 at 13:08
3  
...or, in the "worst case", the same plan. –  Aaron Bertrand Jul 24 '13 at 13:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.