SQL or C# for searching one table based on another table's data?

[Ronald S. Cook]

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?

Thanks,
Ron

 

[RvGrah]

I'd think something like:

Select <columns or whatever you want> from Products Where ProductName
like '%Apple%' OR ProductDescription like '%Apple%' would return very
quickly from any of the major database engines. Using a sql stored
procedure would be even better.

Iterating in c# ought to be slower since this is a set-based question
by nature.

Bob Graham

 

[RvGrah]

Or are you looking for a cartesian product, all records multiplied by
all matches? That would tax either system I would think. I'm no expert
but I think this qualifies as a many-to-many result set, and with
thousands of records in both tables the answer could be huge,
especially if some Products table items have multiple matches in them!

Bob Graham

 

[Carl Daniel [VC++ MVP]]

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