Почему вставить - выбрать переменную таблицу из переменной XML так медленно?

Я пытаюсь вставить некоторые данные из XML-документа в таблицу переменных. Что мой ум в том, что один и тот же select-in (bulk) запускается в кратчайшие сроки, в то время как вставка-выбор занимает много времени и поддерживает процесс SQL-сервера для 100% использования процессора во время выполнения запроса.

Я взглянул на план выполнения, а INDEED - разница. Вставка-выбор добавляет дополнительную "Табличную катушку" node, хотя она не назначает стоимость. "Значения таблицы [XML Reader]" затем получают 92%. С помощью select-in две "Table Value Function [XML Reader]" получают по 49%.

Пожалуйста, объясните: "ПОЧЕМУ это происходит" и "КАК разрешить это (изящно)", поскольку я могу действительно вставлять во временную таблицу, а затем, в свою очередь, вставлять в таблицу переменных, но это просто жутко.

Я пробовал это на SQL 10.50.1600, 10.00.2531 с теми же результатами

Здесь тестовый пример:

declare @xColumns xml
declare @columns table(name nvarchar(300))

if OBJECT_ID('tempdb.dbo.#columns') is not null drop table #columns

insert @columns select name from sys.all_columns

set @xColumns = (select name from @columns for xml path('columns'))

delete @columns

print 'XML data size: ' + cast(datalength(@xColumns) as varchar(30))

--raiserror('selecting', 10, 1) with nowait

--select ColumnNames.value('.', 'nvarchar(300)') name
--from @xColumns.nodes('/columns/name') T1(ColumnNames)

raiserror('selecting into #columns', 10, 1) with nowait

select ColumnNames.value('.', 'nvarchar(300)') name
into #columns
from @xColumns.nodes('/columns/name') T1(ColumnNames)

raiserror('inserting @columns', 10, 1) with nowait

insert @columns
select ColumnNames.value('.', 'nvarchar(300)') name
from @xColumns.nodes('/columns/name') T1(ColumnNames)

Спасибо, куча!!

Ответ 1

Это ошибка в SQL Server 2008. Используйте

insert @columns 
select ColumnNames.value('.', 'nvarchar(300)') name
from @xColumns.nodes('/columns/name') T1(ColumnNames)
OPTION (OPTIMIZE FOR ( @xColumns = NULL ))

Это обходное решение относится к элементу Microsoft Connect Site, в котором также упоминается исправление для этой проблемы с Eager Spool/XML Reader traceflag 4130).

Причина регрессии производительности объясняется в другом элементе соединения

Шпулька была введена из-за общей логики защиты Хэллоуина (это не требуется для выражений XQuery).

Ответ 2

Выглядит как проблема, относящаяся к SQL Server 2008. Когда я запускаю код в SQL Server 2005, обе вставки запускаются быстро и создают идентичные планы выполнения, начинающиеся с фрагмента, показанного ниже, как План 1, В 2008 году первая вставка использует План 1, но вторая вставка создает План 2. Остальная часть обоих планов за пределами показанного фрагмента идентична.

План 1

План 2