Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Location
    London
    Posts
    21

    Red face Unanswered: Optimising Select statements which has a ‘LIKE’ where clause.

    Hi all

    I have been doing some development work in a large VB6 application. I have updated the search capabilities of the application to allow the user to search on partial addresses as the existing search routine only allowed you to search on the whole line of the address.

    Simple change to the stored procedure (this is just an example not the real stored proc):

    From:
    Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode = ‘W1 ABC’
    To:
    Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode LIKE ‘W1%’

    Now this is when things went a bit crazy. I know the implications of using ‘with(nolock)’. But seeing the code is only using the ID field to get the required row, and the database is a live database with hundreds of users at any one time (some updating), I think a dirty read is ok in this routine, as I don’t want SQL to create a shared lock.

    Anyway my problem is this. After the change, the search now created a Shared Lock which sometimes locks out some of the live users updating the system. The Select is also extremely SLOW. It took about 5 minutes to search just over a million records (locking the database during the search, and giving my manager good reason to shout abuse at me). So I checked the indexes. I had an index set on:

    strAddressLine1, strAddressLine2, strAddressLine3, strAddressLine4, strPostCode.

    So I created an index just for the strPostCode (non clustered).

    This had no change to the ‘Like select’ what so ever. So I am now stuck.

    1) Is there another way to search for part of a text field in SQL.
    2) Does ‘Like’ comparison use the index in any way? If so how do I set this index up?
    3) Can I stop a ‘Shared Lock’ being created when I do a ‘like select’?
    4) Do you have any good comebacks I could tell the boss after his next outburst of abuse (please not so bad that he sacks me).

    Any advice truly appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    a couple of answers

    1. I have been working on smaller database systems the last couple of years but as for number 1 try changing the query to "=" with a wild card "%" in the QA with the show execution plan on and see if the index is being used.

    2. I seem to remember that the like operator cancels the index. To check this I would execute the query in the QA and check the execution plan.

    3. Don't know off the top of my head.

    4. Tell your boss that software like the people who create it are imperfect things.

  3. #3
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    Like is one of those "fuzzy" things and does not use indexes. Postcodes are notoriously difficult to search on...

    In your example you give "W1%"

    realise that this will return W12 etc


    I in the time I had to do this tried to Narrow the user down to the local as in W1
    W12 etc

    Alternative prospect here

    Split your Postcode into two fields (I know it sounds wierd) but then you can do an = rather than a like and an index can be used!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try to run the query in the Query Analyzer. In the form that you posted the query, it ought to use the PostCode index. It ought to be able to ride the index as far as the first wildcard (percent sign in this case). If you examine the query plan, it might give you some idea of where the problem is.

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I get an index seek with a bookmark lookup

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(strPostCode varchar(10), Col2 int)
    GO
    
    INSERT INTO myTable99(strPostCode, Col2)
    SELECT 'W1Me',1 UNION ALL
    SELECT 'W2Me',2 UNION ALL
    SELECT 'W3Me',3 UNION ALL
    SELECT 'W4Me',4
    GO
    
    CREATE INDEX myTable99_strPostCode ON myTable99(strPostCode)
    GO
    
    --[CTRL]+K
    Select Top 3000 * from myTable99 with(nolock) Where strPostCode LIKE 'W1%'
    GO
    
    SET NOCOUNT OFF
    GO
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yeah, but that's using the code that they posted, which might or might not generate the same plan as the code they are actually using Not that I've ever been burned by different code being posted than what is actually run before, I just read about it in this book once...

    -PatP

  7. #7
    Join Date
    Oct 2004
    Location
    London
    Posts
    21

    Optimising Select statements which has a ‘LIKE’ where clause: Part2.

    First like to thank everyone who replied. Very much appreciated.

    Unfortunately I am still not much closer at finding a solution. I have done the execution plan thing although I can see a index seek, I don’t think the index I have created is being used (but I am not sure).

    If I delete the index, then the search time on about 500,000 records (I’m testing from a subset of the total number of rows in the table) is about the same as when the index is present. If I do a ‘=’ search, then with an index runs in less than a second and without takes much longer.

    Anyway if anyone has any other ideas or a total different approach I can take then please let me know. Also if anyone knows of any websites or books that look into ‘like selects’ more deeply than just giving you the syntax, like every book and site I have found, then that would be good too.

    Regards

    Eamon.

  8. #8
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    1. Try use index hint:
    Select Top 3000 * from TL_ClientAddresses with(nolock, INDEX (your_index_name)) Where strPostCode LIKE ‘W1%’
    2. Try update statistics:
    EXEC sp_updatestats
    3. Try change query:
    Select Top 3000 * from TL_ClientAddresses with(nolock)
    Where strPostCode >= ‘W1 ’ AND strPostCode =< ‘W1z’
    4. If your query fetch more then 20% table then optimizer don't use index

  9. #9
    Join Date
    Oct 2004
    Location
    London
    Posts
    21

    Talking mwolf! Your a Star!!!

    mwolf! Your a Star!!!

    Select Top 3000 * from TL_ClientAddresses with(nolock, INDEX (your_index_name)) Where strPostCode LIKE ‘W1%’

    Works a treat!!! Gone from over a minute down to 6 seconds just by adding the 'INDEX()' statement.

    I think that's the answer to my problem, Thankyou very much!!!

    Regards

    Eamon.

Posting Permissions

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