Full text searching on a bulletin board system

I was told by a programmer that the full text searching on ms sql is only a
good solution on static data, meaning no new data is being added frequently,
as the indexes have to be rebuilt each time someone is added/removed.
I will be running a bulletin board system, so there will be many new posts
per day. I'll also need searching of the text by users.
What are your thoughts on this and what the programmer said?
Suppose there are hundreds of thousands of posts, and say, one new post is
added every 5 minutes.
How often would you recommend an update or reindexing of the index take
place? And each time it happens how much will it bog down the system?
|||your programmer friend is misinformed. You can schedule incremental
populations for SQL 7 which will process new changes if there is a timestamp
column on the table. If there is no timestamp column it will reindex the
entire table.
With SQL 2000 you should use change tracking. This will only index the
changes which occur in columns you are full text indexing. Incremental
populations still extract and examine each row. Change tracking will only
index the changes and is much faster. You can run Change Tracking in a batch
mode (ie scheduled) or have it run continuously, where it picks up changes
every few seconds (most likely within 10 s).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Shabam" <blislecp@.hotmail.com> wrote in message
news:9MqdnTASMpw_MvbcRVn-vw@.adelphia.com...
> Suppose there are hundreds of thousands of posts, and say, one new post is
> added every 5 minutes.
> How often would you recommend an update or reindexing of the index take
> place? And each time it happens how much will it bog down the system?
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23vYZ0KFsEHA.376@.TK2MSFTNGP14.phx.gbl...
> your programmer friend is misinformed. You can schedule incremental
> populations for SQL 7 which will process new changes if there is a
timestamp
> column on the table. If there is no timestamp column it will reindex the
> entire table.
> With SQL 2000 you should use change tracking. This will only index the
> changes which occur in columns you are full text indexing. Incremental
> populations still extract and examine each row. Change tracking will only
> index the changes and is much faster. You can run Change Tracking in a
batch
> mode (ie scheduled) or have it run continuously, where it picks up changes
> every few seconds (most likely within 10 s).
What kind of load would this generate on the system? I read somewhere that
index updates should be done during off-peak hours.
|||That depends on the frequency of updates, the size of the data you are
indexing, the number of CPUs, and your horsepower.
In general it doesn't consume significant cpu for long periods. You may
notice some CPU spiking with German or the Far Eastern word breakers.
"Shabam" <blislecp@.hotmail.com> wrote in message
news:Br-dneL7af1_m_HcRVn-jA@.adelphia.com...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23vYZ0KFsEHA.376@.TK2MSFTNGP14.phx.gbl...
> timestamp
> batch
> What kind of load would this generate on the system? I read somewhere that
> index updates should be done during off-peak hours.
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uXpTI3IsEHA.2340@.TK2MSFTNGP11.phx.gbl...
> That depends on the frequency of updates, the size of the data you are
> indexing, the number of CPUs, and your horsepower.
> In general it doesn't consume significant cpu for long periods. You may
> notice some CPU spiking with German or the Far Eastern word breakers.
Thanks! That was very helpful.
Are there any times when it wouldn't be good to use the full text searching
of MS SQL?
|||Shabam,
Q. Are there any times when it wouldn't be good to use the full text
searching of MS SQL?
A. This would depend upon the type, amount and frequency of non-FTS type SQL
queries that for your application consume &/or use significant portions of
your server's CPU, Memory and disk I/O (assuming that you have separated the
FT Catalog from your database files). What is your application's workload?
Is it heavy OLTP or OLAP? primarily a web site host with lots of searchers?
Depending upon these answers, you would want to schedule your Full &/or
Incremental Populations around the time periods of your most active
(non-FTS) SQL query processing. If you have a dual or multi-proc server, you
can separate the MSSearch cpu usage from your MSSQLServer cpu usage as well.
Let me know if you have any other questions!
Thanks,
John
"Shabam" <blislecp@.hotmail.com> wrote in message
news:zvCdnWrP0ZOIifPcRVn-sA@.adelphia.com...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uXpTI3IsEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Thanks! That was very helpful.
> Are there any times when it wouldn't be good to use the full text
searching
> of MS SQL?
>
sql