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

Question

Почему вставляет в и присоединяется к #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 имеет ~ 5 000 строк A и B ~ 4 000 строк каждый

Этот запрос имеет производительность во время выполнения ~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 делает неправильный тип "соединения" с подзапросом, но возможно я пропустил его, нет никакого способа указать тип соединения для использования со связанными подзапросами.

Существует ли способ достигнуть этого, не используя #temp таблицы / табличные переменные через индексы и/или подсказки?

Править: Отметьте это dbo. TableReturningFunc1 и dbo. TableReturningFunc2 является встроенный TVF's, не составной, или они "параметризованы" операторы представления.

12

sql sql-server sql-server-2005 tsql

задан Joseph Kingry 29 May 2009 в 21:08

6 ответов

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

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

2