为什么从 XML 变量插入选择到变量表这么慢? [英] Why insert-select to variable table from XML variable so slow?

问题描述

我正在尝试将 XML 文档中的一些数据插入到变量表中.让我大吃一惊的是，相同的 select-into(批量)很快就会运行，而 insert-select 需要很长时间，并且在查询执行时让 SQL 服务器进程对 100% 的 CPU 使用率负责.

I'm trying to insert some data from a XML document into a variable table. What blows my mind is that the same select-into (bulk) runs in no time while insert-select takes ages and holds SQL server process accountable for 100% CPU usage while the query executes.

我查看了执行计划，确实存在差异.insert-select 增加了一个额外的Table spool"节点，即使它没有分配成本.表值函数 [XML 阅读器]"然后获得 92%.使用 select-into，两个表值函数 [XML Reader]"各获得 49%.

I took a look at the execution plan and INDEED there's a difference. The insert-select adds an extra "Table spool" node even though it doesn't assign cost. The "Table Valued Function [XML Reader]" then gets 92%. With select-into, the two "Table Valued Function [XML Reader]" get 49% each.

请解释为什么会发生这种情况"和如何(优雅地)解决这个问题"，因为我确实可以批量插入到临时表中，然后又插入到变量表中，但这太令人毛骨悚然了.

Please explain "WHY is this happening" and "HOW to resolve this (elegantly)" as I can indeed bulk insert into a temporary table and then in turn insert into variable table, but that's just creepy.

我在 SQL 10.50.1600、10.00.2531 上尝试过，结果相同

I tried this on SQL 10.50.1600, 10.00.2531 with the same results

这是一个测试用例:

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)

非常感谢！！

推荐答案

这是 SQL Server 2008 中的错误.使用

This is a bug in SQL Server 2008. Use

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 下).

This workaround is from an item on the Microsoft Connect Site which also mentions a hotfix for this Eager Spool / XML Reader issue is available (under traceflag 4130).

性能回归的原因在不同的连接项

线轴是由于通用的万圣节保护逻辑而引入的(XQuery 表达式不需要).

The spool was introduced due to a general halloween protection logic (that is not needed for the XQuery expressions).

这篇关于为什么从 XML 变量插入选择到变量表这么慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！