Alternative to using the Like Predicate with Wildcard token at the start

  • I'm trying to determine how to perform the below without using the Like predicate:

    SELECT T.col1, T.col1

    FROM MYTABLE T

    WHERE T.col1 Like'%SearchText%' Or T.col2 Like'%SearchText%'

    This is what I've come up with so far:

    SELECT T.col1, T.col1

    FROM MYTABLE T

    WHERE IsNull(CharIndex('SearchText',T.col1),0) > 0 OR IsNull(CharIndex('SearchText',T.col2),0) > 0

    Alternatively I'm wondering if the above would be better as 2 queries union'd so as to avoid using OR like this:

    SELECT T.col1, T.col1

    FROM MYTABLE T

    WHERE IsNull(CharIndex('SearchText',T.col1),0) > 0

    UNION ALL

    SELECT T.col1, T.col1

    FROM MYTABLE T

    WHERE IsNull(CharIndex('SearchText',T.col2),0) > 0

     

    I've read that using a wildcard at the start of the Like predicate eliminates the use of indexes and always requires a table scan.  The problem for me is the table the above is looking thru is the largest in the DB ranging from hundreads of MB's to several GB's depending on the client. NOTE: In the above example c1 & c2 are both VARCHAR(255) columns that are not part of any index.  I have to use the tables as is so no new indexes or other changes to the table involved.

    Any thoughts/suggestions?

    Thanks

    Kindest Regards,

    Just say No to Facebook!
  • Well, if no new indexes are allowed then you don't have to worry about performance because you'll be able to guarantee that it will become poor as the table grows.  Any version of this query will produce a full table scan. 

     

    For starters, I would recommend indexing Col1, Col2.  Then I would recommend that you put each of the versions that you posted into Query Analyzer and run them both with SET STATISTICS IO ON.  Then view the execution plans and review the I/Os statistics and see which is most efficient on your system. 

     

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • If there are no indexes then there is nothing to eliminate.

    Right?

    If you need to find all chapters which mention "index" in a book having no index you need to scan (read) whole book.

    Right? Or you may think about another way?

    > I have to use the tables as is so no new indexes or other changes to the table involved.

    If you cannot do anything than you can do nothing.

    It's that simple.

    _____________
    Code for TallyGenerator

  • Given your boundary conditions (no indexes...), a full table scan is unavoidable.

    Anyway, you should not use the "UNION" approach; this will cause TWO tablescans instead of one. And I would think that a built-in operator such as "LIKE" - even with a starting wildcard - is slightly faster than using "CharIndex" functions. It is definitely more readable.

  • Hi,

    you could try if

    SELECT T.col1, T.col1

    FROM MYTABLE T

    WHERE COALESCE(T.col1+T.col2, T.col1, T.col2, '') Like '%SearchText%'

    is marginally faster (if T.col1+T.col2 remains less then 8165? Byte)...

    regards

    karl

     

    Best regards
    karl

  • Thanks to everyone who has replied so far, especially with providing such detailed responses.  When I posted the orginal question I now realized I was not conveying everything I was thinking.  In this example the WHERE clause contains more conditions then just the search for text in col1 & col2.  A better example of how the query would look is this:

    SELECT T.col1, T.col1

    FROM MYTABLE T

    WHERE T.Col3 In([Condition1]) And T.col4 = [Condition2]

     And (IsNull(CharIndex('SearchText',T.col1),0) > 0 OR IsNull(CharIndex('SearchText',T.col2),0) > 0)

    Where T.col3 is the PK (data type = NUMERIC) and T.col4 is another text field.

    When I mentioned that it was my understanding that using Like with a wild card at the start would eliminate the use of indexes I meant that it would eliminate the entire query from using indexes and not just from using them when resolving the Like condition.

    Thoughts/Comments?

    Kindest Regards,

    Just say No to Facebook!
  • 1st, there is no point for using ISNULL.

    ISNULL(Whatever, 0) may be > 0 only if Whatever > 0

    2nd, Try to avoid IN (Condidtion). Use INNER JOIN or WHERE EXISTS instead.

    3rd, if there are indexes on Col3 and Col4 they will be used (if Conditions are built right)

    If number of selected by those conditions rows is not significant then text scans should not slow down the query very much.

    _____________
    Code for TallyGenerator

  • Basically, if the requirement is to find the string 'mystring' anywhere within that 255 varchar column

    (ie in the beginning, middle or end) - then there is *no* reason not to use

    LIKE '%mystring%'

    Don't mess with functions or other creative innovations, it just makes the query harder to read and understand.

    If LIKE '%mystring%' is what is required, then use it.

    As for 'supported by indexes',  in general - LIKE is an operator that may benefit from indexes, functions almost always doesn't.

    /Kenneth

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

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