大表中带有分散/扁平化xml字段的更快选择

Question

给定一个包含类似以下记录的 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
• "Value"元素的数量对于给定的ScheduleId是可变的，但始终相同
• 在<Data>节点内只有<Value>节点，并且它们始终是数字
• 表可以有超过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() 和 .queries()

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，element的数量始终相同，但是不同的ScheduleId将具有不同数量的value element。例如：

所有ScheduleId=1都有3个value元素（time_X、var1_Y、var2_Y）

所有ScheduleId=2都有2个value元素（time_X、var1_Y）

所有ScheduleId=3都有33个value元素（time_X、var1_Y，......）

- Matma

---

如果您想实现最佳性能，需要在数据库中消除 XML，仅使用主表或子表上的列。 - TheMadDBA

我希望我能够做到，但这就是我必须使用的工具，所以我只是在寻找最好的解决方案来应对困境。 - Matma

你可以尝试移除浮点数转换来查看是否有所帮助。你可以在XML“列”上创建索引，但这会使你的数据库变得更大。 - TheMadDBA

尝试添加索引，但数据库增长到了 SQL Express 的 10GB 限制并停止了。. Value 需要第二个参数，因此不确定如何删除转换... - Matma

Answer 1

如果您可以向“档案”表中添加字段，那么您可以创建XML数据的持久化计算字段作为函数。例如，您可以创建一个名为“value1”的字段，并将其设置为等于Data.value('(/Data/Value/text())[1]', 'float')，然后在列上设置持久标志。这样，在添加或更新记录时它将被解析一次，然后它有自己的数据字段，您可以选择输出它。

不过实践起来有点笨拙，因为您必须使用UDF，因为不能直接使用XML方法。但它看起来像这样：

GO
create table TempArchive
(
  ArchiveId integer not null,
  ScheduleId integer not null,
  [Data] xml not null,
  CONSTRAINT PK_Archive
  PRIMARY KEY CLUSTERED (ArchiveId)
  WITH (IGNORE_DUP_KEY = OFF)
)
GO
create function udf_getdatacolumn
(
    @data xml,
    @index as int
) RETURNS float
with schemabinding
as
begin
return @data.value('(/Data/Value/text())[sql:variable("@index")][1]', 'float')
end
GO
alter table TempArchive add value1 as (dbo.udf_getdatacolumn(data, 1)) persisted
alter table TempArchive add value2 as (dbo.udf_getdatacolumn(data, 2)) persisted
alter table TempArchive add value3 as (dbo.udf_getdatacolumn(data, 3)) persisted
alter table TempArchive add value4 as (dbo.udf_getdatacolumn(data, 4)) persisted
GO
insert into TempArchive values (1, 2, '<Data>
  <Value>1.0</Value>
  <Value>2.0</Value>
  <Value>3.0</Value>
  <Value>4.0</Value>
</Data>')
GO
select ArchiveId, ScheduleId, Value1, Value2, Value3, Value4 
from TempArchive
GO

返回：

ArchiveId   ScheduleId  Value1     Value2     Value3     Value4
----------- ----------- ---------- ---------- ---------- ----------
1           2           1          2          3          4

(1 row(s) affected)

请记住使用大量数据时，首次添加这些计算列需要很长时间。我建议在投入生产之前进行测试。这也会增加您的表格大小。

- Brian Pressler

---

添加到答案中...实现起来比预期的要复杂一些。 :) - Brian Pressler

今天早上我要试一下这个，但我怀疑如果插入数据会使事情变慢，我将不得不考虑其他选项，因为我们正在从机器传感器记录应用程序中添加大量记录。此外，我还需要考虑如何处理不同scheduleId之间的Value元素数量不同的问题（对于单个scheduleId，数量相同，但对于不同的scheduleId，数量将不同）。我将更新问题以澄清。 - Matma

Answer 2

可能最好的解决方案是允许系统将XML列中的数据直接添加到归档表中，然后按计划将数据移动到规范化的表结构以供报告使用。您可以设置SQL代理作业或创建某些服务程序来将数据移动或复制到报告数据库中。一旦数据在报告表中，您可以选择以下操作之一：

• 从归档表中清除记录
• 将归档表记录移动到具有相同结构的另一个表/数据库中。
• 创建一个字段，标记记录何时已添加到报告表/数据库中。

您可以选择符合您要求的任何一种方法。

您的报告表格基本上会分成两个或三个表格。有一个由 (ArchiveId, ScheduleId) 组成的 ArchiveTable。然后您有一个由 (ArchiveId, ValueId, DataPointValue) 组成的 ArchiveDataPointTable。如果您想要数据点的标签，还可以创建一个由 (ValueId, ValueDescription) 组成的 ValuesTable。然后您的图表报告可以运行在仅包含所需数据点的数据透视查询上。因为没有字符串解析，所有值都是数字，所以它应该非常快速。

- Brian Pressler

---

最终我将使用此解决方案的一种变体，并将表格展平为常规列，所有查询等只需要运行得更快。这对应用程序而言几乎是心脏手术级别的问题，但从长远来看，即使最终会存储额外的空列，它也将显着加速事情。 - Matma