Full text search in SQL Server

uniqueman · Post by **uniqueman** » 27 Jan 2004 22:32

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

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

Спасибо

Post by **Win32nipuh** » 28 Jan 2004 09:10

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 · Post by **Dmitry67** » 28 Jan 2004 09:54

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

uniqueman · Post by **uniqueman** » 28 Jan 2004 15:03

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

Есть таблица с четырьмя полями. первое поле - 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 · Post by **chepackav** » 28 Jan 2004 16:31

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

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

uniqueman · Post by **uniqueman** » 28 Jan 2004 16:50

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

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

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

uniqueman · Post by **uniqueman** » 29 Jan 2004 23:26

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

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

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

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

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

А мне нужно чтобы искалась только фраза целиком.

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

Спасибо

vc · Post by vc » 30 Jan 2004 03:39

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

Мне надо найти в базе данных записи, которые содержат фразу допустим 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 · Post by **uniqueman** » 30 Jan 2004 15:31

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

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

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

Dmitry67 · Post by **Dmitry67** » 30 Jan 2004 16:46

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

uniqueman · Post by **uniqueman** » 30 Jan 2004 17:07

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

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

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

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

Sabina · Post by **Sabina** » 31 Jan 2004 00:40

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

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

Сабина

Post by **Win32nipuh** » 02 Feb 2004 09:49

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

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

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

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

, Китайский язык отрабатывается очень неплохо.

uniqueman · Post by **uniqueman** » 09 Feb 2004 19:44

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

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

uniqueman · Post by **uniqueman** » 10 Feb 2004 15:02

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

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

Post by **Win32nipuh** » 16 Feb 2004 11:21

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

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

Я не совсем понимаю, зачем Вы делаете full population ночью?
У Вас стоит change tracking with backgroung reindex - этого достаточно.
Возможен такой вариант: стартует full population, из-за большого объема он не успевает полностью проиндексировать таблицы, заполнить каталог, и Вы получаете кашу.
У меня был такой случай у клиента: слабый сервер, который не успевал индексировать, и на выходе получалась абракадабра.
Поэтому, я бы делал так: если ночью нагрузка на сервере меньше, чем днем, не трогайте сервер, пусть дотягивает индексы, и следите за свойствами каталога, входит ли он в состояние Idle хоть когда-нибудь? Если он постоянно в состоянии Paused, Change Tracking etc., значит он захлебывается, но продолжает "жрать кактусы" :-)

.
Добавьте память, настройте память...

КРоме всего, не забудьте, что построение индексов - это задача сервиса, внешнего по отношению к SQL Server. ЕСли Вы указали для SQL Server более высокий приоритет в системе, то он будет давить mssearch, и он не будет успевать индексировать.

Удачи.

uniqueman · Post by **uniqueman** » 17 Feb 2004 17:57

Я не совсем понимаю, зачем Вы делаете full population ночью?

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

каталога, входит ли он в состояние Idle хоть когда-нибудь? Если он постоянно в состоянии Paused, Change Tracking etc., значит он захлебывается, но продолжает "жрать кактусы" .

я сейчас посмотрел на состояние каталога. Оно стоит Shutdown. Что это? Перестали индексы обновлятся?

КРоме всего, не забудьте, что построение индексов - это задача сервиса, внешнего по отношению к SQL Server. ЕСли Вы указали для SQL Server более высокий приоритет в системе, то он будет давить mssearch, и он не будет успевать индексировать.

а где эти приоритеты ставятся? Вы имеете в виду приоритет процессов?

Post by **Win32nipuh** » 18 Feb 2004 20:47

Сервер сам все перестроит без Вашей помощи, на то у него и background, и changetracking указаны.Я думаю Вы усложняете ему работу.

uniqueman · Post by **uniqueman** » 18 Feb 2004 21:01

Проклятье. У каталога ни с того ни с сего появился статус Shutdown.

Половина результатов выборок неправильна сейчас

Что это за статус? Я ничего не трогал.

8K · Post by 8K » 18 Feb 2004 22:09

uniqueman wrote:Проклятье. У каталога ни с того ни с сего появился статус Shutdown.

Половина результатов выборок неправильна сейчас Что это за статус? Я ничего не трогал.

Каталог может рапортовать такой статус в течении недолгого времени (ну, там, минуту). Если статус не меняется - это признак catalog corruption at MS Search side. Я бы посоветовал заново создать каталог и проиндексировать таблицу.

Там выше дело говорят - не мучайте животинку. Запустите full crawl один раз, а потом дайте возможность автоматическому change tracking отслеживать изменения, вставки и удаления записей.

Если изменения происходят слишком часто и сервер не справляется в real time, есть два варианта: первый - ручной change tracking (т.е. та таблица, куда информация об изменениях складывается, отсылается в MS Search шедулером ночью), второй - отказаться от change tracking вообще и делать тем же самым шедулером incremental population (это в случае, если sysfulltextnotify за день раздувается до непомерно больших размеров).

Для incremental population вам понадобится завести timestamp колонку в своей таблице, иначе волшебным образом incremental внутри превращается в full.

8K · Post by 8K » 18 Feb 2004 22:14

uniqueman wrote:
КРоме всего, не забудьте, что построение индексов - это задача сервиса, внешнего по отношению к SQL Server. ЕСли Вы указали для SQL Server более высокий приоритет в системе, то он будет давить mssearch, и он не будет успевать индексировать.

а где эти приоритеты ставятся? Вы имеете в виду приоритет процессов?

Попробуйте поднять приоритет MS Search через

Code: Select all

sp_fulltext_service 'resource_usage'

uniqueman · Post by **uniqueman** » 18 Feb 2004 22:37

Каталог может рапортовать такой статус в течении недолгого времени (ну, там, минуту). Если статус не меняется - это признак catalog corruption at MS Search side. Я бы посоветовал заново создать каталог и проиндексировать таблицу..

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

Для incremental population вам понадобится завести timestamp колонку в своей таблице, иначе волшебным образом incremental внутри превращается в full.

Мне изменения индекса надо в real time делать. Справляется вроде с нагрузкой, но почему то выдаются неправильные резульаты.

8K · Post by 8K » 18 Feb 2004 22:49

uniqueman wrote:
Если статус не меняется - это признак catalog corruption at MS Search side. Я бы посоветовал заново создать каталог и проиндексировать таблицу..

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

К сожалению, это уже вопрос к MS Search, почему каталог попадает в такое состояние.

Я имел в виду удалить каталог и создать его заново, а не перестраивать. Всякие глюки могут быть; например, по какой-нибудь причине один из каталожных файлов имеет неправильные атрибуты доступа, или еще чего (это я спекулирую). Бывают приколы, что два каталога смотрят в одну директорию.

Затем вам придется заново создать full-text index для вашей таблицы и сделать full population. После этого включите change tracking и background population.

8K · Post by 8K » 18 Feb 2004 22:54

uniqueman wrote:Мне изменения индекса надо в real time делать. Справляется вроде с нагрузкой, но почему то выдаются неправильные резульаты.

Ну, про это я уже в параллельном топике написал. Убедитесь, что результаты действительно неправильные (может, вы неверно понимаете семантику CONTAINS или FREETEXT). Далее, убедитесь, что в sysfulltextnotify этой неправильной записи не висит, т.е. она ушла для индексирования в MS Search. Ввиду асинхронности индекса фантомы возможны. Еще, какой у вас на самом деле тип колонки? TEXT, NTEXT, VARCHAR, NVARCHAR?

uniqueman · Post by **uniqueman** » 18 Feb 2004 23:03

действительно неправильные (может, вы неверно понимаете семантику CONTAINS или FREETEXT).

да вроде согласно MSDN в семантике особенного ничего нет.

Далее, убедитесь, что в sysfulltextnotify этой неправильной записи не висит, т.е. она ушла для индексирования в MS Search.

эта системная таблица у меня пустая.

Ввиду асинхронности индекса фантомы возможны. Еще, какой у вас на самом деле тип колонки? TEXT, NTEXT, VARCHAR, NVARCHAR?

text оба поля

Привет

Full text search in SQL Server

Full text search in SQL Server

Re: Full text search in SQL Server

FTS

Re: FTS