SQL 2005 full-text catalog is random

Question

I built the full-text catalog in a SQL 2005 block, which, after completing the reassembly process, is very slow. I implemented a hack (for example, try ... catch {do again}) so that my users do not receive a timeout error; it makes me feel bad inside. All subsequent requests are lightning fast.

Has anyone experienced this problem and was / have a solution? Thanks!

PS Yes, I have done this many times on Google. Even with the left hand.

Answer 1

It may also be caused by this feature of the Sql server we encountered.

You may experience a 45-second delay when starting a full-text query in an instance of SQL Server 2005 that runs on a server without Internet access

http://support.microsoft.com/default.aspx/kb/915850

Answer 2

this may not be the direct answer to your question, but full-text search in mssql was considered in the stackoverflow podcast series, and the output was not the best :)

therefore, if you can change it to a third-party library, you can try using jeff and co., the Apache Lucene library. The Java version is available at http://lucene.apache.org/java/docs/ and the .net port at http://incubator.apache.org/lucene.net/

Answer 3

I had it too. The first hit is very slow and rest fast. I tried all kinds and could not solve it.

I would like to know the answer to this question.

Answer 4

You can prevent the index from completely rebuilding by "setting start background updateindex" and "start tracking changes" (must be underlined between each word) in each table indexed by the full text.

This allows the sql server to update the index only with changes when necessary. This may help your problem as the index is not rebuildable.

Answer 5

Second sentence of Lucene.Net. Earlier I tried to create a "search engine" using full-text search and SQL. This was always problematic when search criteria became more complex, and often queries were timed out. On my new site I built a search engine with the Lucene.Net project, and it works very well and is much faster than SQL FTS.