将数据聚合到新表的最有效方法

Question

假设我有一个大表A，大约1.51亿行：

TableA
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[MetaDataProp1] [int] NULL,
[MetaDataProp2] [int] NULL,
[MetaDataProp3] [int] NULL,
[MetaDataProp4] [int] NULL,
[MetaDataProp5] [int] NULL,
[MetaDataProp6] [int] NULL,
[MetaDataProp7] [int] NULL,
[MetaDataProp8] [int] NULL,
[MetaDataProp9] [int] NULL,
[Date] [datetime] NOT NULL,
[Cost] [decimal](10,20) NOT NULL

我需要以'n‘方式聚合我的大表，这取决于我们的最终用户需要什么。

例如，Prop1-3、Day和SUM(Cost)对日期范围X和prop5 IN (X,Y,Z)进行分组。

现在，我有时还必须加入到其他表中，以便在此基础上进行一些数据聚合。在每个视图上创建20+索引似乎是个坏主意，并且会造成大量的写开销。因此，这使得索引视图是不可能的。据我所知，这只剩下一个选项:将查询结果推送到我端的一个新表。但这也有一些缺点：

select insert是相当密集的，因为它在工作时阻塞其他查询。这将是一场灾难，只会有20个总结果。

我的想法是：

• 把最大的身份证拿来。
• 使用offset-fetch从分页批A中选择100 k，其中Id <获取的最大Id。这确保了我们不会无休止地重新获取数据，并推送新的数据，同时也确保数据不会在其选择时突然波动。
• 将聚合批次推送到聚合表。
• 继续前进，直到在分页的选择中找不到任何东西。

这方面的好处是：

• 由于它现在是批处理的，所以它允许在批处理选择和批处理插入之间进行其他查询。

这方面的缺点是：

• 更多的读取I/O，因为每个分页的提取必须排序分组的结果。
• 更长的整体聚集时间。

另一种方法可以是从机器中获取整个数据集，将其全部保存在内存中，然后从机器中批量保存。这将减少读取I/O，但这似乎是个坏主意，因为返回的查询可以是40GB+大小的。运行这样一台机器的成本不值得仅用于聚合数据。

你觉得这个解决方案怎么样？对于问题类型，推荐的解决方案是什么？

数据每天更新3次。在这个过程中，我们删除旧的数据，并推入新的数据。大约几百万删除和重新插入每一个去。

我在Server中听到过关于ColumnStore的低语，现在我可以使用它了。但从来不多看。

Answer 1

这就是SSAS的目的，还有很多客户端工具可以连接到它，包括Excel。

详细阐述

server分析服务是SQL Server的一个免费组件，它将构建数据的多维版本(多维数据集)，它将预先构建所需/定义的所有聚合。

该设计工具可免费下载称为Server数据工具。

建立了基于表的数据源和数据视图之后，最简单的方法就是根据表创建维度和事实，维度将拥有所有元数据描述符，而事实只具有数字/度量。

然后，在Cube中加入这两种工具，一旦发布/部署，SSRS和其他工具都可以连接到它。

一些链接

https://www.codeproject.com/Articles/658912/Create-First-OLAP-Cube-in-SQL-Server-Analysis-Serv

https://technet.microsoft.com/en-us/library/ee677579.aspx