Is there any way - Using Like Operator - throws Index Scan - change to Index Seek

Question

Is there any way - Using Like Operator - throws Index Scan - change to Index Seek

John Paul-702936

SSCrazy

Points: 2182
More actions
July 29, 2010 at 4:20 pm

#223852

The Query looks Like this ...(just a scenario)
Select Col1, col2, col3 from
Sometable
Where ( Col1 Like '%','@Var1' ) or (Col2 like '%'+ @Var2)
Non Clustered Index on COL1
Non Clustered Index on Col2
Non Clustered Index on Col3
When we Use "=" operator in the place of LIKE
INDEX Seek is Used
Only the Problem with LIKE Throws INDEX SCAN
IS THERE ANY WAY TO AVOID THIS INDEXSCAN , PLEASE
Thanks
John

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

The reason you're getting an index scan is because of the % wildcard at the start. If you think about an index on a last name, you'll have an alphabetical index, A through to Z. If you want to find a specific name (using =), you can easily look up the index. For example, searching for "Johnson" is easy to find.

If you wanted to find any name beginning with "John" (LIKE 'John%'), you can again use the index to seek the rows - go to "John", and then scan through until the first four characters are no longer "John". This is still classified as an index seek in SQL Server's terminology.

If you wanted to find any name ending in "son" (LIKE '%son'), how would you do it with an alphabetical index? You'd need to scan every single row, checking to see if the name matches the condition. This is why you see the index scan.

If you will only be searching for specific values, you could build your own index, noting which names end with "son". However, you'll be in trouble if you then want to search for some other word.

If you're looking for a single word in the middle of a varchar column, then full-text search may come in handy. This will create a full text index, containing the significant words in the column.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

John Paul-702936 (7/29/2010)
IS THERE ANY WAY TO AVOID THIS INDEXSCAN , PLEASE

There is not. This is a non-SARGable predicate and cannot be used for a seek operation.

Think of it this way - if I gave you a telephone directory and asked you to find me all the people who had the letters 'enhuit' anywhere in their surname, how would you go about that? You can't take advantage of the fact that the telephone directory is ordered by surname, because you don't know what the surname starts with.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 3

How to overcome the Performance Problem here , Some solution / or any ideas Please ..

I am dealing with Number here .. Like

Where ( Col1 Like '%','@Var1' ) or (Col2 like '%'+ @Var2)

here @var1 & var2 are INT

--------------

Ref:-

Select Col1, col2, col3 from

Sometable

Where ( Col1 Like '%','@Var1' ) or (Col2 like '%'+ @Var2)

Non Clustered Index on COL1

Non Clustered Index on Col2

Non Clustered Index on Col3

When we Use "=" operator in the place of LIKE

INDEX Seek is Used

Only the Problem with LIKE Throws INDEX SCAN

IS THERE ANY WAY TO AVOID THIS INDEXSCAN , PLEASE

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If they're ints, why are you using LIKE and wildcards?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 5

Sorry Datatype is not INT , it's Varchar(15)

This COL1 is the - Phone number

The User can Enter any number From Front Webpage

Like Last 4 digits /or 7 digits

the number looks like 1234567890

If the User search for the 4567870 ,

The page need to Display the numbers which are ending with 4567890

This is the Actual Requirement , So i thought Using LIKE Will give the Extract Result (But poor Performance)

Thanks

John

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 6

You could try to create another column that reverses the phone number, index that and then reverse the input of the search and search based on the new column in the reverse order. Otherwise it's pretty plain, you can't search the middle of a row without table scan.

Tom