sql - 有没有比这更快的方法从 T-SQL 中的 XML 节点中提取数据？

Question

我目前正在尝试在 T-SQL 中创建一个存储过程，它将一个 XML 表作为其输入，然后将其中的数据插入到一个临时表中。

我使用的 XML 格式如下:

<Table>
    <row MyFirstColumn="foo" MySecondColumn="bar" ... />
</Table>

我用来将此 XML 数据插入临时表的 SQL 格式如下:

INSERT INTO
    #TempTable
SELECT
    T.c.value('@MyFirstColumn', 'varchar(50)')
   ,T.c.value('@MySecondColumn', 'varchar(50)')
   ,...
FROM
    @x.nodes('//Table/row') T(c)

但是，我使用的是包含 150 列和超过 200,000 行的 XML 表。目前，在 10,000 行上执行此 SQL 大约需要 142 秒，因此这对于处理包含大量行的 XML 表是完全不合适的。

谁能建议一种加速此过程的方法？

Answer 1

当您查询大量列时，在 SQL Server 中使用 nodes()/value() 分解 XML 会出现性能问题。有一个嵌套循环连接，每个列都调用一个 xml 函数。

包含 3 列的查询计划:

包含 5 列的查询计划:

想象一下超过 150 列会是什么样子。

您的另一个选择是使用 OPENXML .许多列都没有同样的问题。

您的查询看起来像这样:

declare @H int;
declare @X xml;

exec sys.sp_xml_preparedocument @H output,
                                @X;

select C1,
       C2,
       C3
from
       openxml(@H, 'Table/row', 0)
       with (
              C1 int,
              C2 int,
              C3 int
            );

exec sys.sp_xml_removedocument @H;

对我来说，使用 150 列和 1000 行用 nodes()/value() 花费了大约 14 秒，用 OPENXML 花费了 3 秒。

Vote for a change.

用于测试的代码；

drop table T;

go

declare @C int = 150;
declare @S nvarchar(max);
declare @X xml;
declare @N int = 1000;
declare @D datetime;

set @S = 'create table T('+
stuff((
      select top(@C) ', '+N'C'+cast(row_number() over(order by 1/0) as nvarchar(3)) + N' int'
      from sys.columns
      for xml path('')
      ), 1, 2, '') + ')'

exec sp_executesql @S;

set @S = 'insert into T select top(@N) '+
stuff((
      select top(@C) ',1'
      from sys.columns as c1
      for xml path('')
      ), 1, 1, '') + ' from sys.columns as c1, sys.columns as c2';

exec sp_executesql @S, N'@N int', @N;

set @X = (
         select *
         from dbo.T
         for xml raw, root('Table')
         );

set @S = 'select '+
stuff((
      select top(@C) ', '+N'T.X.value(''@C'+cast(row_number() over(order by 1/0) as nvarchar(3)) + N''', ''int'')'
      from sys.columns
      for xml path('')
      ), 1, 2, '') + ' from @X.nodes(''Table/row'') as T(X)'

set @D = getdate();
exec sp_executesql @S, N'@X xml', @X;
select datediff(second, @D, getdate());

set @S = 'declare @H int;
exec sp_xml_preparedocument @H output, @X;

select *
from openxml(@H, ''Table/row'', 0)
  with (' +
stuff((
      select top(@C) ', C'+cast(row_number() over(order by 1/0) as nvarchar(3))+ ' int'
      from sys.columns
      for xml path('')
      ), 1, 2, '') + ');
exec sys.sp_xml_removedocument @H';

set @D = getdate();
exec sp_executesql @S, N'@X xml', @X
select datediff(second, @D, getdate());