Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MS SQL 2005 Express. Full Text search takes rediculously long

 
ravil baizhiyenov
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For some reason, FULL TEXT search on my database takes too long to complete the first time. I mean the first time I perform it, it takes about 30 seconds to search the table (which is really small - only some 13 records). However, it then works fine for some time...

I tried using Management Studio, and get same performance issues.

Here is the sql statement I use:

SELECT * FROM Report WHERE FREETEXT([description], 'report')


Please help!
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Presumably its indexing the table on demand rather than upfront. On a full SQL Server install there is a free text search service that does this in the background, but since Express is not intended as a production database I would guess it doesn't do this. Not sure if this is correct, but you can check if there is a free text service running and if there isn't this may be how it is behaving.
 
ravil baizhiyenov
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank for you reply!

Yes, the problem was related to the FT service... Apparently after a certain timeout SQL server has to reload the service. And that takes too long... More on this here: http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/699221f0-a850-4f1d-b1db-a067a172f866/

workaround:
sp_fulltext_service 'verify_signature', 0;
GO

Seems to be working fine for now...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic