Hi,

We are using Full Text Search on SQL 2000. The search performance is
fine.

Our problem is that when a record is added to the table, the average
time it takes before we can find it with a CONTAINS is 20 seconds.
This is with only one user connected.

The tables has only 200000 rows. The field is a VARCHAR of less than
200 characters.

I don't know the exact spec of the server but its a 2CPU, 4GB RAM with
fast disks. If the exact spec is important I can find out.

Thanks for your help.

Sylvain,
Are you using "Change Tracking" with "Update Index in Background" and
concerned that once a record (row) is added that it takes 20 seconds before
a SELECT * from FT-enable_table where CONTAINS(*,'search_word') will return
the record (row) where search_word exists?

If not, is your concern more about the total time it takes to return a
results using the above FTS query? If so, you may want to use CONTAINSTABLE
and the Top_N_Rank parameter, for example:

use Northwind
go
SELECT e.EmployeeID, e.LastName, ct.[RANK]
FROM Employees AS e
JOIN CONTAINSTABLE(Employees, Notes, 'French',10) AS ct ON e.EmployeeID
= ct.[KEY]
ORDER BY ct.[RANK] DESC

Note, the value 10 in the above query as this represents the Top N (10) by
Rank value. Using this parameter on containstable or freetexttable can
improve the FTS query performance, but you should experiment with the value
as if you set it too low, you can miss some results, see KB article 240833
(Q240833) "FIX: Full-Text Search Performance Improved via Support for TOP"
at
http://support.microsoft.com//default.aspx?scid=kb;EN-US;240833 for more
info on this.

You should also review SQL Server 2000 BOL title "Full-text Search
Recommendations" as well.
Regards,
John




[quoted text, click to view]