重构“极端”SQL查询

Question

我有一个业务用户，他尝试编写自己的SQL查询，以获得项目统计数据(例如，任务数量、里程碑等)的报告。查询首先声明一个包含80+列的临时表。然后，临时表有近70条UPDATE语句，超过近500行代码，每行代码都包含自己的一组小业务规则。它以来自临时表的SELECT *结束。

由于时间限制和‘其他因素’，这是匆忙投入生产，现在我的团队坚持支持它。性能是令人震惊的，尽管多亏了一些整理，它相当容易阅读和理解(尽管代码味道很难闻)。

为了加快这一过程并遵循良好的实践，我们应该关注哪些关键领域？

Answer 1

首先，如果这不会导致业务问题，那么就让它成为一个问题。等到它成为一个问题，然后解决所有问题。

当你决定修复它时，检查是否有一条语句导致了大部分的速度问题……隔离并修复它。

如果速度问题是所有语句的问题，并且您可以将其组合到单个SELECT语句中，这可能会节省您的时间。我曾经将这样的一个进程(没有那么多的更新)转换为SELECT，运行它的时间从超过3分钟到不到3秒(没有狗屎……我简直不敢相信)。顺便说一句，如果某些数据来自链接服务器，请不要尝试这样做。

如果您不想这样做，或者因为某种原因不能这样做，那么您可能需要调整现有的proc。以下是我会考虑的一些事情：

1. 如果要在临时表上创建索引，请等到首次插入后再填充它。
2. 调整初始插入以插入尽可能多的列。可能有一些更新可以通过执行此操作来消除。
3. 在运行更新之前索引临时表。在update语句所针对的任何列完成更新之前，不要为其创建索引。
4. 如果您的表和分组允许，请对您的更新进行分组。70次更新对于只有80列的人来说是相当多的，听起来可能有机会做到这一点。

祝好运

Answer 2

我要做的第一件事是检查以确保有一个活动的索引维护作业正在定期运行。如果没有，请重新构建所有现有索引，如果不可能，则至少更新统计信息。

我要做的第二件事是设置一个跟踪(如here所述)，并找出哪些语句导致了最高数量的读取。

然后，我会在SSMS中运行“show actual execution plan”，并将结果与跟踪结果相一致。由此，您应该能够确定是否缺少可以提高性能的索引。

编辑:如果你要投反对票，请留言说明原因。

Answer 3

就像任何重构一样，确保您有一种在每次更改后自动验证重构的方法(您可以使用查询自己编写，这些查询将根据已知的良好基线检查开发输出)。这样，您就可以始终匹配已知良好的数据。当您进入决定是否切换到流程的新版本并希望并行运行几次以确保正确性的阶段时，这将使您对方法的正确性有高度的信心。

我还喜欢记录所有的测试批次和批次中进程的运行时间，这样我就可以判断批次中的某个特定进程是否在某个时间点受到了不利影响。我可以得到过程的平均时间，并看到改进的趋势或发现潜在的问题。这也让我在批次中识别出我可以做出最大改进的低挂水果。