sql - 有没有比这更快的方法从 T-SQL 中的 XML 节点中提取数据?
问题描述
我目前正在尝试在 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 表。
任何人都可以提出一种加快这个过程的方法吗?
解决方案
当您查询大量列时,在 SQL Server 中使用节点()/值()分解 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 行使用 node()/value() 大约需要 14 秒,使用 OPENXML 大约需要 3 秒。
用于测试的代码;
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());
推荐阅读
- powershell - 适用于 Sky for Business 的 Foreach Powershell 命令
- angular - ng-bootstrap 手风琴:如何将类添加到 nbg-pannel 的包装器?
- apache-flink - flink jobmanager删除前如何删除rocksdb(flink state)文件?
- python - Python获取不会导致错误的所有用户在对象中的列表
- csv - 我们可以在 hive(Hadoop 工具)中合并 .CSV 文件和 .RAR 文件吗?
- html - 无法绑定到“配置”,因为它不是“ng-material-multilevel-menu”的已知属性
- sql - 当我的 sql 查询必须通过表中的两个元素进行选择时,如何在 rest api 上纠正 app.get 函数中的路径?
- xml - 当 XML 子元素包含命名空间时更正 XPath 语法
- php - 无法识别 ObjectManager(声明必须与 FixtureInterface->load 兼容)
- mysql - 引起:org.springframework.dao.IncorrectResultSizeDataAccessException:查询没有返回唯一结果:3