Простое изменение приводит к значительному увеличению времени выполнения SQL-запроса.

Question

Я выполняю следующий SQL-запрос в моей базе данных Microsoft SQL Server (2012 Express), и он работает нормально, выполняя менее чем за секунду:

SELECT
  StringValue, COUNT(StringValue)
FROM Attributes
WHERE
  Name = 'Windows OS Version'
  AND StringValue IS NOT NULL
  AND ProductAssociation IN (
    SELECT ID
    FROM ProductAssociations
    WHERE ProductCode = 'MyProductCode'
  )
GROUP BY StringValue

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

SELECT
  StringValue, COUNT(StringValue)
FROM Attributes
WHERE
  Name = 'Windows OS Version'
  AND StringValue IS NOT NULL
  AND ProductAssociation IN (
    SELECT ID
    FROM ProductAssociations
    WHERE ProductCode = 'MyProductCode'
    AND ID IN (
      SELECT A2.ProductAssociation
      FROM Attributes A2
      WHERE A2.Name = 'Is test' AND A2.BooleanValue = 0
    )
  )
GROUP BY StringValue

Но когда я добавляю переменную flag, чтобы позволить мне "включать / выключать" фильтр во внутреннем запросе, и устанавливать флаг в ноль, кажется, что запрос выполняется бесконечно (я оставил его работающим около 5 минут, а затем принудительно отменил):

DECLARE @IsTestsIncluded bit
SET @IsTestsIncluded = 0

SELECT
  StringValue, COUNT(StringValue)
FROM Attributes
WHERE
  Name = 'Windows OS Version'
  AND StringValue IS NOT NULL
  AND ProductAssociation IN (
    SELECT ID
    FROM ProductAssociations
    WHERE ProductCode = 'MyProductCode'
    AND (
      @IsTestsIncluded = 1
      OR
      ID IN (
        SELECT A2.ProductAssociation
        FROM Attributes A2
        WHERE A2.Name = 'Is test' AND A2.BooleanValue = 0
      )
    )
  )
GROUP BY StringValue

Зачем? Что я делаю неправильно? Клянусь, я использовал этот шаблон в прошлом без проблем.

(Когда я установил @IsTestsIncluded = 1 в последнем запросе выше фильтр пропускается и время выполнения нормальное - задержка происходит только тогда, когда @IsTestsIncluded = 0)

---

РЕДАКТИРОВАТЬ

Согласно запросу Джоэла в комментариях, вот план выполнения для первого запроса:

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

(Я не могу опубликовать план выполнения для 3-го запроса, так как он никогда не завершается - если нет другого способа получить его в SSMS?)

Answer 1

Попробуй это:

SELECT
  a.StringValue, COUNT(a.StringValue)
FROM Attributes a
INNER JOIN ProductAssociations p ON a.ProductAssociation = p.ID
    AND p.ProductCode = 'MyProductCode'
LEFT JOIN Attributes a2 ON a2.ProductAssociation = p.ID
    AND a2.Name = 'Is Test' AND a2.BooleanValue = 0       
WHERE
  Name = 'Windows OS Version'
  AND StringValue IS NOT NULL
  AND COALESCE(a2.ProductAssociation, NULLIF(@IsTestsIncluded, 1)) IS NOT NULL
GROUP BY a.StringValue

coalesce/nullif Комбинация - это не самая легкая для понимания вещь, которую я когда-либо писал, но она должна быть функционально эквивалентна той, что у вас есть, если условия соединения соответствуют 0 или 1 записи в объединенной таблице.

Answer 2

Зачем? Что я делаю неправильно?

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

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

Длинное обсуждение темы с альтернативами, плюсами и минусами: условия динамического поиска в T‑SQL

Answer 3

Хороший ответ от Джоэла +1

ИЛИ трудно оптимизировать

Возвращаясь ко второму
Где оптимизатору сложно оптимизировать
Рассмотреть возможность присоединиться ко всем тем, где в
Это все еще имеет ИЛИ, которое может привести к неправильному плану запроса, но дает оптимизатору больше шансов минимизировать ИЛИ

SELECT A1.StringValue, COUNT(A1.StringValue)
 FROM Attributes A1
 JOIN ProductAssociations PA
   ON PA.ID = A1.ProductAssociation
  AND A1.Name = 'Windows OS Version'
  AND A1.StringValue IS NOT NULL
  AND PA.ProductCode = 'MyProductCode'
 JOIN Attributes A2 
   ON A2.ProductAssociation = A1.ProductAssociation 
  AND (     @IsTestsIncluded = 1
        OR (A2.Name = 'Is test' AND A2.BooleanValue = 0)
      )
GROUP BY A1.StringValue  

если вы реорганизуете @IsTestsIncluded, вы можете сделать это

SELECT A1.StringValue, COUNT(A1.StringValue)
 FROM Attributes A1
 JOIN ProductAssociations PA
   ON PA.ID = A1.ProductAssociation
  AND A1.Name = 'Windows OS Version'
  AND A1.StringValue IS NOT NULL
  AND PA.ProductCode = 'MyProductCode'
 LEFT JOIN Attributes A2 
   ON A2.ProductAssociation = A1.ProductAssociation 
  AND A2.Name = 'Is test' 
  AND A2.BooleanValue = 0
WHERE ISNULL(@IsTestsIncluded, A2.ProductAssociation) is NOT NULL
GROUP BY A1.StringValue