Full text search in SQL Server

[uniqueman]

Есть такая задача. В базу данных поступает относительно большой поток информации. Он сколько не большой за одну транзакцию, сколько волнует количество таких транзакций. В секунду может запросто поступить 5 записей по 8К текста каждая. Может и больше..

Клиентское приложение может в любой момент составить такой запрос:

"Дайте мне последние 50 записей , которые содержат слово market".

Я натравил на таблицу, в которой сохраняются данные, Full Text Search feature.

тогда с учетом этой фичи, запрос будет выглядеть так

SELECT TOP 50 *
FROM Table
WHERE CONTAINS (*, 'market')
ORDER BY insert_time DESC

Проблема в том, что это будет работать только на те записи, которые были проиндексированы виндовым Index Service. У меня стоит full re-population этого каталога каждую полночь. Иными словами если этот запрос будет составлен в течение дня, то вернутся только записи только за вчера, потому что текущие записи которые поступают еще не проиндексированы.

Как обойти эту проблему? Делать full repopulation при вставке каждой записи будет очень дорого. Читал про change tracking и про incremental population но не понял до конца.

Какие могут быть выходы из положения?

Спасибо

[Win32nipuh]

Full population это накладно, кроме того, по ходу работы юзеров изменения не видны в поиске.
Я делаю так после того, как каталог создан:

exec sp_fulltext_catalog @catname, 'start_full'
exec sp_fulltext_table N'[dbo].[t1]', 'Start_change_tracking'
exec sp_fulltext_table N'[dbo].[t1]', 'Start_background_updateindex'

[Dmitry67]

А вот такая идея
Все новые записи складировать в еще одну таблицу
Ночью запускать реиндексация а таблице делать truncate
При посике же делать full text search union all с результатом поиска по LIKE '%%' в этой таблице (по идее не очень большой).

[uniqueman]

давайте поподробнее расскажу как обстоят дела. Может я что не так делаю.

Есть таблица с четырьмя полями. первое поле - id (identity), второе - header (тип поля - text), третье - body (тип поля - text), четвертое - datetime (показывает когда была вставлена запись).

Информация приходит в виде header + body. Размер header небольшой, где то порядка 100 символов. Размер же body может достигать 8К. Вот эта информация вставляется в таблицу. В день вставляется порядка 8000 таких записей.

Поля header и body в таблице проиндексированы для Full Text Search (оба).

Информация в таблице хранится только за последние пять дней. Каждую ночь запускается job , который удаляет записи старые (оставляет только за последние пять дней). Следующая job - full population - следует сразу за этим. Происходит переиндексация. После этотго срабатывает третья job - database backup. Все...

Как я говорил выше стоит задача делать поиск по определенному слову в таблице в любой момент времени.

exec sp_fulltext_catalog @catname, 'start_full'
exec sp_fulltext_table N'[dbo].[t1]', 'Start_change_tracking'
exec sp_fulltext_table N'[dbo].[t1]', 'Start_background_updateindex'

Можно поподробнее? В какие моменты делаете это? Через job делаете? Как часто?

Ночью запускать реиндексация а таблице делать truncate

я так понял, что алгоритм такой. Взять все строки из маленькой таблицы, вставить их в основную, проиндексировать основную таблицу заново, удалить строки из маленькой таблицы и начать складывать туда новые, так?

А как в job написать команду копирования строк из одной таблицы в другую?

[chepackav]

Можно поподробнее? В какие моменты делаете это? Через job делаете? Как часто?

Зайдите в BooksOnLine и запустите поиск по change tracking and update index in background - документация, это вообще весьма полезная вещь. Вы, правда, не сказали какая у Вас версия SQL Server - приведенный Win32nipuh совет работает только в SQL2K.

[uniqueman]

Можно поподробнее? В какие моменты делаете это? Через job делаете? Как часто?

Зайдите в BooksOnLine и запустите поиск по change tracking and update index in background - документация, это вообще весьма полезная вещь. Вы, правда, не сказали какая у Вас версия SQL Server - приведенный Win32nipuh совет работает только в SQL2K.

да, поставил то что написал Пух через Enterpise Manager. Действительно заработало. Все изменения отражаются в запросах. ВСе в реальном времени. Но вот памяти и проца жрет немеряно. Сейчас на машине стоит 512 метров. Думаю добавить еще столько же.

[uniqueman]

Вот такая проблема.

Мне надо найти в базе данных записи, которые содержат фразу допустим market closed. В соотвествии с мануалом пишу

SELECT *
FROM table
WHERE CONTAINS (8, ' "market closed" ')

То бишь внутри одиночных кавычек всю фразу заключаю в двойные кавычки.

Однако мне возвращаются также строки, в которых присутствует либо слово market либо closed либо фраза целиком А мне нужно чтобы искалась только фраза целиком.

Что не так делаю?*

Спасибо

[vc]

Вот такая проблема.

Мне надо найти в базе данных записи, которые содержат фразу допустим market closed. В соотвествии с мануалом пишу

SELECT *
FROM table
WHERE CONTAINS (8, ' "market closed" ')

То бишь внутри одиночных кавычек всю фразу заключаю в двойные кавычки.

Однако мне возвращаются также строки, в которых присутствует либо слово market либо closed либо фраза целиком А мне нужно чтобы искалась только фраза целиком.

Что не так делаю?*

Спасибо

This works for me:

Code: Select all

create table t1(id int identity, x varchar(100))

insert into t1(x) values('market closed')
insert into t1(x) values('closed')
insert into t1(x) values('market')
insert into t1(x) values('the day the market closed')


sp_fulltext_catalog 'test', 'create' 
sp_fulltext_table 't1', 'create', 'test', 't1_idx'
sp_fulltext_column 't1', 'x', 'add'  
sp_fulltext_table 't1', 'activate'   
sp_fulltext_catalog 'test', 'start_full'
SELECT FulltextCatalogProperty ('test', 'PopulateStatus')
0

SELECT * 
FROM t1
WHERE CONTAINS (x, ' "market closed" ') 

1   market closed
4   the day the market closed

Rgds.

[uniqueman]

Что то не то. Текст в поле гораздо больше чем просто "market". Там 8К текста. И запрос возвращает запись, даже если встретилось слово "market" просто. Мне нужно именно "market closed" выражение чтобы было.

У меня стоит change tracking with background reindexing. Но я не думаю что это влияет.

ну спецы по SQL Server , где же вы когда необходимо. Тут вроде кто то даже занимался разработкой этого продукта. Что я не так делаю?

[Dmitry67]

Мы тута
Просто full text search довольно специфическая область
Помнб tengiz говорил что вроде они собираются все переписать потому что fts сделан из рук вон плохо

[uniqueman]

простите уважаемые, по моему это я напортачил.

У меня ночью происходит две задачи. Одна удаляет данные старые из таблицы, вторая делает full population. Так вот вторая задача выполнялась первее первой. ТО есть сначала делался population, а потом удалялись строки. Логически это неправильно я думаю. После удаления строк индексы будут указывать на нерпавильные данные.

Вообщем переставил местами задачи.

И еще. У меня на таблице стоит change tracking with background reindex. После того как сработает full population ночью, не надо ничего заново устанавливать в этом плане? change tracking так и будет продолжать работать?

[Sabina]

....При посике же делать full text search union all с ...

При чем, при чем, простите ?

Сабина

[Win32nipuh]

То, что я написал выше делается один раз.
Например, я поставляю базу заказчику, в бэкапе.
Восстановил, создал каталог с нужным языком поиска, выполнил указанные выше утверждения, т.е.
1. full population,
2. changetracking
3. background

Заметьте, пункты 2 и 3 выполняются для каждого полноиндексируемого поля.
Все, больше НИЧЕГО по ходу работы делать не нужно.
Пункт 1 - выполняет начальную полную индексацию, без этого никуда,
2 и 3 - устанавливают признак, он даже остается, если база была завернута в бэкап с каталогом.
В этом случае, после ресторе Вам нужно сказать каталогу Rebuild, больше ничего.
Честно говоря, я не знаю зачем в этом случае ночью полный репопулэйт насильно делать? Ведь все отрабатывает "на лету".

Согласен с Dmitry67, в MS SQL полнотекстовый поиск/индексация сделаны так, что им есть еще над чем поработать.
Это может подтвердить и Alexandr Chepack, участвующий в этом форуме.

И еще: у меня на некоторых серверах при массированной всатвке/обновлении полнотекстовых полей сервер не всегда успевает индексировать, бывает, что он сам уходит в состояние инкрементальной доиндексации, но потом, когда нагрузка на него падает, он успевает дотянуть индексы. А ужесли он ночью сам работает - тогда и доиндексирует.
Job'ы я тоже для этого никакие не запускаю.
Ну и еще: такая нагрузка наблюдается у тех серверов, которые делают индексацию для языков, для которых нет списка шумных слов, и нет парсеров, знающих структуру языка, словоформы и т.д.
Каталоги в этом случае большие, и индексация идет медленнее, как пример, последний сервер с English+Arabic.
, Китайский язык отрабатывается очень неплохо.

[uniqueman]

Честно говоря, я не знаю зачем в этом случае ночью полный репопулэйт насильно делать? Ведь все отрабатывает "на лету".

у меня каждую ночь удаляются некотороые записи из таблицы. Я храню данные только за последние пять дней. Мне показалось, что после удаления записей из таблицы надо сделать заново full population, чтобы переиндексировать заново все записи. Разве это делать не нужно?

[uniqueman]

хм. проблема до сих пор существует. При попытке найти что то подобное "market closed", выдаются как правильные результаты, так и записи, не имеющие такой пары слов.

Может ли это быть из за того, что ночью я удаляю некоторые записи а потом делаю full population? У меня на таблице стоит change tracking with backgroung reindex. Нужно ли делать все равно полную population после удаления записей?