Выполнение SQL-запроса с параметрами (перекомпиляция) всегда быстрее

Question

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

DECLARE @p9 nvarchar(4000)
SET @p9=N'Alex%'

SELECT ContactId as CandidateId
FROM Candidate
            WHERE
                (
                    @p9 IS NULL
                    OR
                    Forename LIKE @p9
                    OR 
                    PreferredName LIKE @p9
                    OR
                    Surname LIKE @p9
                )

Когда работает без option(recompile) (независимо от того, сколько раз я запускаю его)

(1166 row(s) affected)
Table 'Candidate'. Scan count 5, logical reads 9762, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Тот же запрос, но option(recompile) добавлено в конце

(1166 row(s) affected)
Table 'Candidate'. Scan count 3, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Если я НЕ использую переменные в запросе, а вместо них константы, тогда все в порядке. Я предполагаю, что это связано с анализом параметров, но вопрос в том, ПОЧЕМУ? Также каковы были бы недостатки ухода option(recompile) для производственного кода?

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

Обновление: работает exec sp_updatestats не имеет никакого эффекта и выберите с помощью option(recompile) по-прежнему выполняет раз лучше.

Спасибо за ваше время.

Answer 1

Option(recompile) генерирует новый план на основе текущей статистики за счет составления нового плана. Если ваша статистика актуальна, то в 99 раз из 100 вы получите оптимальный план (а не план с кэшированным запросом, который может не подходить для определенного набора параметров, который вы передали, по сравнению с тем, когда план кэшировался. Это называется параметром сниффинг)

"Кроме того, каковы недостатки использования опции (перекомпиляции) для производственного кода?"

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

Также имейте ввиду, что используя Option(recompile) имеет недостаток, что процедура не будет отображаться в соответствующих DMV.