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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-10 : 09:28:43
|
Jonathan Winer writes "SQL 2000 running on Windows 2000 Advanced Server:
I have setup an full-text index on a table (the index consists of 2 text fields - the table contains roughly 100,000 rows). The full-text catalog takes roughly 20 minutes to populate when doing an incremental population. We have it scheduled to run every 20 minutes so that the index is up to date with any inserts or updates to the table. In addition, when the incremental population is running, it is a resource hog and the cpu usage for this process spikes to 80-100%. I changed the settings to 'start_change_tracking' and 'start_background_updateindex' and deleted the schedule for the incremental population. Now the cpu usage is much less (around 20 - 30%, this is good), but in both instances, the full-text queries that are run against the table are taking roughly 15 - 60 seconds to return. While at the same time, if I remove the contains() clauses from my query and use the 'like' clause instead, the query returns almost immediately. Because the Contains() clause is so much more robust, I would like to continue using it, but not at the expense of queries taking so long to return.
How can I tune the indexing features so that an incremental population is not so resource intensive?
I'm also not sure about using the 'start_change_tracking' and 'start_background_updateindex' options. They do not detect updates to text fields. So changes to those fields will not appear in the index.
How can I rewrite the full-text queries to make them more efficient (return more quickly)? (The queries are simple select queries, only returning the necessary fields)
i.e.(select jobtype, postdate, appID from table1 where (contains(jobdesc, 'windows') or contains(jobdesc, 'vb')) and salary = 1" |
|
|
|
|
|
|
|
full-text indexing