Переместить константу в переменную в предложении where радикально изменить план выполнения?

Question

Я создал индекс для сложного представления. Выполнение следующего запроса в Sql Server Management Studio занимает от 0 до нескольких секунд. И план запроса показывает, что 99% затрат находится в поиске индекса по индексу, который я создал для главной большой таблицы. (С общей стоимостью поддерева 7,5)

select * from ComplexView where id = 10000 and theDate = '1/1/2018'

Тем не менее, следующий запрос

declare @id int = 10000, @theDate datetime = '1/1/2018'
select * from ComplexView where id = @id and theDate = @theDate

занимает много времени (от 3 до 10 минут), захватывает план запроса показывает, что 57% затрат приходится на сканирование таблицы на главной большой таблице (плюс фильтр 10% на нее и совпадение хэша на 14% и сортировка на 17%, с общая стоимость поддерева 260).

Answer 1

Первый запрос будет иметь план запроса, скомпилированный на основе значений ваших литералов 10000 а также '1/1/2018' и статистику гистограммы на задействованных таблицах.

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

Это известное и ожидаемое поведение (хотя может быть удивительным, если вы не знаете об этом).

Вы включили показ фактического плана выполнения для обоих запросов и обнаружили, что у них разные планы.

Если вам случится сначала выполнить запрос с "типичными" значениями (на основе распределений задействованных столбцов), вы получите кэшированный план, который работает для большинства значений ваших фильтров.

Если ваша статистика актуальна, попробуйте это:

declare @id int = 10000, @theDate datetime = '1/1/2018'
select * from ComplexView 
where id = @id and theDate = @theDate
option(recompile)

У вас есть более разумный план?

С помощью option(recompile) это один из тех ответов "это зависит"! Если запрос является отчетным запросом (я имею в виду, что он относительно длительный и не часто выполняется), подумайте о его добавлении. Он говорит SQL Server не кэшировать план и перекомпилировать новый план при каждом выполнении. Недостатком является то, что вы не увидите план в кэше, и вы платите (относительно небольшую по сравнению с длительным запросом) стоимость компиляции плана при каждом выполнении.

Если это важный отчетный запрос, я бы добавил option(recompile) чтобы вы никогда не получили неподходящий план запроса.