Ronald S. Cook wrote:
I have a table of keywords (hundreds/thousands of records):
KeywordID KeywordName
--------- -----------
1 Apple
2 Orange
3 Pear
I then have a table of products (also hundreds/thousands of records):
ProductID ProductName ProductDescription
--------- ----------- ------------------------------------
123 Apple Tree Better than an orange tree, this...
124 Great Scent This great scent smells like orange...
What's the most efficent way to search the entire product table and
return all records that have any of the keywords from the keyword
table (in eiter productname or peoductdescription)?
DO YOU THINK I'm going to need to send all this data to a DataSet and
iterate through in C# code, or do you think I could do it all in a
proc?
First, enable full text indexes on the database and buld a fulltext index
for the Product table. That'll improve query speed.
You can write the query easily in SQL:
select
distinct p.*
from
Product p
join Keyword k
on p.ProductName like '%'+k.KeywordName+'%'
or p.ProductDescription like '%'+k.KeywordName+'%'
there are many variations of the query possible, but that one should get you
started (i.e. it may not be the most efficient, but it will work).
If what you really want is a list of Products that contain each and every
keyword (which isn't exactly what you asked for, but seems like something
you might want):
select
k.KeywordID,
p.ProductID
from
KeyWord k
join Product p
on p.ProductName like '%'+k.KeywordName+'%'
or p.ProductDescription like '%'+k.KeywordName+'%'
That'll get you a table ot tuples of (KeywordID,ProductID).
It's very unlikely that a solution that ships all this data back to some C#
code via a dataset will give better performance - network latency and
bandwidth alone will dominate that solution as the tables get larger.
-cd
