FTS - Does Crawl Completed??? And wait untill next query can get the results!

Question

FTS - Does Crawl Completed??? And wait untill next query can get the results!

Abrar Ahmad_

SSCarpal Tunnel

Points: 4244
More actions
February 4, 2020 at 11:01 am

#3722867

Hi,
Actually with Full Text Search FTS we have a table on which we just insert thousand of rows; and then in very next statement try to fetch data with contains query.
The index is not built within this span of time (even Auto) so what would be the solution to this problem; or even if this is the problem.
Introducing a manual delay of seconds for next query; just done everything right. But is it the way only?
Secondly; Crawl Server settings can benefit anything?
Thank you in advance.
February 12, 2020 at 1:04 am

This was removed by the editor as SPAM

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

It doesn't really sound like it's taking too long - sounds like seconds. Are you doing a full or incremental crawl? And how long is it taking? You can find that in the full text log.

Sue

Abrar Ahmad_ SSCarpal Tunnel Points: 4244 More actions · Answer 2

We have AUTO crawl settings. And one of the DMV sys.fulltext_indexes (crawl_start_date, crawl_end_date) same listed about four+ seconds on production; where have only a some hundred thousand rows.

So after inserting into the base table, with AUTO configurations; does the very next query would attain the results from the data inserted above? does incremental crawl settings would work?

Secondly, the CRAWL in above stated DMV should be checked and waited until the flag "has_crawl_completed" is set to request the results from the index?

Lastly Server Level Settings of the CRAWL would help out anything?

Thank You Sue_H

Sue_H SSC Guru Points: 90860 More actions · Answer 3

So you have it set to change tracking automatic. How that works is that after the initial full population, it automatically updates the index for changed/added data. This is an incremental crawl. It won't complete the indexing of the new data the second you finish inserting thousands of rows. As it says in the documentation for Automatic population:

After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.

Refer to the section on automatic population in the following:

Populate Full-Text Indexes

Yes has crawl_completed in sys.fulltext_indexes tells you when it has completed and the index with new data is available.

4 seconds seems reasonable for FT to index the thousands of new rows. In terms of server settings, you can try to change the max full-text crawl range to the number of CPUs on the server. It may speed things up a bit.

Sue

Abrar Ahmad_ SSCarpal Tunnel Points: 4244 More actions · Answer 4

Sue_H wrote:

So you have it set to change tracking automatic. How that works is that after the initial full population, it automatically updates the index for changed/added data. This is an incremental crawl. It won't complete the indexing of the new data the second you finish inserting thousands of rows. As it says in the documentation for Automatic population:
After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.
Refer to the section on automatic population in the following:
Populate Full-Text Indexes
Yes has crawl_completed in sys.fulltext_indexes tells you when it has completed and the index with new data is available.
4 seconds seems reasonable for FT to index the thousands of new rows. In terms of server settings, you can try to change the max full-text crawl range to the number of CPUs on the server. It may speed things up a bit.
Sue

Yes, Thank you for your support!

Meanwhile, does following would work for specific index; and after the CRAWL is completed; the index would be actively returning data?

WHILE EXISTS (SELECT 1 
      FROM sys.fulltext_indexes 
  where object_id = object_id('table_Temp') -- sample table
    AND HAS_CRAWL_COMPLETED = 0
  ) 
BEGIN 
 
--PRINT 'WAIT : ' + CAST( CAST(GETDATE () AS TIME) AS VARCHAR);
WAITFOR DELAY '00:00:00.010';
END

Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 5

That looks like it should work - it would be an easy one to test in a non-production environment. Make sure to qualify the table name with schema.TableName if you are using schemas. Otherwise I believe it just checks dbo schema by default.

Sue

Abrar Ahmad_ SSCarpal Tunnel Points: 4244 More actions · Answer 6

Sue_H wrote:

That looks like it should work - it would be an easy one to test in a non-production environment. Make sure to qualify the table name with schema.TableName if you are using schemas. Otherwise I believe it just checks dbo schema by default.
Sue

Thanks!