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...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 (http://msdn2.microsoft.com/en-us/library/ms142571.aspx) 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|||i would like to see the WHERE clause using full text, please
WHERE CONTAINS( ... ??
your guidance here, pat, will, as usual, be deeply appreciated|||I'd like to see some sample data, with further clarification on what he considers a partial match.|||who said partial match?
here's some sample data showing columns which match
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|||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?|||And where did you get his datai 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: :rolleyes:|||Thanks. I needed those.|||i would like to see the WHERE clause using full text, pleaseI 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|||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
aaguiar wrote:
darkknight187: