Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
subhaoviya
Posting Yak Master
135 Posts |
 Posted - 2012-11-22 : 06:29:32
|
| Hi Is it possible to optimize the below query?select * from table where column_name like '%boo%' or column_name like '%bar%' or column_name like ....... |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-11-22 : 07:32:37
|
| Not if you have preceding wildcard characters, those will force a scan. The OR conditions will also likely force a scan. If you're looking for whole words I'd suggest using full-text indexing, that should give you a performance boost. |
 |
|
|
subhaoviya
Posting Yak Master
135 Posts |
Posted - 2012-11-22 : 08:12:51
|
| Hi friend, Thanks for your reply, I am not looking for whole word search its wildcard only i am using 20 to 30 string in like function its performance is seems slow. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-22 : 10:23:01
|
Even if you have only one search string you are searching for, when your search is "column_name like '%boo%'", the performance will be poor.The reason for this is that SQL has no way of picking up the data you are asking for without having to examine EVERY row. That means a table scan.Compare that with the case where your search does not have the prefixed % wildcard. In that case, if there is an index on the column, SQL Server can use that index and return the rows you are looking for without scanning the entire table.This is very much similar to using a phone book. If you were asked to find phone numbers of people with the last name Smith, it is easy - you turn to the page that starts with S, and then find the m's under that and so on. On the other hand, if you were asked to find everyone whose last name was like '%mit%', you would need read the entire phone book so you will get not only last names such as MITTRA or MITCHUM, but also SMITH and AMIT.This is why Rob suggested enabling full text search.Also, to avoid the or conditions, you can put your search terms into a table and join on that table - something like this:select t.*from table t inner join YourSearchTermsTable y on t.column_name like '%'+y.searchcol+'%' This is not perfect, because if a single row matches more than one search term, you will get duplicate results, which you may need to filter out. |
|
|
|
|
|
|
|
|
OR condition in like function