sql-server - 随着 xml 文档的增长，交叉应用 xml 查询的性能呈指数级下降

Question

我拥有的

我有一个可变大小的 XML 文档，需要在 MSSQL 2008 R2 上进行解析，如下所示:

<data item_id_type="1" cfgid="{4F5BBD5E-72ED-4201-B741-F6C8CC89D8EB}" has_data_event="False">
  <item name="1">
    <field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.506543009706267</field>
    <field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.79500402346138</field>
    <field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.0152649050024924</field>
  </item>
  <item name="2">
    <field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.366096802804087</field>
    <field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.386642801354842</field>
    <field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.031671174184115</field>
  </item>
</data>

.

我想要的是

我需要将其转换为常规的表类型数据集，如下所示:

item_name field_id                             field_type  field_value
--------- ------------------------------------ ----------- ---------------
1         EA032B25-19F1-4C1B-BDDE-3113542D13A5 2           0.5065430097062
1         71014ACB-571B-4C72-9C9B-05458B11335F 2           -0.795004023461
1         740C36E9-1988-413E-A1D5-B3E5B4405B45 2           0.0152649050024
2         EA032B25-19F1-4C1B-BDDE-3113542D13A5 2           0.3660968028040
2         71014ACB-571B-4C72-9C9B-05458B11335F 2           -0.386642801354
2         740C36E9-1988-413E-A1D5-B3E5B4405B45 2           0.0316711741841
3         EA032B25-19F1-4C1B-BDDE-3113542D13A5 2           0.8839620369590
3         71014ACB-571B-4C72-9C9B-05458B11335F 2           -0.781459993268
3         740C36E9-1988-413E-A1D5-B3E5B4405B45 2           0.2284423515729

.

什么有效

此 cross apply查询创建所需的输出:

create table #temp (x xml)

insert into #temp (x)
values ('
<data item_id_type="1" cfgid="{4F5BBD5E-72ED-4201-B741-F6C8CC89D8EB}" has_data_event="False">
  <item name="1">
    <field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.506543009706267</field>
    <field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.79500402346138</field>
    <field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.0152649050024924</field>
  </item>
  <item name="2">
    <field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.366096802804087</field>
    <field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.386642801354842</field>
    <field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.031671174184115</field>
  </item>
  <item name="3">
    <field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.883962036959074</field>
    <field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.781459993268713</field>
    <field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.228442351572923</field>
  </item>
</data>
')

select c.value('(../@name)','varchar(5)') as item_name
      ,c.value('(@id)','uniqueidentifier') as field_id
      ,c.value('(@type)','int') as field_type
      ,c.value('(.)','nvarchar(15)') as field_value
from   #temp cross apply
       #temp.x.nodes('/data/item/field') as y(c)

drop table #temp

.

问题

当有几百(或更少)时 <item> XML 中的元素，查询执行得很好。然而，当有 1,000 <item>元素，在 SSMS 中完成返回行需要 24 秒。当有 6,500 <item>元素，运行 cross apply大约需要20分钟询问。我们可以有 10-20,000 <item>元素。

.

问题

是什么让 cross apply查询在这个简单的 XML 文档上的表现如此糟糕，并且随着数据集的增长执行速度呈指数级增长？

是否有更有效的方法将 XML 文档转换为表格数据集(在 SQL 中)？

Answer 1

What makes the cross apply query perform so poorly on this simple XML document, and perform exponentially slower as the dataset grows?

它是使用父轴从项目节点获取属性ID。

查询计划的这一部分是有问题的。



请注意来自较低表值函数的 423 行。

再添加一个带有三个字段节点的项目节点就可以了。



返回 732 行。

如果我们将第一个查询的节点加倍到总共 6 个项目节点会怎样？



我们最多返回了 1602 行。

top 函数中的图 18 是 XML 中的所有字段节点。我们这里有 6 个项目，每个项目有三个字段。这 18 个节点在嵌套循环中用于与另一个函数连接，因此 18 次执行返回 1602 行，因此每次迭代返回 89 行。这恰好是整个 XML 中节点的确切数量。嗯，它实际上比所有可见节点多一个。我不知道为什么。您可以使用此查询来检查 XML 中的节点总数。

select count(*)
from @XML.nodes('//*, //@*, //*/text()') as T(X)  

所以SQL Server在使用父轴时获取值的算法 ..在 values 函数中，它首先找到您正在粉碎的所有节点，在最后一种情况下为 18。对于这些节点中的每一个，它分解并返回整个 XML 文档，并在过滤器运算符中检查您实际需要的节点。在那里你有你的指数增长。
您应该使用一个额外的交叉应用，而不是使用父轴。首先在元素上切碎，然后在现场切碎。

select I.X.value('@name', 'varchar(5)') as item_name,
       F.X.value('@id', 'uniqueidentifier') as field_id,
       F.X.value('@type', 'int') as field_type,
       F.X.value('text()[1]', 'nvarchar(15)') as field_value
from #temp as T
  cross apply T.x.nodes('/data/item') as I(X)
  cross apply I.X.nodes('field') as F(X)

我还更改了访问字段文本值的方式。使用 .将使 SQL Server 去寻找子节点到 field并在结果中连接这些值。您没有子值，因此结果相同，但避免在查询计划(UDX 运算符)中包含该部分是一件好事。

如果您使用的是 XML 索引，则查询计划不存在父轴的问题，但您仍然可以从更改获取字段值的方式中受益。