Как оптимизировать полнотекстовый поиск SQL Server

Question

Я хочу использовать fulltextsearch для службы автозаполнения, а это значит, что она нужна мне для быстрой работы! До двух секунд макс.

Результаты поиска взяты из разных таблиц, поэтому я создал представление, которое объединяет их. Я использую функцию SQL FREETEXTTABLE().

Запрос выполняется очень медленно, иногда до 40 секунд.

Чтобы оптимизировать время выполнения запроса, я убедился, что в базовой таблице есть столбец кластеризованного индекса, который является целочисленным типом данных (а не GUID)

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

Answer 1

Причина, по которой ваш запрос очень быстрый в первый раз после перестройки каталога, может быть очень простой:

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

Поэтому тестирование скорости запроса имеет смысл только после завершения перестроения индексов.

Следующий выбор может пригодиться для проверки размера (а также фрагментации) индексов. Когда размер перестает расти, перестройка индексов заканчивается;)

-- Compute fragmentation information for all full-text indexes on the database
SELECT c.fulltext_catalog_id, c.name AS fulltext_catalog_name, i.change_tracking_state,
    i.object_id, OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS object_name,
    f.num_fragments, f.fulltext_mb, f.largest_fragment_mb,
    100.0 * (f.fulltext_mb - f.largest_fragment_mb) / NULLIF(f.fulltext_mb, 0)  AS fulltext_fragmentation_in_percent
FROM sys.fulltext_catalogs c
JOIN sys.fulltext_indexes i
    ON i.fulltext_catalog_id = c.fulltext_catalog_id
JOIN (
    -- Compute fragment data for each table with a full-text index
    SELECT table_id,
        COUNT(*) AS num_fragments,
        CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_mb,
        CONVERT(DECIMAL(9,2), MAX(data_size/(1024.*1024.))) AS largest_fragment_mb
    FROM sys.fulltext_index_fragments
    GROUP BY table_id
) f
    ON f.table_id = i.object_id

Answer 2

Вот хороший ресурс, чтобы проверить. Однако, если вы действительно хотите повысить производительность, вам придется подумать об обновлении вашего оборудования. (Я увидел значительное увеличение производительности за счет перемещения моих данных и файлов полнотекстового индекса на отдельные диски, оптимизированные для чтения, и перемещения журналов и базы данных tempdb на отдельные диски, оптимизированные для записи - всего 4 дополнительных диска плюс еще 1 для ОС и SQL Двоичные файлы сервера.)

Некоторые другие не аппаратные решения, которые я рекомендую:

1. Настройте встроенный список стоп-слов, чтобы определить больше стоп-слов, тем самым уменьшая размер вашего полнотекстового индекса.
2. Измените структуру файла tempdb. Смотрите здесь и здесь.
3. Если ваше представление выполняет более 1 вызова FREETEXTTABLE, рассмотрите возможность изменения структуры данных таким образом, чтобы представление выполняло только 1 вызов.

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