Sql служат, Полнотекстовый поиск с Containstable является очень медленным при Использовании в СОЕДИНЕНИИ!

Я потратил довольно много времени на этот вопрос, и на основе выполнения многих сценариев, вот что я выяснил:

если у вас есть Contains или ContainsTable где-либо в вашем запросе, это та часть, которая выполняется первой и довольно независимо. Это означает, что даже если остальные условия ограничивают ваш поиск только одной записью, ни Contains, ни containstable не заботятся об этом. Таким образом, это похоже на параллельное выполнение.

Теперь, поскольку полнотекстовый поиск возвращает только поле Key, он немедленно ищет Key как первое поле других индексов, выбранных для запроса. Поэтому в приведенном выше примере он ищет индекс с [key], field1, field2. Проблема в том, что он выбирает индекс для остальной части запроса на основе полей в предложении where. Так, для примера выше он выбирает покрытый индекс, который у меня есть, а это что-то вроде field1, field2, Id. (Id таблицы совпадает с [Key], полученным в результате полнотекстового поиска). Итак, итог таков:

1. выполняется containstable
2. выполняется остальная часть запроса и выбирается индекс на основе пункта where запроса
3. Он пытается объединить эти два индекса. Поэтому, если индекс, который он выбрал для остальной части запроса, начинается с поля [key], то все в порядке. Однако если индекс не имеет поля [key] в качестве первого ключа, он начинает выполнять циклы. Он даже не выполняет сканирование таблицы, иначе перебор 5000 записей не был бы таким медленным. Цикл выполняется так: он запускает цикл для общего числа результатов от FTS, умноженного на общее число результатов от остальной части запроса. Так что если FTS возвращает 2000 записей, а остальная часть запроса возвращает 3000, то цикл будет 2000*3000= 6,000,000. Я не понимаю, почему.

Итак, в моем случае он выполняет полнотекстовый поиск, затем выполняет остальную часть запроса, но выбирает покрытый индекс, который у меня основан на поле1, поле2, id (что неверно), и в результате все портится. Если я изменю свой покрытый индекс на Id, field1, field2, то все будет очень быстро.

Я ожидал, что FTS вернет кучу [key], остальные запросы вернут кучу [Id], а затем Id должен быть сопоставлен с [key].

Конечно, я попытался упростить свой запрос, но на самом деле запрос намного сложнее, и я не могу просто изменить индекс. У меня также есть сценарии, в которых текст, переданный в full text, является пустым, и в этих сценариях я даже не хочу присоединяться к containstable. В этих случаях изменение моего покрытого индекса, чтобы первым полем было поле id, приведет к катастрофе.

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

спасибо

Обычно это работает очень быстро:

select t1.*, t2.Rank
    from containstable(table1, field2, 'something') as t2
        join table1 as t1 ON t1.id = t2.Key AND t1.field1=90
    order by t2.Rank desc

Существует большая разница в том, где вы указываете критерии поиска: в JOIN или в WHERE.

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

Если да, то мой ответ из этой темы будет применим.

От http://technet.microsoft.com/en-us/library/cc721269.aspx#_Toc202506240

Самое главное, чтобы был выбран правильный тип соединения выбран для полнотекстового запроса. Кардинальность оценка кардинальности на FulltextMatch STVF очень важна для правильного плана. Поэтому первое, что необходимо проверить, это Оценка кардинальности FulltextMatch. Это расчетное количество совпадений в индексе для полнотекстовой поисковой строка. Например, в запросе на Рисунок 3 это число должно быть близко к количество документов, содержащих термин "слово". В большинстве случаев это должно быть быть очень точным, но если оценка сильно отклоняется, вы можете создать плохие планы. Оценка для одиночных сроков обычно очень хороша, но оценка нескольких терминов, таких как фразы или И-запросы, является более сложной задачей поскольку невозможно узнать, что пересечение терминов в индексе будет основано на частоте терминов в индексе. Если кардинальность оценка кардинальности хорошая, плохой план вероятно, вызван запросом модель затрат оптимизатора. Единственный способ исправить проблему с планом - это использовать запрос подсказку для принудительного использования определенного типа объединения или ОПТИМИЗИРОВАТЬ ДЛЯ.

Таким образом, он просто не может знать из хранящейся в нем информации, являются ли 2 поисковых термина вместе достаточно независимыми или часто встречаются вместе. Возможно, вам следует иметь две отдельные процедуры, одну для однословных запросов, которые вы позволяете оптимизатору делать свою работу, и одну для многословных поисковых запросов, для которых вы принудительно составляете "достаточно хороший" план (sys.dm_fts_index_keywords может помочь, если вы хотите сделать грубую оценку кардинальности самостоятельно).

Если вы столкнулись с проблемой при однословных запросах, то здесь может подойти отрывок из статьи по ссылке.

В полнотекстовом поиске SQL Server 2008 у нас есть возможность изменить план, который генерируется на основе оценки кардинальности. сгенерированный на основе оценки кардинальности используемого поискового термина. Если план запроса фиксирован (как в параметризованном запросе внутри хранимой процедуры), этот шаг не выполняется. не выполняется. Поэтому составленный план всегда обслуживает данный запрос, даже если этот план не является идеальным для данного поискового термина.

Поэтому вам может понадобиться использовать опцию RECOMPILE.