February 4, 2020 at 11:01 am
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 5, 2020 at 12:25 am
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
February 6, 2020 at 9:44 am
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
February 6, 2020 at 8:51 pm
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:
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
February 7, 2020 at 11:37 am
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:
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
February 7, 2020 at 11:40 pm
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
February 10, 2020 at 10:54 am
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!
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