Именно такую задачу на вечер поставил перед нашей командой заказчик. Поиск в базе объявлений, содержащих "нехорошие" слова, и выставление всем подобным записям флага Is18YearsOnly. Конечно только этим задача не ограничивалась, её решение включало в себя доработку кода на соответствие требованиям и обновление базы. С кодом мы успешно справились в рабочее время, а вторую часть я взял на дом. По дороге домой, и уже дома, сидя за компьютером, я думал над тем как решить эту задачу наиболее эффективно. И вот что придумал…
Все идеи с LIKE-ом, я отбросил ещё по дороге домой (объёмы данных требовали чего-то более эффективного), и взял за основу Full-Text Search. Именно при помощи него я и отбирал "нехорошие" объявления. Сразу оговорюсь, что в таблице с объявлениями порядка 10 млн. записей, список "нехороших" слов содержал около 100 слов. Сама таблица объявлений имеет составной ключ, и поэтому не располагает к полнотекстовому индексированию. Поэтому, как основу для полнотекстового индекса, я создаю индексированное представление, содержащее интересующие меня данные (заголовок и тело объявления):
CREATE VIEW iv_ForSearch WITH SCHEMABINDING
AS
SELECT Id, Title, Description
FROM dbo.Ads
WHERE Is18YearsOnly = 0
GO
CREATE UNIQUE CLUSTERED INDEX IDX_FORSEARCH_ID
ON iv_ForSearch(Id)
GO
Всё это заняло около 10 мин. После этого, создаю по этому представлению полнотектовый индекс, в полнотекстовом каталоге (ftc_Ads), включающий в себя интересующие меня поля:
CREATE FULLTEXT INDEX ON iv_ForSearch (Title, Description)
KEY INDEX IDX_FORSEARCH_ID
ON ftc_Ads
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)
GO
Теперь ждём пока полнотекстовый индекс полностью заполнится. За процессом заполнения можно следить при помощи свойства полнотекстового каталога. 1 -означает, что идёт полное заполнение, 0 - что индексы каталога полностью заполнены:
SELECT fulltextcatalogproperty('ftc_Ads','PopulateStatus')
После чего начинается самое интересное! Собственно то, зачем я городил огород. Запрос по полнотекстовому индексу, который ищет точные вхождения нужных мне слов. Полученные в результате запроса айдишники объявлений я кладу во временную таблицу для дальнейшего использования:
SELECT Id INTO #BadAds
FROM iv_ForSearch
WHERE CONTAINS((Title, Description),
'"xxx1" OR "xxx2" .... OR "xxxN"')
Этот запрос бежал около 15 мин. И если бы вместо него был бы LIKE, то я бы наверное спать сегодня не ложился :) А так всё супер! Впереди последний шаг – обновление объявлений. Непосредственно перед обновлением таблицы объявлений я удаляю индексированние представление, дабы ускорить этот процесс:
Отлично! Все необходимые данные теперь есть. Всего таких записей 88 тыс. с копейками, поэтому я запускаю цикл на 884 повторения. Обновление я делаю небольшимим порциями, чтобы снизить время блокировок, и, как следствие, ускорить время выполнения запроса и снизить влияние на систему. Для того, чтобы гарантировать обновление и удаление одних и тех-же записей, я создаю кластерный индекс по полю Id во временной таблице:
CREATE UNIQUE CLUSTERED INDEX IDX_BADADS_ID
ON #BadAds(Id)
GO
UPDATE Ads SET Is18YearsOnly = 1
WHERE Id IN (SELECT TOP(100) Id FROM #BadAds)
DELETE TOP(100) FROM #BadAds
GO 884
Это длилось порядка 25 мин. Теперь удаляем временную таблицу и можно готовиться ко сну:
Итог: ещё один скрипт в копилку: Поиск + Обновление = Full-Text Search + Обновление порциями.
Ссылки по теме: