I implemented a full text search recently in our database. It worked great at the beginning, only 1 second to get the results while it took 10 seconds with the old LIKE search.
But I realized the new full-text search was getting slower each day until it takes almost the same time as the old search.
I noticed that when I go in the catalog properties and click "Catalog action: Optimize catalog", the search become fast again.
I could probably just script the optimize catalog transact-sql and put it in a job, but I would like to know why the search is going slower if I don't run this optimization daily, is there something else I should do ?
For information, my catalog contains data from 3 different tables, full-text indexes are change tracking automatic, and I scheduled a full population daily at night.
"Yes, during a slow time." means "during a general slow time for the server" - i.e. during your off-peak hours and not "when the searching becomes slow".
Fast full-text search get slower with time
Posted - 09/30/2011 : 07:39:13
