sql full text index problem with "score" not updating in a timely fashion

Question

Have full text index on a table for the purpose of searching and producing a list of results based on relevance, I assign relevance based on the highest "score" from the full text index.

The table contains 3 columns, a unique ID, a text field in the type (varchar), and the third is a timestamp of the last edit. The full text index only looks at the TEXT(varchar) column.

I have noticed that there seems to be a delay between the time that the data in my table is update and that new score being available through the same query re-executed.

The process would be as follows:- I update the text in a record- The old score is replaced with a lower value- (even though the data would be more relevant) over the course of minutes sometimes hours i re-execute the same query and the score slowy increases each time until it reaches a score that is higher than its previous score (which is what i wanted).

I cannot find out any information about the internal workings of the full text indexing service relating to this "delay".

Any help, tips, suggestions would be most welcome at this point, thanks.

Answer 1

Robert Cain (www.arcanecode.com) presented a great session at SQL Saturday #25 in Gainesville, GA this past Saturday. His presentation link: Full Text Searching

[Robert, I if you see this, I hope you don't mind the re-post, but you've got the clearest explanation of how full text searching works that I've seen.]

Anyway, my understanding from listening to Robert speak this weekend is that the Full Text Index will only update when the server has some resources to spare. In short, it's a lower-priority process than you might be expecting if your server is getting hit hard.