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

Я повторяю вопрос, заданный Mongus Pong. Почему временная таблица будет быстрее, чем вложенный запрос? у которого нет ответа, который работает для меня.

Большинство из нас в какой-то момент обнаруживают, что, когда вложенный запрос достигает определенной сложности, он должен разбиваться на временные таблицы, чтобы поддерживать его производительность. Абсурдно, что это когда-либо может быть наиболее практичным способом продвижения вперед, и это означает, что эти процессы больше не могут быть видны. И часто сторонние BI-приложения будут хорошо работать только с представлениями, поэтому это очень важно.

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

Я часто обнаруживаю, что простой переход от подзапроса к таблице # занимает от 120 секунд до 5. По сути, оптимизатор где-то допускает серьезную ошибку. Конечно, могут быть очень трудоемкие способы, которыми я мог бы уговорить оптимизатора смотреть на таблицы в правильном порядке, но даже это не дает никаких гарантий. Я не спрашиваю об идеальном времени выполнения 2 секунды здесь, просто скорость, которую временные табуляции предлагают мне в пределах гибкости представления.

Я никогда не писал здесь раньше, но я писал SQL годами и читал комментарии других опытных людей, которые также просто пришли, чтобы принять эту проблему, и теперь я просто хотел бы, чтобы соответствующий гений сделал шаг вперед и сказал: Особый намек есть X...

Ответ 1

Есть несколько возможных объяснений того, почему вы видите это поведение. Некоторые общие

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

Самый надежный метод - просто использовать таблицу #temp и материализовать ее самостоятельно.

В противном случае, что касается пункта 1, см. Предоставить подсказку для принудительной промежуточной материализации CTE или производных таблиц. Использование TOP(large_number)... ORDER BY может часто способствовать тому, что результат будет TOP(large_number)... ORDER BY а не подвергнут повторной оценке.

Даже если это работает, однако нет никакой статистики по катушке.

Для пунктов 2 и 3 вам необходимо проанализировать, почему вы не получили желаемый план. Возможно, переписав запрос для использования предикатов sargable или обновив статистику, можно получить лучший план. В противном случае вы можете попытаться использовать подсказки запроса, чтобы получить желаемый план.

Ответ 2

Я не верю, что есть подсказка запроса, которая инструктирует движок обманывать каждый подзапрос по очереди.

Существует подсказка запроса OPTION (FORCE ORDER), которая заставляет двигатель выполнять JOINs в указанном порядке, что потенциально может привести его к достижению этого результата в некоторых случаях. Этот намек иногда приводит к более эффективному планированию сложного запроса, и двигатель продолжает настаивать на субоптимальном плане. Конечно, оптимизатору обычно следует доверять, чтобы определить лучший план.

В идеале будет намек на запрос, который позволит вам назначить CTE или подзапрос как "материализованную" или "анонимную временную таблицу", но этого не существует.

Ответ 3

Другим вариантом (для будущих читателей этой статьи) является использование пользовательской функции. Многозадачные функции (как описано в Как делиться данными между хранимыми процедурами) заставляют SQL Server материализовать результаты вашего подзапроса. Кроме того, они позволяют указать первичные ключи и индексы в результирующей таблице, чтобы помочь оптимизатору запросов. Затем эту функцию можно использовать в инструкции select как часть вашего представления. Например:

CREATE FUNCTION SalesByStore (@storeid varchar(30))
   RETURNS @t TABLE (title varchar(80) NOT NULL PRIMARY KEY,
                     qty   smallint    NOT NULL)  AS
BEGIN
   INSERT @t (title, qty)
      SELECT t.title, s.qty
      FROM   sales s
      JOIN   titles t ON t.title_id = s.title_id
      WHERE  s.stor_id = @storeid
   RETURN
END

CREATE VIEW SalesData As
SELECT * FROM SalesByStore('6380')

Ответ 4

Запуская эту проблему, я обнаружил, что (в моем случае) SQL Server оценивал условия в некотором порядке, потому что у меня был индекс, который можно было бы использовать (IDX_CreatedOn on TableFoo).

SELECT bar.*
FROM
    (SELECT * FROM TableFoo WHERE Deleted = 1) foo
    JOIN TableBar bar ON (bar.FooId = foo.Id)
WHERE
foo.CreatedOn > DATEADD(DAY, -7, GETUTCDATE())

Мне удалось обойти это, заставив подзапрос использовать другой индекс (т.е. тот, который будет использоваться, когда подзапрос был выполнен без родительского запроса). В моем случае я переключился на PK, что было бессмысленным для запроса, но сначала разрешило условия из подзапроса.

SELECT bar.*
FROM
    (SELECT * FROM TableFoo WITH (INDEX([PK_Id]) WHERE Deleted = 1) foo
    JOIN TableBar bar ON (bar.FooId = foo.Id)
WHERE
foo.CreatedOn > DATEADD(DAY, -7, GETUTCDATE())

Фильтрация по столбцу Deleted была действительно простой, и фильтрация нескольких результатов на CreatedOn впоследствии была еще проще. Я смог понять это, сравнив Actual Execution Plan подзапроса и родительский запрос.

Более хакерское решение (и не рекомендуется) - заставить подзапрос выполнить сначала, ограничив результаты с помощью TOP, однако это может привести к возникновению странных проблем в будущем, если результаты подзапроса превысят предел (вы всегда можете установить предел на что-то смешное). К сожалению, TOP 100 PERCENT не может использоваться для этой цели, поскольку SQL Server просто игнорирует его.