sql-server - 在具有切碎/扁平 xml 字段的大表上更快地选择

Question

给定一个包含如下记录的 MS SQL EXPRESS 2008 R2 表

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

Data Field 中的 XML 结构如下

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

使用以下捕获进行选择的最快方法是什么

• 查询需要返回超过 200 万行!
• 由 ScheduleId 返回
• 返回“数据”列Value节点切碎成列
• 并为返回的每一行包含 id 字段 archiveid 和 scheduleid
• 每个 ScheduleId 的“值”元素的数量是可变的，但对于给定的 ScheduleId 总是相同的
• 永远只有<Value> <Data> 中的节点节点，它们总是数字
• 表最多可以有 5000 万行，目前仅在 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 的元素数量始终相同，但不同的 ScheduleId 将具有不同数量的值元素。

• 所有 ScheduleId=1 都有 3 个值元素(time_X、var1_Y、var2_Y)
• 所有 ScheduleId=2 都有 2 个值元素(time_X、var1_Y)
• 所有 ScheduleId=3 都有 33 个值元素(time_X, var1_Y,......)等等

Answer 1

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

虽然不能直接使用 XML 方法，但由于您必须使用 udf，因此在实践中有点笨拙。但它看起来像这样:

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)

请记住，对于大量数据，首次添加这些计算列时会花费很长时间。我建议在投入生产之前对其进行测试。它还会增加表格的大小。