Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: quicker way of writing a LIKE query

    Hi

    I have two product tables in two different databases, both contain thousands of records. I have to write a query that suggests matches on similar codes, and have come up with:

    SELECT TB1.product, TB2.product
    FROM TB1
    JOIN (select distinct product
    from db2.dbo.TB2) as TB2 --this table has PK of product and warehouse
    ON TB2.product LIKE '%' + TB1.product+'%'

    which DOES work, but because the table have many rows,takes time to do it... is there a way of rewritting this query, so it gives a faster result?

    Thanks in advance...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem that I see is that you are using a definition that requires a table scan for TB2 in order to determine row-by-row if the value of TB1.product exists anywhere in TB2.product.

    This type of search is an ugly process to implement using just a set based language like SQL. This kind of problem is why Full Text Search was added to MS-SQL. Beware, in that Full Text Search is definitely NOT a "free lunch", there is definitely an overhead cost associated with it.

    There are other ways to speed up the process, but none of them are very pretty. My first thought is to evaluate the cost/benefit of using Full Text Search, and only to pursue other answers if you decide not to use it and really need something else.

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would like to see the WHERE clause using full text, please

    WHERE CONTAINS( ... ????

    your guidance here, pat, will, as usual, be deeply appreciated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd like to see some sample data, with further clarification on what he considers a partial match.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    who said partial match?

    here's some sample data showing columns which match
    Code:
    TB1.product  TB2.product
    shampoo      Kerastase Resistance Bain Volumactive Shampoo Volumizing
                 philosophy cinnamon buns shampoo, conditioner, & shower gel
                 H2O Plus Sea Marine Revitalizing Shampoo
    shaving      Proraso Eucalyptus & Menthol Shaving Cream 150 ml. 
                 The Art of Shaving Unscented Pre-Shave Oil
                 Tweezerman Badger Hair Shaving Brush
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    who said partial match?
    LIKE implies partial matches, whether he wishes or not. And where did you get his data, or did I miss a smiley somewhere?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    And where did you get his data
    i made it up

    his first post said that his query works

    this data fits that query

    are you smiley-deprived? here, have a few: :blush:
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks. I needed those.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    i would like to see the WHERE clause using full text, please
    I know... As you are fond of reminding me, you are so NOT a DBA. This one falls outside of the scope of solutions in which you like to play, it is one of those tasks where you just get the job done and move on with life.

    The CONTAINS function doesn't work the way you are implying, I don't know of a completely set-based solution for this kind of problem. I would retrieve the rows from the smaller table to a client (such as VBA within a DTS package), and build a temp table of the matches or partial matches so that I could return that. You could also do it with a cursor and dynamic SQL, but that strikes me as even uglier. This is ugly, but it will perform better than the "brute force" of the LIKE approach.

    -PatP

  10. #10
    Join Date
    Feb 2005
    Posts
    76

    Smile

    hey

    Thanks for all the replies...

    I had advanced a wee bit...
    Basically I have been able to cut down the amount of rows in TB1 on some factors, and dumped it into a temp table...

    I will look into the Full Text Search : )

    Thanks again

Posting Permissions

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