Почему вставка и соединение таблиц #temp быстрее?

У меня есть запрос, который выглядит как

SELECT
 P.Column1,
 P.Column2,
 P.Column3,
 ...
 (
   SELECT
       A.ColumnX,
       A.ColumnY,
       ...
   FROM
      dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A
   WHERE
      A.Key = P.Key
   FOR XML AUTO, TYPE  
 ),
 (
   SELECT
       B.ColumnX,
       B.ColumnY,
       ...
   FROM
      dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B
   WHERE
      B.Key = P.Key
   FOR XML AUTO, TYPE  
 )
FROM
(
   <joined tables here>
) AS P
FOR XML AUTO,ROOT('ROOT')

P имеет ~ 5000 строк A и B ~ 4000 строк

Этот запрос имеет производительность выполнения ~ 10 + минут.

Изменить это на это:

SELECT
 P.Column1,
 P.Column2,
 P.Column3,
 ...
INTO #P

SELECT
 A.ColumnX,
 A.ColumnY,
 ...
INTO #A     
FROM
 dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A

SELECT
 B.ColumnX,
 B.ColumnY,
 ...
INTO #B     
FROM
 dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B


SELECT
 P.Column1,
 P.Column2,
 P.Column3,
 ...
 (
   SELECT
       A.ColumnX,
       A.ColumnY,
       ...
   FROM
      #A AS A
   WHERE
      A.Key = P.Key
   FOR XML AUTO, TYPE  
 ),
 (
   SELECT
       B.ColumnX,
       B.ColumnY,
       ...
   FROM
      #B AS B
   WHERE
      B.Key = P.Key
   FOR XML AUTO, TYPE  
 )
FROM #P AS P
FOR XML AUTO,ROOT('ROOT')

имеет производительность ~ 4 секунды.

Это не имеет большого смысла, так как это может стоить вставить в временную таблицу, а затем сделать соединение по умолчанию выше. Моя склонность заключается в том, что SQL делает неправильный тип "join" с подзапросом, но, возможно, я пропустил его, нет способа указать тип соединения для использования с коррелированными подзапросами.

Есть ли способ достичь этого без использования #temp tables/@переменных таблицы с помощью индексов и/или подсказок?

EDIT: Обратите внимание, что dbo.TableReturningFunc1 и dbo.TableReturningFunc2 являются встроенными TVF, а не многозадачными, или они являются "параметризованными" инструкциями вида.

Ответ 1

Ваши процедуры переоцениваются для каждой строки в P.

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

Вставка в временную таблицу выполняется быстро, поскольку она не генерирует redo/rollback.

Соединения также бывают быстрыми, поскольку наличие стабильного набора результатов позволяет создавать временный индекс с помощью Eager Spool или Worktable

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

Вы можете попытаться заставить его сделать это с помощью ORDER BY внутри подзапроса:

WITH    f1 AS
        (
        SELECT  TOP 1000000000
                A.ColumnX,
                A.ColumnY
        FROM    dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A
        ORDER BY
                A.key
        ),
        f2 AS
        (
        SELECT  TOP 1000000000
                B.ColumnX,
                B.ColumnY,
        FROM    dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B  
        ORDER BY
                B.Key
        )
SELECT  …

что может привести к Eager Spool, генерируемому оптимизатором.

Однако это далеко не гарантировано.

Гарантированный способ заключается в добавлении OPTION (USE PLAN) к вашему запросу и завершении соответствия CTE в предложение Spool.

Посмотрите эту запись в своем блоге о том, как это сделать:

Генерация XML в подзапросах

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

Использование временных таблиц будет намного проще.

Ответ 2

Этот ответ должен быть прочитан вместе с Quassnoi article
http://explainextended.com/2009/05/28/generating-xml-in-subqueries/

При разумном применении CROSS APPLY вы можете принудительно выполнить кэширование или краткую оценку встроенных TVF. Этот запрос возвращается мгновенно.

SELECT  *
FROM    (
        SELECT  (
                SELECT  f.num
                FOR XML PATH('fo'), ELEMENTS ABSENT
                ) AS x
        FROM    [20090528_tvf].t_integer i
        cross apply (
            select num
            from [20090528_tvf].fn_num(9990) f
            where f.num = i.num
            ) f
) q
--WHERE   x IS NOT NULL -- covered by using CROSS apply
FOR XML AUTO

Вы не представили реальные структуры, поэтому сложно создать что-то значимое, но техника должна также применяться.

Если вы измените многозадачность TVF в статье Quassnoi на встроенный TVF, план станет еще быстрее (по крайней мере, на один порядок), и план магически сводится к тому, что я не могу понять (это слишком просто!).

CREATE FUNCTION [20090528_tvf].fn_num(@maxval INT)  
RETURNS TABLE
AS RETURN 
        SELECT  num + @maxval   num
        FROM    t_integer

Статистика

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(10 row(s) affected)
Table 't_integer'. Scan count 2, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

Ответ 3

Это проблема с вашим подзапросом, ссылающимся на ваш внешний запрос, то есть запрос должен быть скомпилирован и выполнен для каждой строки во внешнем запросе. Вместо использования явных временных таблиц вы можете использовать производную таблицу. Чтобы упростить свой пример:

SELECT P.Column1,
       (SELECT [your XML transformation etc] FROM A where A.ID = P.ID) AS A

Если P содержит 10 000 записей, тогда SELECT A.ColumnX FROM A, где A.ID = P.ID будет исполнено 10 000 раз.
Вместо этого вы можете использовать производную таблицу следующим образом:

SELECT P.Column1, A2.Column FROM  
P LEFT JOIN 
 (SELECT A.ID, [your XML transformation etc] FROM A) AS A2 
 ON P.ID = A2.ID

Хорошо, не этот иллюстративный псевдокод, но основная идея такая же, как и временная таблица, за исключением того, что SQL Server делает все в памяти: сначала выбирает все данные в "A2" и строит временную таблицу в памяти, затем присоединяется к нему. Это избавит вас от необходимости выбирать его для TEMP.

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

Плохо: (выполняется столько запросов, сколько есть сотрудников в БД)

SELECT EmpName, 
 (SELECT SUM(absdays) FROM Absence where Absence.PerID = Employee.PerID) AS Abstotal        
FROM Employee

Хорошо: (выполняется только два запроса)

SELECT EmpName, AbsSummary.Abstotal
FROM Employee LEFT JOIN
      (SELECT PerID, SUM(absdays) As Abstotal 
       FROM Absence GROUP BY PerID) AS AbsSummary
ON AbsSummary.PerID = Employee.PerID

Ответ 4

Существует несколько возможных причин, по которым использование промежуточных таблиц Temp может ускорить запрос, но наиболее вероятным в вашем случае является то, что функции, которые вызываются (но не указаны), вероятно, являются многопозиционными TVF, а не -line TVF. Multi-statement TVF непрозрачны для оптимизации своих вызывающих запросов, и, таким образом, оптимизатор не может определить, есть ли какие-либо возможности для повторного использования данных или другие оптимизации логического/физического переопределения операторов. Таким образом, все, что он может сделать, состоит в том, чтобы повторно запускать TVF каждый раз, когда содержательный запрос должен создавать другую строку с столбцами XML.

Короче говоря, многозадачность TVF расстраивает оптимизатор.

Обычными решениями в порядке (типичных) предпочтений являются:

Повторно напишите оскорбительный мультирежим TVF как встроенный TVF
Ввести код функции в вызывающий запрос или
Сбросьте оскорбительные данные TVF в таблицу temp. это то, что вы сделали...

Ответ 5

Рассмотрите возможность использования конструкции WITH common_table_expression для того, что у вас есть в качестве подвыборов или временных таблиц, см. http://msdn.microsoft.com/en-us/library/ms175972(SQL.90).aspx.

Ответ 6

Это не имеет большого смысла, поскольку казалось бы, затраты на temp, а затем выполните соединение быть выше по de > Это не имеет большого смысла, поскольку это казалось бы, затраты на temp, а затем выполните соединение выше по умолчанию.

С временными таблицами вы явно проинструктируете Sql Server о том, какое промежуточное хранилище использовать. Но если вы вложите все в большой запрос, Sql Server решит сам. Разница на самом деле не такая большая; в конце дня используется временное хранилище, независимо от того, указали ли вы его как временную таблицу или нет.

В вашем случае временные таблицы работают быстрее, поэтому почему бы не придерживаться их?

Ответ 7

Я согласился, таблица Temp - хорошая концепция. Когда число строк увеличивается в таблице, пример 40 миллионов строк, и я хочу обновить несколько столбцов в таблице, применяя объединения с другой таблицей, в этом случае я всегда предпочитаю использовать выражение Common table для обновления столбцов в выражении select, используя case, теперь мой набор результатов для набора предложений содержит обновленные строки. Включение 40 миллионов записей в таблицу temp с помощью оператора select с использованием case case заняло у меня 21 минуту, а затем создание индекса заняло 10 минут, так что время создания и создания индекса заняло 30 минут, Затем я собираюсь применить обновление, присоединив обновленную таблицу результатов temp table с основной таблицей. Потребовалось 5 минут, чтобы обновить 10 миллионов записей из 40 миллионов записей, поэтому мое общее время обновления для 10 миллионов записей заняло почти 35 минут против 5 минут от выражения Common table. Мой выбор в этом случае является общим табличным выражением.

Ответ 8

Если временные таблицы оказываются более быстрыми в вашем конкретном экземпляре, вместо этого вы должны использовать переменную таблицы.

Здесь есть хорошая статья о различиях и последствиях:

http://www.codeproject.com/KB/database/SQP_performance.aspx