Почему параметризованный запрос производит значительно более медленный план запроса против непараметрированного запроса

Ответ 1

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

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

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

В частности, в вашем запросе попробуйте следующее:

declare @p0 int
set @p0 = 1000
select *
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = @p0
OPTION ( OPTIMIZE FOR (@p0 = 1000))

Но я был бы осторожен в этом, не будучи уверенным, что данные, содержащиеся в этом запросе, не изменятся и ваш запрос по этому плану ВСЕГДА будет более эффективным.

Ответ 2

Я думаю, что вы работаете с " параметр sniffing". В основном, это означает, что SQL Server пытается использовать столько информации, сколько нужно для вычисления оптимального плана выполнения для вашего запроса. В случае вашего первого запроса у вас есть постоянное значение, которое известно во время компиляции, поэтому движок может напрямую оптимизировать план запроса для этого значения.

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

Один из способов, которым вы можете попытаться обойти это, - это обернуть запрос в хранимой процедуре, которая принимает параметр напрямую, а затем применит его к запросу - что-то вроде этого:

CREATE PROCEDURE test
  @p0 int
AS
BEGIN
  select *
  from foo
  join bar on bar.x = foo.x
  join baz on baz.y = foo.y
  where foo.x = @p0
END

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

Ответ 3

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

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

Ответ 4

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

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