Необычная медлительность представления SQL Server

Кто-нибудь может понять поведение SQL Server здесь? Мне кажется, что это похоже на ошибку / сбой в оптимизаторе запросов.

Нет, это не ошибка.

Задача разделена на несколько более мелких блоков:

Подход с использованием временных таблиц - не более чем разделение этого большого плана запросов на более мелкие части и выполнение их независимо.

Чем меньше фрагмент, тем больше вероятность того, что SQL Server Query Optimizer не выполнит какое-либо существенное несоответствие в оценке мощности и выберет правильные физические операторы и типы объединений, так что меньше вероятность увидеть вложенный цикл на миллионах строк или какая-то другая неприятная вещь.

Когда наступает время выполнения фрагмента кода, указанного ниже, оптимизатор запросов знает, сколько строк в каждой задействованной временной таблице и как они распределены:

select p.pklDocMaxPlace
from #picklistDocs p
join #picklistDocLines p1 on p.pklDocId = p1.pklDocId
join #orderLines r1 on r1.rdrDocId = p1.rdrDocId
                   and r1.rdrLineId = p1.rdrLineId

Одна единица работы :

Подход CTE, как было упомянуто Лукашем и Робертом в комментариях, является своего рода синтаксическим сахаром, похожим на представление «представление на представлении». Однако, в конце концов, оптимизатор запросов должен объединить все CTE в один консолидированный и иногда большой план запросов и выполнить его как один блок . Поэтому, чем больше план, тем больше шансов на сюрпризы, связанные с производительностью.

Таким образом, в отличие от предыдущего фрагмента, оптимизатор запросов компилирует план в тот момент, когда количество строк только угадывается с помощью оценки мощности с использованием статистики:

select p.pklDocMaxPlace
from picklistDocs p
join picklistDocLines p1 on p.pklDocId = p1.pklDocId
join orderLines r1 on r1.rdrDocId = p1.rdrDocId
                  and r1.rdrLineId = p1.rdrLineId

querytraceon 8649:

Когда вы включаете option(querytraceon 8649), вы просто заставляете оптимизатор запросов изменять поведение, так же, как другие подсказки запроса или подобные трассировки, подобные 4199. Таким образом, принудительный параллелизм, возможно, иногда генерирует лучший план, но на это вряд ли можно положиться.

Некоторые идеи о том, как это можно решить:

• Обновление статистики по задействованным таблицам
• Игра с переключением новых и устаревших оценок кардинальности [114 ]
• (imho) Переписать CTE в производную таблицу?
• Если задействованы большие наборы данных, то разбиение логики на более мелкие части с использованием подхода #temp table - это то, что может быть последовательным обходным решением для выбора.
• и т. Д.

Есть одно исключение:

• Индексированные представления. С помощью подсказки NOEXPAND (или если используется Enterprise Edition). Логика представления не должна быть сведена в общий план запроса, включающего его.