Alternatives to MS SQL 2005 FullText Catalog

Question

I can't seem to get acceptable performance from FullText Catalogs. We have situations where we must run 100k+ queries as quickly as possible. Some of the queries use FREETEXT some don't. Here's an example of a query

IF EXISTS(select 1 from user_data d where d.userid=@userid and FREETEXT(*, @activities) SET @match=1

This can take between 3-15 seconds. I need it to be much faster < 1s if possible.

I like the "flexibility" of the fulltext query in that it can search across multiple columns and the syntax is pretty intuitive. I'd rather not use a Like statement because we want to be able to match words like "Writer" and "Writing".

I've tried some of the suggestions listed here http://msdn.microsoft.com/en-us/library/ms142560(SQL.90).aspx

We've got as much memory and cpu as we can afford, unfortunately we can't put the catalogs on their own disk controllers.

I'm stumped and ready to explore other alternatives to FullText Queries. Is there anything else out there that gives that kind of "Writer"/"Writing" similar matches? Perhaps even something that uses the CLR?

Answer 1

Due to the nature of FREETEXT, the performance is less than when you'd use CONTAINS, simply because it has to take into account less precise alternatives for the keywords given. CONTAINS can find Writing when you specify Write btw, I'm not sure if you've checked whether CONTAINS will do the trick or not.

Also be sure to avoid IF statements in SQL, as they often lead to a complete recompilation of the execution plan for every query execution, which will likely contribute to the poor performance you're seeing. I'm not sure how the IF statement is used, as it's likely inside a bigger piece of SQL. Try to merge the EXISTS query with that bigger piece of sql, as you can set the @match parameter from within the SELECT statement inside the EXISTS, or get rid of the variable altogether and use the EXISTS clause as a predicate in the bigger query.

SQL is a set-oriented language and interpreted. Therefore, it's often faster to get rid of imperative programming constructs and use the native set-operators of sql instead.

Answer 2

Check out these alternatives, although I doubt they'll improve performance without isolating them onto separate hardware:

http://stackoverflow.com/questions/120965/which-search-technology-to-use-with-aspnet

http://stackoverflow.com/questions/37059/lucenenet-and-sql-server