Почему характеристики этих двух запросов настолько разные?

У меня есть сохраненный proc, который ищет продукты (250 000 строк), используя полный текстовый индекс.

Сохраненный proc принимает параметр, который является условием полного текстового поиска. Этот параметр может быть нулевым, поэтому я добавил нулевую проверку, и запрос неожиданно начал работать на порядки медленнее.

-- This is normally a parameter of my stored proc
DECLARE @Filter VARCHAR(100)
SET @Filter = 'FORMSOF(INFLECTIONAL, robe)'

-- #1 - Runs < 1 sec
SELECT TOP 100 ID FROM dbo.Products
WHERE CONTAINS(Name, @Filter)

-- #2 - Runs in 18 secs
SELECT TOP 100 ID FROM dbo.Products
WHERE @Filter IS NULL OR CONTAINS(Name, @Filter)

Вот планы выполнения:

Запрос № 1 Execution plant #1

Запрос № 2 Execution plant #2

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

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

Ответы

Ответ 1

OR может сокрушить производительность, поэтому сделайте так:

DECLARE @Filter VARCHAR(100)
SET @Filter = 'FORMSOF(INFLECTIONAL, robe)'

IF @Filter IS NOT NULL
BEGIN
    SELECT TOP 100 ID FROM dbo.Products
    WHERE CONTAINS(Name, @Filter)
END
ELSE
BEGIN
    SELECT TOP 100 ID FROM dbo.Products
END

Посмотрите на эту статью: Динамические условия поиска в T-SQL от Erland Sommarskog и этот вопрос: SQL Server 2008 - Условный запрос.

Ответ 2

Первый план запроса выглядит просто:

  • полнотекстовый поиск для решения CONTAINS(Name, @Filter)
  • сканирование индекса для поиска других столбцов согласованных строк
  • объединить два, используя хеш-соединение

Оператор конкатенации образует объединение двух наборов записей. Таким образом, похоже на второй запрос:

  • сканирование индекса (позже использовалось для поиска других столбцов)
  • постоянное сканирование. Я предполагаю, что он обрабатывает ваш запрос как не параметризованный, поэтому план запроса не должен работать ни для какого другого значения @Filter. Если правильно, постоянное сканирование разрешает @Filter is not null.
  • полнотекстовый поиск для решения CONTAINS(Name, @Filter)
  • объединяет результат 3 с пустым множеством из 2 Цикл
  • объединяет результаты 1 и 4 для поиска других столбцов.

Хеш объединяет торговую память для скорости; если ваша система имеет достаточно памяти, она намного быстрее, чем объединение цикла. Это может легко объяснить 10-100-кратное замедление.

Одно исправление заключается в использовании двух разных запросов:

if @Filter is null
    SELECT TOP 100 ID FROM dbo.Products
else
    SELECT TOP 100 ID FROM dbo.Products WHERE CONTAINS(Name, @Filter)

Ответ 3

Вы ввели условие ИЛИ. В большинстве случаев намного проще проверить явно для NULL и выполнить один запрос против вашего метода.

Например, попробуйте следующее:

IF @Filter IS NULL
 BEGIN
SELECT TOP 100 ID FROM dbo.Products
END
ELSE
BEGIN
SELECT TOP 100 ID FROM dbo.Products
WHERE @Filter CONTAINS(Name, @Filter)
END