将 XML 格式的数据大数组(列表)存储到 SQL Server 2016 中 [英] Store big arrays (Lists) of data in XML format into SQL server 2016

问题描述

我需要保存在 C# 中生成的多次测量的结果.
结果是不同类型数据的大列表:

I need to save the result from several measurements generated in C#.
The results are big Lists of data of different types:

List<double, double> [20000 records]
List<string, double, double> [20000 records]
List<int, double, double> [20000 records]

我的第一个想法是将这些列表以 XML 格式存储到一个表(xml 字段)中.因此，对于每个列表，我都会生成一个 XML 文件并将其插入表中.

My first thought was to store these lists in XML format into a table (xml field). So, for each List I generate a XML file and insert it into the table.

<MeasurementList>
  <item>
    <left_measurement>0.1264</left_measurement>
    <right_measurement>6.500</right_measurement>
  </item>
  <item>
    <left_measurement>0.2314</left_measurement>
    <right_measurement>6.968</right_measurement>
  </item>
  <item>
    <left_measurement>0.2365</left_measurement>
    <right_measurement>7.598</right_measurement>
  </item>
  ...
</MeasurementList> 

这将每天发生 3000 次，所以我每天需要 9000 条记录.

This will occur 3000 times a day, so I need 9000 records each day.

我知道磁盘空间是个问题，但我主要担心的是:
这是最好的方法还是有其他解决方案

I know that the disk space is an issue, but my main concern is:
Is this is the best aproach or there are any other solutions

推荐答案

由于您自己的答案被删除为 only-link-answer 我会将我的评论作为答案(并在上面删除它们)).

As your own answer was deleted as a only-link-answer I'll place my comments as answer (and delete them above).

将数百万个值填充到数百万行中仍然需要比存储相同数量的数据(嵌套在纯数据所需字符数的四倍左右)更少的位置.

Stuffing millions of values into millions of rows will still need less place than to store the same amount of data nested in about four times the characters you'd need for the pure data.

至少我会使用 而不是 .(字符)空间中最短的是

At least I'd use <lm> instead of <left_measurement>. The shortest in (character) space was

<L v="0.2314"/><R v="6.968"/>
or 
<L>0.2314</L><R>6.968</R>
or 
<v l="0.2314" r="6.968">

最后一个选项将反映您的 List[20000 条记录] 最好...

The last option would reflect your List<double, double> [20000 records] best...

主要问题是:几天后你打算怎么办?您可以以某种方式存储数百万个值，但是您是否需要再次读取它们?如果不是:你为什么要存储它?如果是(这就是我的假设):不要将它放在基于字符串的容器中！你会遇到严重的性能问题...

The main question is: What are you going to do with this after some days? You can store millions of values somehow, but will you ever have to read them again? If not: Why do you store it at all? If yes (and that's what I assume): Don't put this in a string based container! You will get into sever performance troubles...

关于性能和磁盘空间:

• a FLOAT 将占用 4 个字节，而 0.2314 占用 6 个字符(NVARCHAR 中的 12 个字节！)并且您不需要额外的空间元数据！
• SQL Server 会将 XML 存储为带有 NVARCHAR 字符串的隐藏层次结构表(每个字符 2 个字节！)
• JSON 仍然存储为您看到的字符串(如果 纯拉丁语，您可以使用 VARCHAR，每个字符占 1 个字节).
• XML 在查询中比 JSON 快得多(不需要结构解析)...
• (XML 和 JSON)都基于字符串存储它们的值.任何读取这些值的尝试都需要进行类型转换(0.1234"不是一个数字，而是一个字符串，看起来像一个数字！)这对于几个值来说并不是很昂贵，但这会使您的系统崩溃数十亿数据...

• a FLOAT will take 4 bytes, while 0.2314 consumes 6 characters (12 bytes in NVARCHAR!) and you do not need extra space for the meta data!
• SQL Server will store an XML as a hidden hierachy table with NVARCHAR strings (2 bytes per character!)
• JSON is still stored as the string you see (If plain latin you can use VARCHAR with 1 byte per character).
• XML is much faster in queries than JSON (no structure parsing needed)...
• Both (XML and JSON) store their values string based. Any attempt to read these values will need a type conversion ("0.1234" is not a number, but a string, that looks like a number!) This is not very expensive with a few values, but this will tear down your system with billions of data...

您可能会阅读我什么时候可以将 JSON 或 XML 数据保存在 SQL 表中

这篇关于将 XML 格式的数据大数组(列表)存储到 SQL Server 2016 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！