T-SQL MS SQL FullText Catalog Search Issue

lanexllc
lanexllc used Ask the Experts™
on
Hello-

We are presently working on a search routine searching a bunch of publication article content.   At present, the routine is written using LIKE clauses in the query, but response times are very slow, on the upwards end of 30 seconds (or, occasionally timing out.)

The content itself is publication information (stories, etc.), and contains approximately 2000 articles stored in ntext fields in the database.

A fulltext catalog has been built on the ntext field.

The issue is this:

If we explore using the fulltext catalog as opposed to LIKE clauses, the results are typically either an error message, or, the search strings are treated as individual words.

SO, if I search for "War and Peace" I will get search results matching the word "War" and "Peace", the word "and" is treated as a stop word and is ignored.

In the case of a value such as a Bible verse, such as "Matthew 6", the SQL query will throw an error indicating that there is an empty value in the fulltext query (presumably the number 6 is dropped, which results in this condition.)

My question is this:

Has anyone found a reliable search method for searching database content for the types of information described above, and, what are some examples of these queries?   When we search for values such as "war" we also get partial matches, so, an article could be returned if it had partial matches such as "warranty" or "warren" as well.

We have tried both the CONTAINS clause as well as FREETEXT at this point.   We are running on MS SQL 2005.

Incidentally, LIKE clauses do seem to return the desired results, the issue is they are too slow.

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
FULL TEXT search is way different than LIKE just read below from:
 http://msdn.microsoft.com/en-us/library/ms142571.aspx

However....LIKE could be still fast IF:
A. you have an index on the searched column AND do NOT use fully qualifiers like %searchtxt%' BUT 'searchtxt%'  or "%searchtx'.

B. you have another column(s) that identify your record set before you do the actual search - somthing like:


Select * from Clients where MerchantID = 123456 and ClientName LIKE "%searchtxt%'
In my case I have a Clustered index on MerchantId and the searches are even way much faster than FULL TEXT

**********************
For each supported language, SQL Server provides language-specific linguistic components, including a word breaker and stemmer and an empty thesaurus file. For each full-text language, SQL Server also provides a file in which you can optionally define language-specific synonyms to extend the scope of search queries (a thesaurus file). In addition, beginning in SQL Server 2008, a system stoplist is provided. To support specific language or business scenarios, you can alter the system stoplist by adding and removing stopwords (also known as noise words), and you can create additional stoplists as needed.

For writing full-text queries, SQL Server provides a set of full-text predicates (CONTAINS and FREETEXT) and rowset-valued functions (CONTAINSTABLE and FREETEXTTABLE). Using these, applications and users can perform a variety of types of full-text searches, such as searching on a single word or phrase (and optionally ranking the result set), searching on a word or phrase close to another word or phrase, or searching on synonymous forms of a specific word.

Author

Commented:
Have you seen a search error out (with a SQL error) searching a FTCatalog with a phrase such as "Matthew 6".   In indicates that there is an empty search parameter- my feeling is it is dropping the number 6 as it is not text, would this be correct?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial