sql-server - 带有碎裂/展平的xml字段的大型表上的快速选择

给出一个带有如下记录的MS SQL Express 2008 R2表

ArchiveId   ScheduleId  Data
391063      62          <Data>....</Data>
391064      62          <Data>....</Data>
391065      63          <Data>....</Data>

数据字段中的XML是这样构造的

<Data>
  <Value>1.0</Value>
  <Value>2.0</Value>
  <Value>3.0</Value>
  <Value>4.0</Value>
</Data>

用下面的捕获来进行选择的最快方法是什么
查询将需要返回超过2m+行！
按scheduleID返回
返回“data”列Value分解成列的节点
并为返回的每一行包含id字段archiveid和scheduleid
每个scheduleid的“value”元素的数量是可变的，但对于给定的scheduleid总是相同的
在<Value>节点中只有<Data>个节点，它们始终是数字
表可以有超过50M行，并且当前仅在scheduleID（非唯一非群集）和archiveID（pk群集）上编制索引
理想情况下，我正在寻找格式的数据；

ArchiveId   ScheduleId  Value1  Value2  etc
391063      62          1.0     2.0
391064      62          1.1     2.1

我试着用

select
    ArchiveId,
    ScheduleId ,
    v.value('(Value/text())[1]', 'float') as value1 ,
    v.value('(Value/text())[2]', 'float') as value2 ,
    v.value('(Value/text())[3]', 'float') as value3 ,
    v.value('(Value/text())[4]', 'float') as value4
from
    Archives
    cross apply [data].nodes('//Data') x(v)
where
    ScheduleId = 2499

以及直的.values（）和.querys（）

select
    ArchiveId,
    ScheduleId,
    Data.value('(/Data/Value/text())[1]', 'float') as value1,
    Data.value('(/Data/Value/text())[2]', 'float') as value2,
    Data.value('(/Data/Value/text())[3]', 'float') as value3,
    Data.value('(/Data/Value/text())[4]', 'float') as value4
from
    Archives
where
    ScheduleId = 2499
order by
    ArchiveId asc

这两种方法都可以工作，但在大型数据集上的速度非常慢，我想知道是否有一种更快的方法可以在非常大的行上执行这类操作。我意识到，无论这将需要一段时间，但什么是我最好的赌注时，这样做。
这里有很多例子，但它们都有更复杂或动态的数据结构，或者基于xml内容本身有某种复杂的选择需求。
我拥有的数据总是相同的结构（一个数据节点和x值节点），选择标准根本不在xml中。
我只是在寻找一种最简单的方法，在将XML展成列的同时将大量记录拉回来。
编辑：本质上，我们将图形数据存储在XML中，以便以后绘制线图。重要的是，虽然同一scheduleID的元素数始终相同，但不同scheduleID的值元素数不同。
所有scheduleID=1都有3个值元素（time、var1、var2）
所有scheduleID=2都有2个值元素（time-x，var1-y）
所有scheduleID=3都有33个值元素（time_x，var1_y，…）
等

最佳答案

最好的解决方案可能是允许您的系统将XML列中的数据按原样添加到归档表中，然后按计划将数据移到规范化的表结构中进行报告。您可以设置一个sql代理作业或创建一些服务程序来将数据移动或复制到报表数据库。一旦数据在报表中，您可以：
从存档表中清除记录
将存档表记录移动到具有相同结构的另一个表/数据库。
创建一个字段，该字段在将记录添加到报表表/数据库时进行标记。
你可以选择一个符合你要求的。
你的报告表基本上可以分成两到三个表。这是由（ArchiveTable，scheduleID）组成的ArchiveId。然后你的ArchiveDataPointTable由（ArchiveId，ValueId，DataPointValue）制成。如果需要数据点的标签，也可以创建一个（ValuesTable，ValueId）的ValueDescription。然后，图表报表可以只使用所需的数据点运行透视查询。因为不会有字符串解析，而且所有的值都是数值，所以应该非常快。

关于sql-server - 带有碎裂/展平的xml字段的大型表上的快速选择，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30916859/