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.