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.
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.