November 22, 2002 at 7:13 am
Hi
Full Test Indexing
Populating the catalogue.
Is there any way to update the full text catalogue ONLY when the field in the row specified for full text indexing has been updated?
All the things I have seen (incremental, tracking changes) appear to work on a row basis, and any updates to that row means that it will be flagged for updating.
We have a table which has many columns often updated, but the full text column very rarely, and only want the full text update to worry about any rows where this column has been updated and not any of the others.
Any thoughts?
Cheers
November 22, 2002 at 7:33 am
This is a work around and I'm not sure the performance hit is worth it. I'm going to assume you're still updating the index on a set schedule, not on the fly. Modify accordingly.
You could have a trigger on the table for INSERT, UPDATE, and DELETE which checks to see if that particular column is updated. It then writes to a staging table. Your database job could check to see if something is in the staging table. If it is, it updates the full text index and then clears the staging table for the next time. If there isn't, it shuts down and doesn't run the update.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 22, 2002 at 10:22 am
Hi
Thanks for replying.
I like that idea, it only falls down if there is a single entry in the staging table, it will still do the full incrementation on all updated products. We will undoublty have a few inserts/updates to out full text field between schedules.
FYI: what we are currently looking to do is move some newly added (non full text) fields that are very regularly updated to another table on a one to one relationship. This will mean that very few rows will be hit with an update, and therefore reduce the full text update.
One of the other things we looked at is being able to run the Full text population SP through a trigger
(exec sp_fulltext_getdata 18, 1076198884, 0x0001F253, 1 (DB, Table and Row id's, don't know what the 1 is)) However this is an extended proc, and cannot run through script, ie canot just add to trigger, and turn off incremental update.
Another thought we has was to have a trigger that set back the datestamp, used to determin full text incremental update, to the original value if the update did not hit the full text field, however cannot update a datestamp field, Doh!
Cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply