Unanswered: Optimize Full Text Search
I have a table that currently has a FullText Index on one of the columns. We are currently adding about 500,000 records a day, so it is growing fairly quickly. we're curretly sitting at around 75 million records, and the performance of the Full Text index is horrible. All of the records are bulk loaded once per day, and the Catalog automatically tracks and applies changes, so I know the Index is up to date. I've gone in and looked a the properties several times, and there are never any pending changes, so I don't think my issue is with loading the data.
Everything I've read so far tells me that the slow down is directly proportional to the amount of results returned from the Full Text Search and that I should limit it based on the items Rank. Unfortunately, in addition to the FTS I have other criteria that need to be met. (namely a date range, transaction type, sender, receiver, etc...) Each query is different, but the Text Field is just part of the equation, so I can't filter out any results using the RANK option in the query.
I am currently on a SQL 2005 Standard instance, so Partitioned Tables isn't an option yet. (Our other production environement is 2005 Enterprise, so I'm working on getting data copied over there to test with.) I'm looking at creating a partition on the data so that there are 52 partitions, one for each week of the year. That will split the underlying table up pretty well, but will that affect the Full Text Search catalog in any way?
I'm afraid that I'm going to do all this work, and in the long run the full text search will still search the entire table, not just the necessary partition. Has anyone else partitioned a table with a Full Text search field on it? And what kind of performance impact has it had?
SELECT * FROM Users WHERE Clue>0
0 rows returned