I've built a full-text catalog on a SQL 2005 box that, after it's re-build process completes, runs extremely slow. I've implemented a hack (i.e. try...catch{do again}) so that my users don't get a timeout error; this makes me feel bad inside. All subsequent queries are lightning fast.
Has anyone experienced this issue and was/is there a solution? Thanks!
P.S. Yes, I've Google'd it many times. Even with my left hand.
i've had this as well. first hit very slow and rest are fast. tried all sorts and couldnt resolve it.
would love to know the answer to this one.
You could prevent having to fully rebuild the index by "setting start background updateindex" and "start change tracking" (there should be an underscore between each word) on each table that is full text indexed.
This allows sql server to update the index only with changes when required. It may help your issue since the index is not being rebuilt.
this might not be a direct answer to your question, but the full-text-search on mssql was covered on stackoverflow podcast series, and the conclusion was it's not the best thing :)
so, if you are able to change it to a 3rd party library, you may try what's used by jeff & co., the Apache Lucene library. Java version available at http://lucene.apache.org/java/docs/ , and .net port at http://incubator.apache.org/lucene.net/
I second the Lucene.Net suggestion. I have previously tried to build a 'search engine' of sorts using Full Text Search and SQL. It was always problematic when the search criteria gets complicated and often queries would time out. On my new site I built the search engine with Lucene.Net project and it works really well and is much faster than SQL FTS.
It could also be caused by this Sql Server 'feature' which we've experienced.
You may experience a 45-second delay when you run a full-text query in an instance of SQL Server 2005 that is running on a server without Internet access