Looking for "search" advice

Question

Looking for "search" advice

jc-292104

Valued Member

Points: 74
More actions
January 9, 2006 at 10:58 am

#92549

Hi there everyone. Hopefully someone can help me solve my current dilema!
I'm trying to come up with a more elegant search for our website so our customers get more - and hopefully better - results. We have somewhere in the neighborhood of 45k active SKUs with lots of information for each item. Currently we're using Full-Text Indexing which in our setup is fast (good thing) but not very good at finding matches in our particular set of data (bad thing.)
The root of the problem is that a user needs to be able to search for text that may exist anywhere inside a string, not just at the start of words. For example they know that "12345" is part of the product number, but if it's stored as "ABC-12345-XYZ" then a CONTAINS will never find the match. The quick and easy answer would be just using "Where ItemSKU Like '%12345%'" which will find the desired data, but is not nearly as fast as full-text once you throw that wildcard at the beginning of the string.
Here's our environment:
Windows 2000 Server, all service packs and updates
SQL 2000, all service packs and updates
How are other people doing this sort of search? We can't be the only ones trying to do this Is there another option that I'm unaware of or am I looking at some 3rd party solution?
Thanks for any advice you can offer!
-jc

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

G'day,

Your best bet may be avoiding the problem rather than solving it. Simply place two buttons on the search form - "Quick Search" which does the full text currently implemented, and "Deep Search" which uses the LIKE implementation. Use a hint text to inform the user that Quick Search is fast but may not find everything. Tell them that Deep Search will find everything, but may be a little slower. By empowering your users, they will feel in command of the situation and will be more likely to overlook a slightly slower search.

Wayne

jc-292104 Valued Member Points: 74 More actions · Answer 2

Thanks for the reply.

I was afraid that might be the answer. This is very frustrating because I know many people must want the level of search functionality I'm talking about and yet there doesn't seem to be a straight forward solution.

We're definitely going to offer an advanced search and can certainly give the option for the "deep search" using Like behind the scenes. I was just hoping to hear there was something specific I could tweak to get what I'm after

-jc

Gary Nease SSC-Addicted Points: 424 More actions · Answer 3

Your problem description has only one example, so I'm not sure this will help, but...

You could maintain a copy of the TEXT column, which has been stripped of all the hyphens (and other noise that breaks the granularity of FtSearch). We have a similar problem searching 17Gb of XML blobs in SQL2000. We use XSLT to produce a copy of each row that is saved into to a separate 7Gb FT-searchable table (free of tags) whenever we persist the XML to the primary table. Our FtSearch queries return the a key that can be related back to either table. We have split the two tables into different databases across linked servers and get very good performance well above the volumes you are talking about.

Regards,

Gary

jc-292104 Valued Member Points: 74 More actions · Answer 4

Thanks for the info, Gary. It's actually similar to what we're doing, but sounds like yours is on a larger scale I created a separate table that has 2 columns: a key to tie back to our main product table and then another FT-enabled column that is all information relevant to searching, i.e. SKU, description, manufacturer, etc.

We still come back to the same problem though - how best to search for a string in the middle of words.

I'm considering adding another FT-enabled column that would do a Reverse() of all the text to allow us to at least match on beginnings of words and ends of words. It still wouldn't find any matches for the middle of a word, but it gets us a big step closer covers the majority of our data I believe.

The question then becomes what performance impact would having each search hit two large FT-enabled columns instead of just the one? SQL is currently on a single CPU xeon 3.2ghz with 4gb of RAM. I guess the short answer is to try it and see for myself