为什么insert-select to variable table from XML variable这么慢？

Question

我正在尝试将XML文档中的一些数据插入到变量表中。让我印象深刻的是，同样的select-into (批量)很快就会运行，而insert-select会耗费很长时间，并在查询执行时让SQL服务器进程对100%的CPU使用率负责。

我看了一下执行计划，确实有区别。insert-select添加了一个额外的"Table spool“节点，即使它没有分配成本。然后，“表值函数XML Reader”获得了92%。使用select-into，两个“表值函数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)

非常感谢！

Answer 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上的一个项目，该项目还提到了针对此急切假脱机/ XML Reader问题的热修复程序(在traceflag 4130下)。

性能回归的原因在a different connect item中进行了说明

由于一般的万圣节保护逻辑( XQuery表达式不需要)而引入了假脱机。

Answer 2

看起来是特定于SQL Server 2008的问题。当我在SQL Server2005中运行代码时，这两个插入都运行得很快，并生成相同的执行计划，它们以下面显示的PLAN1片段开始。在2008年，第一次插入使用计划1，但第二次插入产生计划2。除了显示的片段之外，两个计划的其余部分是相同的。

计划1

​

​

计划2

​

​