sql - 删除SQL Server 2008数据库中的字符串重复

Question

背景:专业工具开发人员。 SQL / DB业余爱好者。

设置: .Net 3.5 Winforms应用程序与MS SQL Server 2008通讯。

场景:我正在用从大量文件中提取的信息填充数据库。总计约有6000万条记录，每条记录都具有与之关联的任意大小的消息。我最初的计划是在记录中使用 nvarchar(max)字段来保存消息，但是在对数据的子集执行测试后，这将使数据库过大(推断为无法接受的113GB)。在此初始测试数据集(1.3GB数据库)上运行了一些查询，我发现有大量的消息重复，我们可以使用它来将消息数据缩减到大约六分之一。我已经尝试并想到了几种方法来实现这一目标，但没有一种令人满意。我已经搜寻了几天，但是a)似乎没有一个好的答案(不太可能)，或者b)我不知道如何表达我的需求(很有可能)。

已考虑/尝试过的方法:

使用 nvarchar(max)字段将消息批量插入记录中。 –发现冗余过多。

保留此消息列，但找到一种方法来使数据库“压缩”消息。 –不知道该怎么做。

添加一个用于唯一消息的消息表，该表以主记录“指向”的ID为关键字。 –在原则上工作的同时，实现唯一性是很痛苦的，随着添加更多消息，它会减慢速度。

在客户端上执行重复删除。 –要求每次填充会话都将所有消息都提取到客户端。这无法扩展，因为它们需要容纳在内存中。

在消息表中添加一个额外的(索引)哈希列，并使用相应的(本地生成的)哈希值提交消息。搜索此内容以缩小实际需要测试的消息的范围。 –复杂，必须有更好的方法。

第三种方法相当于创建一个字符串字典表。经过几次迭代，我得出以下结论:

数据库具有一个消息表，该消息表将(自动分配的) int ID主键映射到 nvarchar(max)消息。

客户端对消息进行批处理并提交多个记录以插入到存储过程中。

存储过程遍历一批传入记录以及每个消息:

一世。在消息字典表中检查(选择)消息的现有实例。

ii。如果找到，请记住现有消息的ID。

iii。如果找不到，请插入新的消息记录，并记住新记录的ID(OUTPUT)。

所有消息(旧消息和新消息)的ID作为该过程的输出结果集返回。

客户端生成主表记录，其中包含使用从过程返回的ID填充的消息的条目( int 外键)。

问题:

随着消息数量的增加，对现有消息的搜索变得越来越慢，这成为限制因素。

我已经尝试为邮件列建立索引(UNIQUE)，但是您无法为 nvarchar(max)列建立索引。

我看过MS SQL Server 2008的全文本搜索功能，但对我来说似乎有点过头了。

我曾考虑过尝试合并成批邮件，但是我看不到一种方法来轻松获取相应的ID列表(按正确的顺序排列新旧ID)以回馈给客户。

在我看来，我正在尝试对数据进行某种规格化，但是据我对数据库设计的了解，这更像是“行规格化”，而不是关于“列规格化”的适当规格化。令我惊讶的是，这并不需要到处都有相应的支持。

因此，我的问题是:这里正确的方法是什么？

任何帮助，不胜感激。

山姆

Answer 1

规范化(以及规范化的原因)有两个实际方面:数据排列的敏感性(以及相应的维护优势)和性能。

关于敏感性，至少从抽象DB设计的角度来看，您需要考虑的一个问题是数据是否真正重复。尽管您可能有两条具有相同数据的消息，但实际上它们可能并不代表“同一件事”。真正的问题是:两个消息共享相同文本的事实是否使它们成为同一消息？ 换句话说，假设消息A和消息B共享相同的文本，您是否希望消息A中的更改反映在消息B中？

如果您的回答是“是”，则您的字符串字典是正确的方法。如果否，那么您实际上没有重复的数据，只是看起来相同但不是的数据。

从性能的角度来看，我可能认为带有附加消息哈希的字符串字典将是最好的方法。我认为这并不像您认为的那么复杂。在几乎每种语言(包括T-SQL)中都可以使用标准哈希算法，并且在这种情况下，我不会认为冲突甚至哈希值的分布都非常重要，因为您只是将其用作“提示”以加快查询的执行速度。