задержка выполнения sql при назначении переменной

Следующий запрос будет выполнен примерно через 22 секунды:

DECLARE @i INT, @x INT
SET    @i = 156567

SELECT 
TOP 1
    @x = AncestorId
FROM 
    dbo.tvw_AllProjectStructureParents_ChildView a
WHERE 
    ProjectStructureId = @i AND
        a.NodeTypeCode = 42 AND
        a.AncestorTypeDiffLevel = 1
OPTION (RECOMPILE)

Проблема связана с назначением переменной (действительно эта строка: @x = AncestorId). при снятии задания ускоряется примерно до 15 миллисекунд! Я решил это, вставив результат во временную таблицу, но я думаю, что это плохой способ.

Может ли кто-нибудь помочь мне, что является источником проблемы?!

P.S.

плохой план выполнения (22s): https://www.brentozar.com/pastetheplan/?id=Sy6a4c9bW

хороший план выполнения (20мс): https://www.brentozar.com/pastetheplan/?id=Byg8Hc5ZZ


sql sql-server parameter-sniffing
person Mahmoud Moravej    schedule 30.05.2017    source источник
comment
Это довольно интересно. Назначение переменной, по-видимому, отключает оптимизацию внедрения параметров, которую вы получаете с помощью OPTION (RECOMPILE). Вы можете изменить dbo.tvw_AllProjectStructureParents_ChildView на встроенный TVF и передать параметр, чтобы вообще не использовать OPTION (RECOMPILE).   -  person Martin Smith    schedule 30.05.2017
comment
Итак, можем ли мы сказать, что это ошибка SQL Server?   -  person Mahmoud Moravej    schedule 30.05.2017
comment
Является ли значение @i одинаковым в обоих случаях? Сколько раз вы проверяли это поведение? Я думаю, у вас проблемы с вашими индексами. Иногда оптимизатор запросов использует плохие планы, и это может быть совпадением   -  person Mikhail Lobanov    schedule 30.05.2017
comment
пожалуйста, предоставьте определение представления и таблиц (с индексами)   -  person Mikhail Lobanov    schedule 30.05.2017

Ответы (2)


arrow_upward
4
arrow_downward

При использовании OPTION (RECOMPILE) SQL Server обычно может выполнять оптимизация встраивания параметров.

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

Тривиальный пример, показывающий оптимизацию внедрения параметров в действии и эффект присваивания переменной, приведен ниже (фактические планы выполнения не оцениваются).

DECLARE @A INT = 1, 
        @B INT = 2,
        @C INT;

SELECT TOP (1) number FROM master..spt_values WHERE @A > @B;
SELECT TOP (1) number FROM master..spt_values WHERE @A > @B OPTION (RECOMPILE);
SELECT TOP (1) @C = number FROM master..spt_values WHERE @A > @B OPTION (RECOMPILE);

Планы на это ниже

введите здесь описание изображения

Обратите внимание, что средний даже не касается таблицы, поскольку SQL Server может сделать вывод во время компиляции, что @A > @B не является true. Но план 3 вернулся к включению таблицы в план, поскольку назначение переменной, очевидно, предотвращает эффект OPTION (RECOMPILE), показанный в плане 2.

(Кроме того, третий план на самом деле не в 4-5 раз дороже, чем первый. Назначение переменной также, похоже, подавляет обычную логику цели строки, когда затраты на сканирование индекса будут уменьшены, чтобы отразить TOP 1)

В вашем хорошем плане @i значение 156567 вставляется прямо в поиск в опорной части рекурсивного CTE, он возвращает 0 строк, поэтому рекурсивная часть не должна выполнять никакой работы.

введите здесь описание изображения

В вашем плохом плане рекурсивный CTE полностью материализуется с 627 393 выполнениями рекурсивного поддерева, и, наконец, предикат применяется к результирующим 627 393 строкам (отбрасывая их все) в конце

введите здесь описание изображения

Я не уверен, почему SQL Server не может сбросить предикат с переменной. Вы не предоставили определения своих таблиц или представление с рекурсивным CTE. Существует аналогичная проблема с предикаты push, view и оконные функции.

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

person Martin Smith    schedule 30.05.2017

arrow_upward
-2
arrow_downward

Разница, вероятно, связана с SELECT TOP 1.

Когда у вас есть только поле, SQL Server возьмет только первую строку. Когда у вас есть назначение переменной, SQL Server извлекает все результаты, но использует только верхний.

Я проверял разные запросы, и это не всегда так, но, вероятно, здесь оптимизация SQL Server не работает из-за сложности представлений/таблиц.

Вы можете попробовать следующий обходной путь:

DECLARE @i INT, @x INT
SET    @i = 156567

SET @x = (SELECT 
TOP 1
    AncestorId
FROM 
    dbo.tvw_AllProjectStructureParents_ChildView a
WHERE 
    ProjectStructureId = @i AND
        a.NodeTypeCode = 42 AND
        a.AncestorTypeDiffLevel = 1)
person smartobelix    schedule 30.05.2017