Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Всем доброе время суток,
возможно плохо пользовался поиском, но он мне не особо помог.

Задача такая

1) Выполнить полнотекстовый поиск по таблице
2) упорядочить возможно крайне большой (пусть 100 000) результат FTS и вывести "нужну маленькую страницу" (пусть 100 записей)

Поиск ФТС в целом работает быстро (для такого количества записей), но потом необходимо упорядочить результат FTS по полую (пусть это будет "цена") и вывести результат максимально быстро (значительно быстрее 1 сек).

Физические проблемы того что сначала необходимо данные найти ФТСом (и топ 100 не для них делать) потом приджойнить их ВСЕ к основной таблице опять же, потом просканировать индекс с полем (полями) по которым упорядочивать необходимо, который так же большой и значения "цена" могут быть равными больше чем у 100 записей.

Хотелось бы получить совет "как ускорить логику запроса", например, возможно есть способ застравить нужный top 100 вернуть уже ФТСом, что бы поле а-ля "цена" в зависимости от своего ЧИСЛОВОГО значения (или даты или алфавитного номера и т. п.) влияла на RANK в FTS.

По условиям задачи считаем что все индексы есть и запросы "прямые",
Запрос всегда делает "Top (КоличествоЗаписейНаСтранице*НаномерСтранице)" (переменной естественно).
4 апр 12, 16:02    [12365296]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
NIIIK
Задача такая
Какая версия сиквела?
4 апр 12, 17:00    [12365845]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
alexeyvg,

2008R2
5 апр 12, 12:36    [12369767]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
я уже думаю о всяких "костыльных решениях" что бы увеличить ранг записи на основе не текстовых атрибутов, например

1) заводим дополнительное поле (текстовое)
2) если цена 5 долларов, то ставим "пять символов", если цена 10, ставим 10ть символов
(по факту "кодировка" может быть более сложная, естественно)

Но там же ещё ранг "основного поиска", он тоже проблема.

Вообще изначально глупая "хотелка" потому что по ФТСу может быть найдено вагон записей с разным рангом, а потом независимо от этого ранга необходимо всех и джойнить и упорядочивать по другому атрибуту.

Тут вообще два шага "найти всё для ФТС" - "использовать огроменный результат ФТСа и отсортировать его по 3им атрибутам".
5 апр 12, 12:47    [12369826]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
NIIIK
alexeyvg,

2008R2
В этой версии сервер выбирает план с полнотекстовом поиском достаточно оптимально. Он будет учитывать статистику и выберет один из трёх вариантов - взять все результаты полнотекстового поиска и отсортировать их после джойна, либо сортировать сразу и делать проверять каждую запись в полнотекстовом индексе, либо найти оба множества и сделать джойн.

Первый вариант хорош, если полнотекстовым поиском находится мало записей, второй - если много, третий - если остальными условиями накладывается хороший фильтр.

Начиная с 2008 это работает довольно неплохо, но конечно сервер тоже может ошибиться...
5 апр 12, 13:11    [12369960]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
ФТС ищет быстро, находит "много".
Это "много" надо опять приджойнить (к той же) таблице и упорядочить по полю из этой таблице (не ФТС).

Т.е. один из вариантов и так заведомо реализован.

Долго это потому что на уровне ФТС top 100 записей не сделать пока не приджойнить все "равноправные" записи и не упорядочить.
5 апр 12, 16:23    [12371447]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
NIIIK
Долго это потому что на уровне ФТС top 100 записей не сделать пока не приджойнить все "равноправные" записи и не упорядочить.
Почему вы думаете, что сервер выбирает именно такой план?

Или вы явно делаете вышеописанное, не давая серверу выбрать какой то другой вариант?

NIIIK
Т.е. один из вариантов и так заведомо реализован.
Так код то какой?
5 апр 12, 19:50    [12372762]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
alexeyvg
NIIIK
Долго это потому что на уровне ФТС top 100 записей не сделать пока не приджойнить все "равноправные" записи и не упорядочить.
Почему вы думаете, что сервер выбирает именно такой план?

Или вы явно делаете вышеописанное, не давая серверу выбрать какой то другой вариант?

NIIIK
Т.е. один из вариантов и так заведомо реализован.
Так код то какой?


думаю это у автора такая задача, санчала выбрать, потом джойнуть и уже потом ордер и первые Н.
У меня похожая фигня
5 апр 12, 20:28    [12372917]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
Winnipuh
alexeyvg
пропущено...
Почему вы думаете, что сервер выбирает именно такой план?

Или вы явно делаете вышеописанное, не давая серверу выбрать какой то другой вариант?

пропущено...
Так код то какой?


думаю это у автора такая задача, санчала выбрать, потом джойнуть и уже потом ордер и первые Н.
У меня похожая фигня
Таких задач не бывает, это называется "реализация"

Задача, это "нажимаю кнопку, на экране список с такой то сортировкой".
5 апр 12, 23:05    [12373460]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
l_slava
Member

Откуда:
Сообщений: 17
alexeyvg,
стандартная задача

1. Открываем сайт амазона
2. Search: Office Products и ищем слово "paper" : Showing 1 - 24 of 238,279 Results
3. Sort by: Price: Low to High

И того надо из 238,279 отобразить 24 самых дешевых. Вопрос как это сделать быстрее чем за 1 сек?
13 апр 12, 14:48    [12412368]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
alexeyvg,

Уверяю вас, задача такая.

Только "хотелка заказчика" такая что надо что бы "летало".

И запрос написан так, что ФТСом сначала ищутся все записи и его ранк не влияет (или является последним) на порядок сортировки найденных записей. Я уверен что план запроса такой, что сортируются записи только найденные ФТСом, но их много и их надо отсортировать (по не ФТСному полю). А время вполнения 1секунда для заказчика уже много, когда он видет что в гугле или на амазоне результаты поиска за 0,0ХХ секунды.

Если бы можно было бы создать насколько ФТС инлексов с порядком (правилом) сортировки по другим полям и потом уже на уровне ФТСа выбирать нужное количество строк - было бы значительно лучше.

Если бы была бы какая-то "Альтернативная ФТС для МС СКЛ Сервера".

Sphinx это умеет вроде делать, но по сути "отдельнная зрень" - это необзодим целый огород с обновлением базы и по взаимодействию
1) либо приложение ищет Sphinx, а потом продолжается работа уже с МС СКЛ и приложение передаёт "найденные строки" в МС СКЛ при необходимости
2) либо МС СКЛ как-то коннектится к Sphinx (может табличная CLR) и приложение не знает что запрос изначально выполнялся совсем не в Ms SQL. Но тут в любом случае "лишнее звено".
13 апр 12, 14:57    [12412469]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
>Вопрос как это сделать быстрее чем за 1 сек?
А если ещё бы пейджинг сделать
не Top (@lastRow), row_number() .. as rowNum ... + проверка на следующем подзапросе where rowNum >= @firstRowOnPage

а как бы "окном", как например, если мы сортируем по primary key c типом INT и для следующей странице (только при кликании по страницам последовательно) можем передать идешник последней записи на предыдущей странице и поставить проверку where pk > @lastPK.
Что бы последняя страница тоже долго не выбиралась бы.

Заранее спасибо :)
13 апр 12, 15:16    [12412676]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
NIIIK
И запрос написан так, что ФТСом сначала ищутся все записи и его ранк не влияет (или является последним) на порядок сортировки найденных записей.
Так какой текст запроса?
NIIIK
Я уверен что план запроса такой, что сортируются записи только найденные ФТСом, но их много и их надо отсортировать (по не ФТСному полю).
Вы это увидели в плане запроса?

l_slava
И того надо из 238,279 отобразить 24 самых дешевых. Вопрос как это сделать быстрее чем за 1 сек?
Для начала попробовать просто написать запрос с поиском и сортировкой, не забыв сделать индекс по Price, посмотреть план запроса.

Посмотреть распределение данных, подумать, правильный ли план выбирает сиквел, можно ли придумать план лучше. Если можно, то подумать, почему сиквел не использует хороший план, а использует плохой, как это поправить.
NIIIK
Если бы была бы какая-то "Альтернативная ФТС для МС СКЛ Сервера".
Ну вот выже пишите про альтернативную ФТС для сиквела. Можно и самому написать, тут в форуме есть хороший пример, прямо с кодом.

Но для начала нужно разобраться с имеющимся функционалом.
13 апр 12, 15:17    [12412678]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
l_slava
Member

Откуда:
Сообщений: 17
alexeyvg,
Вот набросал небольшой пример:
Всего в таблице Catalog_Test - 10 млн. записей
ФТС по слову paper выдает 200 000

select top 20 c.Price
from Catalog_Test c
join CONTAINSTABLE(Catalog_Test, (Product_Description), '"paper"') s
on c.PK_Catalog = s.[KEY]
order by c.Price

|--Top(TOP EXPRESSION:((20)))
|--Parallelism(Gather Streams, ORDER BY:([c].[Price] ASC))
|--Sort(TOP 20, ORDER BY:([c].[Price] ASC))
|--Hash Match(Inner Join, HASH:(FulltextMatch.[docid])=([c].[PK_Catalog]), RESIDUAL:(FulltextMatch.[docid]=[dbo].[Catalog_test].[PK_Catalog] as [c].[PK_Catalog]))
|--Bitmap(HASH:(FulltextMatch.[docid]), DEFINE:([Bitmap1004]))
| |--Stream Aggregate(GROUP BY:(FulltextMatch.[docid]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:(FulltextMatch.[docid]), ORDER BY:(FulltextMatch.[docid] ASC))
| |--Stream Aggregate(GROUP BY:(FulltextMatch.[docid], FulltextMatch.[colid]))
| |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:(FulltextMatch.[docid], FulltextMatch.[colid]))
| |--Table-valued function
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([c].[PK_Catalog]))
|--Index Scan(OBJECT:([dbo].[Catalog_test].[idx_Price] AS [c]), WHERE:(PROBE([Bitmap1004],[dbo].[Catalog_test].[PK_Catalog] as [c].[PK_Catalog],N'[IN ROW]')))
13 апр 12, 15:52    [12413011]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Нас со l_slava так же устроит какой-то "красивый вариант" со сторонним (не МС СКЛ) FTS, но прикрученном к МС СКЛ.
Сейчас рассматривается вариант, как основной,
1) использовать Sphinx
2) CLR с табличной функцией которая обращается к Sphinx (ищет в Sphinx, возвращает строки, остальное на МС СКЛ).
2.1) Возможно поиск выполнять напрямую в Sphinx с приложения, потом возвращаться в МС СКЛ для дальнейшей работы

Сами понимаем что известные нам возможности МС СКЛ исчерпаны. Сама задача тоже ресурсоёмкая при решении её стандартными методами. ФТС вообще только по тексту ищет и действительно возвратит "все записи" и все их джойнить и упорядочивать.
13 апр 12, 16:05    [12413104]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
alexeyvg,

не сразу обновилось
>Ну вот выже пишите про альтернативную ФТС для сиквела. Можно и самому написать, тут в форуме есть хороший пример, прямо с кодом.

Сам припоминаю, пытаюсь найти, не получается. Знаете где точно есть?

У ФТСа стандартного две основные проблемы. Во первых он сам сторонний и одновременно (даже если запрос один) выполнять ФТС и накладывать остальные условия (период дат, цен и т. п.) МС СКЛ не заставиш. Во вторых его нельзя заставить сортировать результаты не по ФТС Тексту.
13 апр 12, 16:13    [12413170]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Как направление:Можно посторить результаты заранее. На основании данных из sys.dm_fts_index_keywords_by_document строим таблицу (/индуксированное представление) - топы строк для каждого ключевого.

Ссори, если не в тему, в FTS нуб.
13 апр 12, 16:14    [12413175]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Mnior
Как направление:Можно посторить результаты заранее.
Ссори, если не в тему, в FTS нуб.


Если "в принципе", а не "в Мс СКЛ"
Как несколько разных индексов с разными правилами/направлениями сортировки, как если мы делаем какой-то индекс, например по дате в двух экземплярах при этом один ASK, другой DESK.
13 апр 12, 16:20    [12413228]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
NIIIK
Сам припоминаю, пытаюсь найти, не получается. Знаете где точно есть?

Что то в блоге на SQL.RU нету, нашёл на gotdotnet: http://www.gotdotnet.ru/blogs/DeColores/1914/
NIIIK
У ФТСа стандартного две основные проблемы. Во первых он сам сторонний и одновременно (даже если запрос один) выполнять ФТС и накладывать остальные условия (период дат, цен и т. п.) МС СКЛ не заставиш. Во вторых его нельзя заставить сортировать результаты не по ФТС Тексту.
Я же вам писал, какие алгоритмы использует сиквел. Может, вы судите по версии меньше 2008? Архитектура FTS была кардинально перестроена.

l_slava
alexeyvg,
Вот набросал небольшой пример:
Надо было в тег SRC оформить, а то уровни вложенности теряются, читать план неудобно...
13 апр 12, 17:43    [12413689]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
NIIIK
Во вторых его нельзя заставить сортировать результаты не по ФТС Тексту.
Собственно, на самом деле проблема в том, что нельзя делать составные индексы ФТС + обычные поля.
l_slava
alexeyvg,
Вот набросал небольшой пример:
И обязательно проверьте вариант:
select top 20 *
from Catalog_Test c
where contains(Product_Description, '"post"')
order by c.Price
13 апр 12, 18:37    [12413932]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
l_slava
select top 20 c.Price
from Catalog_Test c
join CONTAINSTABLE(Catalog_Test, (Product_Description),  '"paper"') s
on c.PK_Catalog = s.[KEY]
order by c.Price 
  |--Top(TOP EXPRESSION:((20)))
|--Parallelism(Gather Streams, ORDER BY:([c].[Price] ASC))
|--Sort(TOP 20, ORDER BY:([c].[Price] ASC))
|--Hash Match(Inner Join, HASH:(FulltextMatch.[docid])=([c].[PK_Catalog]), RESIDUAL:(FulltextMatch.[docid]=[dbo].[Catalog_test].[PK_Catalog] as [c].[PK_Catalog]))
|--Bitmap(HASH:(FulltextMatch.[docid]), DEFINE:([Bitmap1004]))
| |--Stream Aggregate(GROUP BY:(FulltextMatch.[docid]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:(FulltextMatch.[docid]), ORDER BY:(FulltextMatch.[docid] ASC))
| |--Stream Aggregate(GROUP BY:(FulltextMatch.[docid], FulltextMatch.[colid]))
| |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:(FulltextMatch.[docid], FulltextMatch.[colid]))
| |--Table-valued function
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([c].[PK_Catalog]))
|--Index Scan(OBJECT:([dbo].[Catalog_test].[idx_Price] AS [c]), WHERE:(PROBE([Bitmap1004],[dbo].[Catalog_test].[PK_Catalog] as [c].[PK_Catalog],N'[IN ROW]')))
13 апр 12, 19:22    [12414171]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый поиск по FTS + упорядочивание "не по RANK"  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
l_slava
alexeyvg,
Вот набросал небольшой пример:
Всего в таблице Catalog_Test - 10 млн. записей
ФТС по слову paper выдает 200 000


Я думала у меня проблемы....
У меня по поиску может вывалиться 2 000 000 строк.
И вот надо как то первую 1000 из них быстро выбрать ....

Когда 200 000, то за секунду все пролетает... а вот миллионы уже медленно.
18 апр 12, 16:15    [12434130]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить