优化SQL计算序列-嵌套查询还是单独查询？

Question

短简介：

当它需要有12个嵌套的计算查询时，它是否更适合

• A)分别执行每个操作(保存到每个结果的表中，然后为下一个查询读取该表)
• B)有大量的嵌套选择

全称：

我正在尝试从SQL中的一系列输入表中计算一些高级预测。

我正在构建十几个“模块”，这些模块被划分成自己的模式，每个模块通常包括4-10个输入表和6-10个计算步骤。一旦完成，来自每个模块的所有输出都会转储到同一个输出表中。

查询范围从7k到200 k行。

单个模式的/模块表可能如下所示：

• 输入表1
• 输入表2
• 输入表3
• 输入表4
• 计算查询1结果表
• 计算查询2结果表
• 计算查询3结果表
• 计算查询4结果表
• 计算查询5结果表
• 计算查询6结果表
• 最终输出

每个计算查询都使用以前的结果(大部分情况下)。最后的输出是最终计算查询的结果。计算并不十分复杂:分区最大值、基本公式(+、-、*、/)或和等。通常每个计算步骤中只有1-3个，并且总是在同一列上.

将其拆分为多个计算查询(而不是一个超级公式)的主要原因是，每个计算以不同的方式连接输出并使用不同的输入表；还有一些是基于先前的行结果的。(例如最大分区或滞后)

我的要求如下：

• 计算步骤1的最终输出并合并为最终输出的过程。
• 计算到选定的计算查询并合并到其各自的结果表(和stop)中的过程。认为这是“压倒一切的决赛”

我不需要存储中间查询的计算结果-只有最后的输出或‘覆盖最终’，如果选择。

我的问题：，我正在尝试优化整个过程-在这一点上，它看起来大约需要10-15秒。我想要1秒-但是我很感激这可能是不可能的。

我尝试过的：

首先，我为每个计算查询创建了一个过程，将结果合并到相应的输出表中。使用此方法，每个计算查询必须从数据库中读取，然后合并到其输出中。

但是，我尝试了临时表，但我不知道为什么这是最优的，因为我已经有了计算步骤的现有表--这些表都是用下一个步骤索引的。

然后，我假设简单地将所有查询嵌套到一个超级过程中会更快，甚至可能会有一个表函数序列。

我的问题：

然而，我遇到了一个我找不到答案的想法--这就是：

• 在每个计算步骤中将结果插入到表中可能会减慢处理速度(特别是当它们被2-4列索引时)；但是，至少下一步将对数据进行索引。
• 嵌套选择将节省插入数据的工作量，但是这些结果不会被索引吗？对吗？还是错了？

选择的结果是否有明智的索引？考虑到我的情况，你会给我什么建议来解决这个问题。也许我错过了一些很简单的东西。

附加信息：

• 大多数较大的查询结果(150-200K)都有4列需要索引。
• 我所有的表只有一个需要计算的列-其余的都是索引的。

例如: ForecastID，组，年份，类型，子类型，值

因此，我必须索引组、年份、类型和子类型，以连接多个输入表，然后计算值列。

我告诉您这一点，以防索引重的表影响您的建议--我不会在这里寻求优化索引的帮助，因为已经有大量的建议，而且这是一个不同的问题！

Answer 1

查询优化往往是一门艺术，而不是科学，几乎没有硬规则和快速规则，因为对结果有太多可能的影响。有了这么大的警告，是时候到达最高点了。

索引对加载表的影响-索引对插入的性能影响与触发器相似。除非您有一个过滤的索引，否则每个插入都必须更新表上的每个索引，所以在三个索引上，您将考虑将每个插入的更新数量翻两番。在每次插入一次读取和200 k的小表大小(对于一次表扫描非常可行)，对于三个索引，您可能在黄油区域之外的成本和效益，使这些索引在您的工作表。

嵌套结果--就像CTE一样，嵌套结果在整个结果集适合内存时工作得最好。当部件在内存中而部件位于磁盘上时，它的性能通常会比没有索引的类似大小的临时表执行得更差。对于具有较小数据类型的200 k行和一台现代服务器，在5列左右的情况下，只要每次只执行一个结果集，嵌套查询的性能就应该是良好的。再一次，这取决于您的设置，如果您是带着ram，将它们放到临时表中。

联接-使用临时表/嵌套查询的另一个可能的理由是避免过大的联接。连接过程的第一步是表之间的完整笛卡儿连接，然后根据on和where子句进行筛选。在所有RDMS中，Join过程都得到了很大的优化，因此大多数情况下，您不知道在幕后发生了多少繁重的工作，但是当表达到较大的大小时，这可能是一个主要的性能问题。因此，您可以从两个表中选择所需的数据子集，并将两个小得多的集合连接起来。同样，子集和完整表联接之间的黄油区域取决于许多因素，因此您必须使用查询来查找适合您的情况的位置。

不幸的是，如果没有一些示例输入和输出以及/或执行计划，我真的不能给出具体的建议，但我希望这是一些值得思考的东西。祝好运。

Answer 2

听起来您来自子查询的数据集超过几千行，因此我将从方法A开始，将这些中间结果集中的一些保存到#temptables，检查这些表的执行计划，并在需要时对这些表进行索引。

如果您想使用方法B，或者混合A和B，我建议在可能的情况下不要使用嵌套的查询。它们更具可读性，并且在测试/设计查询时更容易切换到#temptables。