给定一个包含类似以下记录的 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,......)