Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-21 : 09:39:52
|
| Mariam writes "Hello,I have this intranet (implemented in ASP) which uses an SQL2000 (win 2000 server) database to search for certain records. I have enabled full text search on SQL, and I use the "Like" operator to search thru text. That is sooo slow. Even doing a simple:Select story from news where story like '%sports%'takes 18 secs in query analyzer. My "story" field is of type ntext, and I tested "contains" and found it to take 1 sec in query analyzer.Now using "Contains" is faced by 2 (and a half) problems:1- contains assumes the word inserted to be a prefix, how can I set it to be any of the word? e.g: contains(Story, 'end*') returns all stories having "end", "ending", "ended" but not stories having "weekend"2- how do I implement "Not contains"? Users also have an option to insert words to be excluded from the text, so a user might have: NOT CONTAINS(Story, '"Sport*" OR "Tennis*" OR "Basketball*"'). Theoretically, this is correct, but I still get stories containing some of those terms.3- Contains automatically ignores some search strings, e.g. Contains(Story, 'e*') returns an error ("a clause of the query contained only ignored words"). I have 2 languages in my databases, and users need to insert such a search string to make sure they get only english stories or only arabic stories. I can override that search string by adding a checkbox for language. But contains also ignores all search strings in arabic (even if I insert a fully qualified existing word). Any ideas if "contains" applies for unicode text or not? remember my field is of type ntext. It would not be logical not to be able to use the full text search facilities for non-english languages.One final note, I test all my queries thru the query analyzer. This is not an ASP problem. I'm still stuck in the SQL part.Thank u in advance.Mariam" |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-02-21 : 18:36:09
|
| Mariam, here's the best I know or can find...1) Can't do suffixes. Think of the Full-Text index as an alphabetical list of all the words, and you'll see how hard it would be to locate weekend by searching for end. The LIKE operator is not an index and behaves differently.2) I think the problem is your use of OR instead of AND. I believe you want something like CONTAINS(Story, 'NOT"Sport*" AND NOT "Tennis" AND NOT "Basketball"')3) The list of search strings that get ignored are included in a file called a Noise Table. You might want to search BOL or MSDN for more info on which file is used for which language, but I belive that all letters individually are excluded so searching for "e*" would be rejected.Hope that helps...------------------------GENERAL-ly speaking... |
 |
|
|
nizmaylo
Constraint Violating Yak Guru
258 Posts |
Posted - 2002-02-21 : 19:26:30
|
| You can edit files containing noise words (it's a file, not a table) to include individual letters and other noise words. The last is not advisable, however, since it will slow down full text search.The best option is to create a COM object, which will check a string submitted by a client and re-write it into a string which complies with the rules of CONTAINS OR FULLTEXT.Example.Client typed "e baseball tennis" - to be included "cross-country" - to be excludedYou convert into '"baseball" AND "tennis" AND NOT "cross-country"'Actually, an idea of having a client indicate what's included what's excluded is not all that great.You should let them type Boolean phrases and then check how their phrases comply with the rules.If you don't know where to start with a COM object, search MSDN for "full-text".Also, if you have Microsoft e-commerce server, there should be an object for full-text searches (can't give you any advise on it, since I've never used it).helena |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-02-21 : 19:52:55
|
quote:
You can edit files containing noise words (it's a file, not a table)
Yep, that was a typo on my part...  Good catch Helena!------------------------GENERAL-ly speaking... |
|
|
|
|
|
|
|
|
Full Text Search