SQL Server Performance Forum – Threads Archive
Full text query performance
Hi, I have a table with about half million of records (really small) which is full text enabled. Every day I have another application which imports some data and adds about 300 new records into this table. After such an import, if I run a query with a CONTAINS statement and filter only records added during the last import (I have a CreateDate field and I use it to filter records imported same day), the query is veeeery very slow (20 seconds or more). But if I run the same query and filter older records (which also include the latest records) the query returns data in 1 second. E.g.
1) the following statement takes ages to complete:
WHERE CreateDate >= ‘today’ AND CONTAINS(field, ‘something’) 2) and the following query, which also returns correctly the latest imported rows, takes only 1 second:
WHERE CreateDate >= ‘yesterday or older’ AND CONTAINS(field, ‘something’) The table has a clustered index with a fillfactor of 90% and another non-clustered index on CreateDate field with the same fillfactor. Could someone give some advices on what could be wrong? fyi… I’ve run a DBCC SHOWCONTING over my table and for the clustered index there is a good scan density (more than 99%) and an average page density of 84%. Thank you, Radu.
Not that I understand very much of Full-Text searches, but are you populating your Full-Text index after the import? —
Microsoft SQL Server MVP
Yes. The FTS in SQL 2005 has been improved and allows tracking changes automatically. I tried both options: automatic and it didn’t work (my queries were slow), and after that I changed the tracking to manual and ran the
EXEC sp_fulltext_catalog @fullTextCatalogName, ‘start_incremental’ â€¦ which does the same thing. I also tried to rebuild the catalog, but with no success. Overall I manage to fix the problem, but here are the steps I must perform every day:
– detach the database and remove the full text catalog;
– delete the transaction log file;
– re-attach the database;
– re-build the indexes (especially the clustered index (with DBCC DBREINDEX);
– re-create the full text catalog and populate it. But it’s a real pain as the database is live and I must move very quickly.