Запрос SQL Server: быстрый с литеральным, но медленным с переменной

У меня есть представление, которое возвращает 2 ints из таблицы с использованием CTE. Если я запрашиваю такой вид, он работает менее чем за секунду

SELECT * FROM view1 WHERE ID = 1

Однако, если я запрашиваю такой вид, он занимает 4 секунды.

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id

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

Есть ли что-то очевидное, что мне не хватает, чтобы попытаться заставить второй запрос использовать Index Seek, а не индексное сканирование. Я использую SQL 2008, но все, что мне нужно, также нужно запускать на SQL 2005. Сначала я подумал, что это какая-то проблема с параметром sniffing, но я получаю те же результаты, даже если я очищаю кеш.

Ответ 1

Вероятно, это связано с тем, что в случае параметра оптимизатор не может знать, что значение не является нулевым, поэтому ему необходимо создать план, который возвращает правильные результаты, даже если это так. Если у вас есть SQL Server 2008 SP1, вы можете попробовать добавить OPTION(RECOMPILE) к запросу.

Ответ 2

Вы можете добавить опцию OPTIMIZE FOR к вашему запросу, например

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id OPTION (OPTIMIZE FOR (@ID = 1))

Ответ 3

В моем случае в таблице столбцов таблицы был определен как VarChar, а в параметризованном типе параметра запроса был определен как NVarChar, это ввело CONVERT_IMPLICIT в фактический план выполнения, чтобы сопоставить тип данных перед сравнением и это было виновником производительности свиноматки, 2 сек. Против 11 сек. Просто корректирующий тип параметра сделал параметризованный запрос так же быстро, как не параметризованная версия.

Надеюсь, это может помочь кому-то с подобной проблемой.

Ответ 4

Когда SQL начинает оптимизировать план запроса для запроса с переменной, он будет соответствовать доступному индексу для столбца. В этом случае был индекс, поэтому SQL вычислил, что он просто сканирует индекс, ищущий значение. Когда SQL сделал план запроса с столбцом и литеральным значением, он мог посмотреть статистику и значение, чтобы решить, следует ли сканировать индекс или если поиск будет правильным.

Используя подсказку optimize и значение, SQL указывает, что "это значение, которое будет использоваться большую часть времени, чтобы оптимизировать это значение", и план хранится так, как если бы это литеральное значение использовалось. Использование подсказки optimize и поднабора UNKNOWN указывает SQL, что вы не знаете, какое значение будет иметь значение, поэтому SQL ищет статистику для столбца и решает, что искать, искать или сканировать, будет лучше и делает план соответствующим образом.

Ответ 5

Произошла одна и та же проблема сама, и она оказалась отсутствующим индексом, включающим (левое) соединение в результате подзапроса.

select *
from foo A
left outer join (
  select x, count(*)
  from bar
  group by x
) B on A.x = B.x

Добавлен индекс с именем bar_x для bar.x