July 29, 2010 at 4:20 pm
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
July 29, 2010 at 5:43 pm
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.
July 30, 2010 at 2:43 am
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
July 30, 2010 at 7:05 am
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
July 30, 2010 at 7:18 am
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
July 30, 2010 at 7:25 am
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
September 21, 2010 at 8:36 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy