sql-server - 关于如何在十亿行表上扩展和改进 "pivot-based query"的执行时间的建议，每天增加一百万

Question

我们公司正在开发一个内部项目来解析文本文件。这些文本文件由使用正则表达式提取的元数据组成。十台计算机全天候 24/7 解析文本文件，并将提取的元数据提供给高端 Intel Xeon SQL Server 2005 数据库。

简化的数据库模式如下所示:

Items| Id | Name   ||----|--------|| 1  | Sample |

Items_Attributes| ItemId | AttributeId ||--------|-------------|| 1      | 1           || 1      | 2           |

Attributes| Id | AttributeTypeId | Value ||----|-----------------|-------|| 1  | 1               | 500mB || 2  | 2               | 1.0.0 |

AttributeTypes| Id | Name    ||----|---------|| 1  | Size    || 2  | Version |

有许多不同的文本文件类型，其中包含不同的元数据。对于每个文本文件，我们都有一个Item，对于每个提取的元数据值，我们都有一个Attribute。

Items_Attributes 使我们能够避免重复的 Attribute 值，从而避免数据库大小增加 x^10。

这个特定的模式允许我们动态添加新的正则表达式并从新处理的文件中获取新的元数据，无论它们具有哪种内部结构。

此外，这使我们能够过滤数据并根据用户标准获取动态报告。我们按 Attribute 过滤，然后旋转结果集 (http://msdn.microsoft.com/en-us/library/ms177410.aspx)。所以这个例子伪sql查​​询

从大小 = @A 和版本 = @B 的项目中选择

会像这样返回一个透视表

<预>|元素名称 |尺码 |版本 ||------------|--------|--------|| sample | 500 兆字节 | 1.0.0 |

该应用程序已经运行了几个月，性能急剧下降，以至于无法再使用。报告时间不应超过 2 秒，Items_Attributes 表平均每周增加 10,000,000 行。一切都已正确索引，我们花费大量时间分析和优化查询执行计划。

所以我的问题是，您将如何扩展它以减少报告执行时间？

我们提出了这个可能的解决方案:

• 购买更多硬件并设置 SQL Server 集群。 (我们需要有关正确“集群”策略的建议)
• 使用像 HBase 这样的键/值数据库(我们真的不知道是否能解决我们的问题)
• 使用 ODBMS 而不是 RDBMS(我们一直在考虑 db4o)
• 将我们的软件迁移到云端(我们的经验为零)
• 在运行时静态生成报告。 (我们真的不想)
• 常见报告的静态索引 View (性能几乎相同)
• 去规范化架构(我们的一些报告在单个查询中涉及多达 50 个表)

Answer 1

也许 SQL Server CAT 团队关于实体-属性-值数据库模型陷阱的这份白皮书可以提供帮助:http://sqlcat.com/whitepapers/archive/2008/09/03/best-practices-for-semantic-data-modeling-for-performance-and-scalability.aspx