1

What can I use instead of LIKE in the queries? As LIKE has poor performance impact on the search condition.

My scenario:

SELECT 
    *
FROM 
    MetaDataTag
WHERE 
    Name LIKE 'SUN RCC%'

I cant use contains as a Full text index is required on the table, which I am not opting for.

Suggestions will be very helpful.

talegna
  • 2,407
  • 2
  • 19
  • 22
SESHI
  • 33
  • 2
  • 7
  • why dont you use like on certain columns then on entire table ?`(select *) ?`that would take time – vhadalgi Nov 27 '13 at 11:16
  • Another option is to use the `CHARINDEX` (See: http://technet.microsoft.com/en-us/library/ms186323.aspx) if the returned value is greater than 0 it exists in the string. Unfortunately as `CHARINDEX('123','1')` and `CHARINDEX('123','5')` both return 0 it can't quite determine whether the contained string is either the start or doesn't exist. You could work around this by adding a prefix to the string being compared but personally I'd say in this instance `LIKE` is a reasonable solution. – talegna Nov 27 '13 at 11:20
  • Another option is to look at the contents of `LEFT(str)` (See: http://stackoverflow.com/questions/9493844/is-there-startswith-or-contains-in-t-sql-with-variables) – talegna Nov 27 '13 at 11:22
  • Are you saying that it has poor performance on this particular query or are you going on advice that `LIKE` has poor performance in general? If the latter, it's simply not true; assuming that you have an index that supports searching on the Name column, the optimizer can use that index to satisfy your query since you don't have a wildcard at the beginning of your pattern. If the former, check to make sure that you have a covering index for your query. – Ben Thul Nov 27 '13 at 15:23

2 Answers2

7

In your particular case, LIKE is not a bad option because you are only using a postfix wildcard. Your query can actually use an index on the Name column.

Have a look at this visual explanation why it works: http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

Markus Winand
  • 8,371
  • 1
  • 35
  • 44
0

try this..

SELECT 
    *
FROM 
    MetaDataTag
WHERE PATINDEX('SUN RCC%', Name ) != 0
RobertKing
  • 1,853
  • 8
  • 30
  • 54