Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Unanswered: Like clause not using index

    A user has a query using Like clause with % and is not using indexes is there any way to force index .

    " Where Lname like '%'+@lname+'%'

    Please suggest somthing to improve the perfiormance

    thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can use (INDEX=index_name) optimizer hint.

  3. #3
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Hi

    Would you please post the syntax for it
    Thanks

    Here is the query

    select * from

    Where NME_PROSPECT_FULL LIKE '%John smith%


    index is NME_IDX
    thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not going to help you any.

    A wildcard in front of an expression, will always cause a scan...

    I imagine that if there is an index on last anme, that it would scan it...

    Did you do a show plan?

    Also, use Index hints as a last resort
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2002
    Posts
    58
    Originally posted by rdjabarov
    You can use (INDEX=index_name) optimizer hint.

    Well, you can certainly specify the optimizer hint, but it won't help the performance any, and could easily hurt it.


    Sad fact is, NO index will help if you lead off your target string with a wildcard. Think of it like this. The fact that a dictionary is in alphabetic order won't help you find all the words that have 'TIO' somewhere in the middle. You'll still have to look at every word in the dictionary. Same with this query.

    If you specify the index hint, it engine will still have to scan the entire index looking for matching values. And if the index you specify doesn't cover all the needed columns, the engine will ALSO have to do a bookmark lookup in the clustered index or heap to find those other columns. A clustered index scan is usually faster than an index scan + bookmark lookup (as opposed to index SEEK + bookmark)

    If the logic of the situation will let you get correct results by using this

    Where NME_PROSPECT_FULL LIKE 'John smith%

    ie, you know you have the starting characters, the engine will probably use the index without have to tell it to.
    Last edited by Steve Duncan; 12-22-03 at 15:33.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •