I have a table with 4 Million documents that i access via FTI. I also have an int Id field as a clustered index.
When i search for words in the documents that are rare, the query is very fast, 1 second.
When i search for words that are very common, the query is much slower, 13 seconds.
I can use a date field to reduce the volume of data, but it still seems some what slow.
eg.
No date selection used - 673770 recs found - 13 seconds
Within 30 days - 18563 recs found - 12 seconds
Within 10 days - 19 recs found - 9 seconds
I have implemented all the suggestions from the Execution Plan, but would still like better performance.
I have 32 GB RAM in my server, dual Quad core, and have enabled 'AWE' to allocate memory.
Is data partitioning my best option for improving performance ?