Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
loki13
Starting Member
5 Posts |
 Posted - 2011-09-30 : 07:39:13
|
| Hi,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.thanks for your help ! |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-09-30 : 08:56:37
|
| because everytime you add a new value, you have the possbility of fragmenting the index. |
 |
|
|
loki13
Starting Member
5 Posts |
Posted - 2011-09-30 : 12:57:40
|
So it is THE solution to periodically call this ??ALTER FULLTEXT CATALOG [MyCatalog] REORGANIZE |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-10-03 : 03:48:32
|
| Yes, during a slow time. |
|
|
|
loki13
Starting Member
5 Posts |
Posted - 2011-10-03 : 08:00:18
|
quote:
Originally posted by RickD
Yes, during a slow time.
Isn't it a good idea to schedule an execution in the sql server agent to do it before it become slow ?I mean, is it "not recommended" to do it too often or something ? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-10-03 : 10:42:58
|
| "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". |
|
|
|
loki13
Starting Member
5 Posts |
Posted - 2011-10-04 : 07:09:53
|
| ok thanks for your answers guys |
|
|
|
|
|
|
|
|
Fast full-text search get slower with time