born2achieve (10/13/2013)
Hi Jeff,am back,
i got one more tricky situation from my client. i frightened to hear about this concept from them. the concept will be i will have to take the product name from table 1 and search it with %product name% search condition. not whole word matching.
In your example, after we split the comma separated values into temp table , fetch each item from table 1 and we have map it with %table1.productname% onto temp table. i am wondering about this ugly concept. because it will kill the time. do you have any suggestion on this concept. sample below,
if the product name on table 1 is "milk" and on the temp table if we have "milk with fat","milk with out fat","milk with less fat" then we have to fetch these three product name. for this i hope we should have to use % table1.productname %.
could you please
Would you also want to pick up "Milk Glass", "Milk Paint", "Milky Way Candy Bar", "Dried Milk", "Milky Ammonia", "Milk Stain Remover", "Chocolate Milk Syrup", etc? Even FTS (Full Text Search) is going to have a problem with leading wildcards because leading wildcards either aren't SARGable (can't use a seek) or you have to generate huge amounts of word parts to facilitate SARGable leading wildcard searches. And, are they going to want to be able to search for a mult-word entry regardless of the order of the search words entered?
Fortunately, you only have a thousand or so products. It's not like you're trying to build a catalog for Grainger (which sells thousands and thousands of different parts and sizes of parts) or a document search like Google. Just do the normal double-ended wildcard search on Table 1 and see how it pans out. Even though it won't be SARGable, a nice narrow unique index that includes the product name and the product ID may help a bit here simply because the data is more narrow than the original Table 1 (assuming there are more than just the 3 columns you posted... I could be wrong). Maybe not.
If they want something faster, then it's going to take a basic redesign of tables and data. For example, there should be a table of categories that should be searched for "Milk" related products and they should search for categories instead of individual products. That's likely the original intent of Table 2 but they fell way short on that. Such further design is also way beyond what should be solved in a forum like this one.
--Jeff Moden
Change is inevitable... Change for the better is not.