Post reply

Full Text Indexing has delay within Contains statement

  • August 30, 2006 at 2:59 pm

    #170009

    Problem exist on an SQL Server 2005

    I have a table that consists of about 100 or more records. I created a full text index on it so that I could do a "Select * from Products where Contains(*,'searchKey')". The first time I issue the select statement against the index the result takes about 45 seconds and returns nothing. If I resubmit the select statement I receive my results within seconds. I will continue to receive results within seconds with each submitted statement. Now if I stop submitting select statements with the Contains for 10 minutes or more and I then start again, the system goes back to taking 45 seconds and returns nothing. I can get the system to do this over and over again with consistency.

    Thanks,

    Kneale

  • September 4, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • September 4, 2006 at 8:39 am

    #658359

    Did you get chance to run when you are waiting for 45 seconds?

    select * from sys.sysprocesses

    I feel that your query is blocked by something. Please let us know waittype, waittime and lastwaittype column value for the SPID which is running your select statement is running.

     

  • September 4, 2006 at 8:52 am

    #658362

    interesting....

    Could you please run below query in specific database

    sp_fulltext_service

    'verify_signature', 0

    After running above, restart Full Text service for that instance and run the query again

    Let us know the results...

    HTH

  • September 6, 2006 at 12:08 pm

    #658836

    First of all thanks for your help.

    I ran the procedure as instructed and so far the test queries are running without any delay. I have a few more tests to do but I feel confident enough to move this back into production.

    sp_fulltext_service 'verify_signature', 0

    Thanks again,

    Kneale

  • September 6, 2006 at 12:54 pm

    #658860

    Glad to know it helped.

    One quick question. Does this box has access to internet?

  • September 6, 2006 at 1:48 pm

    #658868

    It is currently being blocked but if need be I can open it up in order to access the internet.

    Kneale

  • September 6, 2006 at 9:35 pm

    #658948

    Can you please open it for the internet access. and revert back the query to

    sp_fulltext_service 'verify_signature', 1

     

    Let me know the results...

     

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply