Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have a table with a full-text index that is essentially a denormalized view of several tables that is automatically updated when any of the base tables are updated via triggers. This table is primarily used for a nightly operation, but I would like to use it for more real-time things if I could only get the population figured out.
 
When setting up the full-text index, I set 'track changes' to 'Automatic' as I didn't want have to fully populate the index every night. However, it doesn't seem to incorporate newly added records for some time, if at all. I can only get new records to return in FREETEXTTABLE query by re-building the index.
 
I tried setting up a population schedule twice a day, but that doesn't seem to do anything. I am testing it by looking at newly inserted records in the base tables and searching for them in a FREETEXTTABLE query, expecting the correct ID to show up in the search results.
 
When researching online, I kept seeing 'populate in background' as a potential solution, but I never saw anything about how to actually enable this.
 
Any ideas?
Posted 16-Aug-11 7:42am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Most likely the background population you saw is another term for tracking changes.
 
If you add large amount of data in your nightly operations, I would suggest that you drop the index before nightly operations, do the operations and then create the index again (so not using rebuild). When creating the index again, put the change tracking to auto so the index should keep track on the day time operations.
  Permalink  
Comments
Zach Swanson at 17-Aug-11 10:27am
   
Thanks for your help Mika. The data operations are performed during the day using a web interface. When making changes, updates to the base tables are automatically copied to the fulltext table (denormalized view) via triggers updating only the affected rows and fields.
 
How is dropping and re-creating the index different from a full re-population? Ideally, I am wanting the index to be up-to-date within 4 hours of changes made.
Mika Wendelius at 17-Aug-11 12:26pm
   
From your post I understood that you 'massively' updating the data at night time. If that's the case then dropping the index and creating it after the data operations would have 2 effects:
- the speed of data load would be most likely higher (no index maintenance)
- index would be up-to-date after creating it with 'fresh' data
 
I've seen some situations where change tracking is ignoring some changes which eventually causes missing entries. This has happened if the table containing the index is modified very fast. That's another reason why I suggested not having the index when data is loaded. But as said these apply only if you have a 'load period' for the table
 
If you allow the index to be few hours late, one possibility is that you don't use background population but create a job under SQL Server agent. In this job you either populate the index manually (ALTER FULLTEXT INDEX...) or rebuild it. You can schedule this job to execute for example every 4 hours.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 BillWoodruff 360
1 Mathew Soji 309
2 DamithSL 225
3 Afzaal Ahmad Zeeshan 202
4 Maciej Los 190
0 OriginalGriff 6,249
1 Sergey Alexandrovich Kryukov 5,853
2 DamithSL 5,183
3 Manas Bhardwaj 4,673
4 Maciej Los 3,865


Advertise | Privacy | Mobile
Web04 | 2.8.1411019.1 | Last Updated 17 Aug 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100