Поиск и обновление

        Именно такую задачу на вечер поставил перед нашей командой заказчик. Поиск в базе объявлений, содержащих "нехорошие" слова, и выставление всем подобным записям флага 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, то я бы наверное спать сегодня не ложился :) А так всё супер! Впереди последний шаг – обновление объявлений. Непосредственно перед обновлением таблицы объявлений я удаляю индексированние представление, дабы ускорить этот процесс:

 

DROP VIEW iv_ForSearch
GO

Отлично! Все необходимые данные теперь есть. Всего таких записей 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 мин. Теперь удаляем временную таблицу и можно готовиться ко сну:

 

DROP TABLE #BadAds
GO
 

Итог: ещё один скрипт в копилку: Поиск + Обновление = Full-Text Search + Обновление порциями.

 

Ссылки по теме: 

blog comments powered by Disqus

Обо мне

MVP

Data Architect at Intapp, Inc.

PASS Regional Mentor, CEE

MCT, MCITP, MCPD, MCTS


Microsoft MVP

Month List