Почему запрос резко замедляется, если в предложении WHERE константа заменяется параметром (с тем же значением)?

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

ЗапроС# 1 - с константой

;WITH Hierarchy (Id, ParentId, Data, Depth)
AS
( SELECT Id, ParentId, NULL AS Data, 0 AS Depth
  FROM Test
  UNION ALL
  SELECT h.Id, t.ParentId, COALESCE(h.Data, t.Data), Depth + 1 AS Depth
  FROM Hierarchy h
       INNER JOIN Test t ON t.Id = h.ParentId
)
SELECT *
FROM Hierarchy
WHERE Id = 69

Запрос № 2 - с параметром

DECLARE @Id INT
SELECT @Id = 69

;WITH Hierarchy (Id, ParentId, Data, Depth)
AS
( SELECT Id, ParentId, NULL AS Data, 0 AS Depth
  FROM Test
  UNION ALL
  SELECT h.Id, t.ParentId, COALESCE(h.Data, t.Data), Depth + 1 AS Depth
  FROM Hierarchy h
       INNER JOIN Test t ON t.Id = h.ParentId
)
SELECT *
FROM Hierarchy
WHERE Id = @Id

В случае таблицы с 50 000 строк запрос с константой работает в течение 10 миллисекунд, а один с параметром работает в течение 30 секунд (в 3 000 раз медленнее).

Невозможно переместить последнее предложение WHERE в определение привязки рекурсии, так как я хотел бы использовать запрос для создания представления (без последнего WHERE). Выбор из представления имел бы предложение WHERE (WHERE Id = @Id) - мне нужно это из-за Entity Framework, но это еще одна история.

Может ли кто-нибудь предложить способ заставить запрос №2 (с параметром) использовать тот же план запроса, что и запрос №1 (с константой)?

Я уже пробовал играть с индексами, но это не помогло.

Если кому-то захочется, я могу опубликовать определение таблицы и некоторые примеры данных. Я использую SQL 2008 R2.

Спасибо за вашу помощь заранее!

План выполнения - Запрос №1 - с константой

План выполнения - Запрос № 2 - с параметром

Ответ 1

Как предложил Мартин в комментарии к этому вопросу, проблема в том, что SQL-сервер не преувеличивает предикат из предложения WHERE - см. ссылку в своем комментарии.

В итоге я создал пользовательскую функцию, ориентированную на таблицу, и использовал ее с помощью оператора CROSS APPLY для создания представления.

Посмотрим на само решение.

Определенная пользователем табличная функция

CREATE FUNCTION [dbo].[TestFunction] (@Id INT)
RETURNS TABLE 
AS
RETURN 
(
    WITH
    Hierarchy (Id,  ParentId, Data, Depth)
    AS(
    SELECT Id, ParentId, NULL AS Data, 0 AS Depth FROM Test Where Id = @Id
    UNION ALL
    SELECT h.Id, t.ParentId, COALESCE(h.Data, t.Data), Depth + 1 AS Depth
        FROM Hierarchy h
            INNER JOIN Test t ON t.Id = h.ParentId
    )
    SELECT * FROM Hierarchy
)

Просмотр

CREATE VIEW [dbo].[TestView]
AS
SELECT t.Id, t.ParentId, f.Data, f.Depth
FROM
    Test AS t
    CROSS APPLY TestFunction(Id) as f

Запрос с константой

SELECT * FROM TestView WHERE Id = 69

Запрос с параметром

DECLARE @Id INT
SELECT @Id = 69
SELECT * FROM TestView WHERE Id = @Id

Запрос с parmater выполняется в основном так же быстро, как запрос с константой.

Спасибо Мартин и всем остальным!

Ответ 2

Для второго запроса попробуйте использовать опцию OPTIMIZE FOR или OPTION (RECOMPILE) подсказку, чтобы убедиться, что это заставляет ее перекомпилировать на основе предоставленного значение параметра.

Ответ 3

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

Ответ 4

Это может быть худшее предложение, но подумали ли вы о создании sproc для создания своего запроса в виде строки и выполнить его с помощью sp_executesql?

Я ничего не знаю о кэшировании поведения SQL, выполняемого sp_executesql, это было первое, что появилось в моей голове.