 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / September 2007First FTS-query to slow
We are using FTS on a table which has for the moment 4500 rows. When we restart the FTS-service, or no one uses the search for a while, then the first FTS-query takes up to 60 seconds. (also when there is only 1 record returned) While lookalike queries after the first take about 60 miliseconds. We've alreade rebuild the catalog, but this does not help. The problem is on the FTS. The query I use to test the performance uses only the FTS-engine, see statement below. select [KEY] from containstable(EURO.TB_ProductGroup_FTS_V2,*,'test') Does anyone know how to fix that ? This is because the full-text engine relies on the file system cache to keep the catalogs in cache. If they are not used they are flushed to disk. You need a sql server job that runs ever 5 minutes or so to search on something relatively large - like the word the.  SignatureRelevantNoise.com - dedicated to mining blogs for business intelligence. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > We are using FTS on a table which has for the moment 4500 rows. > When we restart the FTS-service, or no one uses the search for a while, [quoted text clipped - 11 lines] > > Does anyone know how to fix that ? I also had this problem currently and the weird thing is that, the first query delay will be gone if I cleared the DNS entry in the tcp/ip setting. When I put it back then the problem will be back where the first query will be badly delayed (around 60 sec more or less). Subsequent FT query will be normal and fast. Leave the machine for around 15 mins. then the first query will be slow again. This happen with either small number of records (around 30 rows) and also lots (around 300 rows). Anybody got solution or actionable explanation for this? Thanks. Faisol > This is because the full-text engine relies on the file system cache to keep > the catalogs in cache. If they are not used they are flushed to disk. You [quoted text clipped - 16 lines] > > > > Does anyone know how to fix that ? Check out http://support.microsoft.com/kb/915850 as I suspect this may the problem you are all encountering. The only thing I am unsure about is how insecure the suggested work around is so as a temporary solution I have created a sql job to execute a basic full-text query every 4 minutes to that the word breaker does not have to try to re- verify every 5 minutes. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.