logo标签列表

重构“极端”的SQL查询

sqlsql-server-2005refactoring
3
我有一个商业用户试图自己编写SQL查询来生成项目统计报告（例如任务数量、里程碑等）。查询开始声明了一个临时表，其中包含80多个列。接下来有近70个UPDATE语句，涉及将各自的业务规则应用于代码中的每行数据。最后使用SELECT * from the临时表完成查询。
由于时间限制和其他因素，这个查询被匆忙推向生产环境，现在我的团队需要支持它。尽管经过一些整理，代码相对易读易懂（虽然代码质量不佳），但性能十分糟糕。
我们应该关注哪些关键问题，以使查询更快并符合良好的实践标准？
将此信息添加到主要文章中，并在可能的情况下进行适当标记。 - Adriano Varoli Piazza
11个回答
5
首先，如果这不会引起业务问题，请等到它成为问题时再解决。当你决定要修复它时，请检查是否有一个语句导致了大部分速度问题...隔离并修复它。
如果速度问题涉及所有语句，并且你可以将其全部合并成单个SELECT，则可能会节省时间。我曾经将一个存储过程（更新较少）转换为SELECT，运行时间从3分钟以上缩短到了不到3秒钟（真的...我简直不敢相信）。顺便说一下，如果一些数据来自链接服务器，请不要尝试这样做。
如果由于某种原因你不能或不想这样做，则可能需要调整现有的存储过程。以下是我会考虑的一些事情：
  1. 如果你正在为临时表创建索引，请在初始插入后再进行。
  2. 调整初始插入以尽可能多地插入列。你可能可以通过这样做消除一些更新。
  3. 在运行更新之前对临时表进行索引。不要在任何被更新语句所针对的列上创建索引，直到它们被更新。
  4. 如果你的表和分组允许，将你的更新分组。70次更新对于只有80列的表来说是相当多的，听起来可能有机会这样做。
祝你好运。
3
我会做的第一件事是检查是否定期运行了活动索引维护作业。如果没有，请重建所有现有索引，或者如果不可能，则至少更新统计信息。
其次，我会设置跟踪（如此处所述），并找出哪些语句导致读取数量最多。
然后，我将在SSMS中使用“显示实际执行计划”并将结果与跟踪相结合。从这个结果中，你应该能够确定是否存在缺失的索引可以提高性能。
编辑：如果您要投反对票，请注明原因。
2
在大约500行代码中，临时表有近70个UPDATE语句，每个语句都包含自己的一组小业务规则。最后以SELECT * from the temp table结束。
实际上，这听起来很容易理解，每个更新语句都针对表执行一个特定目的和一组业务规则。我认为，维护由15个或更多联接、CASE语句等散布在各处的“一切都做”的一个或几个SELECT语句的500行代码过程要困难得多。虽然这样会提高性能...
在SQL中，编写清晰简洁的代码（使用多个更新、创建函数等）似乎总是对性能产生很大的负面影响。尝试一次性完成所有操作，在其他编程语言中被认为是不良实践，但在面向集合的语言中似乎是核心。
2
与任何重构一样，请确保在每次更改后都有自动验证重构的方式（您可以使用查询编写这些内容，以检查开发输出与已知良好基线的匹配情况）。这样，您始终在匹配已知良好数据。当您进入决定是否切换到新版本流程并希望并行运行几次迭代以确保正确性的阶段时，这将使您对方法的正确性具有高度信心。
我还喜欢记录所有测试批次和批处理中过程的运行时间，这样我就可以知道某个特定的过程是否在某个时间点受到不利影响。我可以获得过程的平均时间，并查看改进趋势或发现潜在问题。这也让我识别出批处理中最容易改进的地方。
1

好的，既然你告诉我们的唯一关于这个存储过程的事情就是它有一个80+列的临时表，那我唯一能建议的就是删除该表，并重新编写其余部分以消除对它的需求。

谢谢James，现在在问题中提供了更多的细节。 - Alex Angas
1

如果这是一个报告生成的存储过程，它被运行的频率是多少？如果只需要每天运行一次，并且在夜间运行，那么性能会有多大问题？

如果不是的话，我建议在重新编写之前要小心，因为有可能会弄乱你的数据。

此外，听起来像是应该将其拆分成一个SSIS包，用结果构建一个新的永久表，这样只需要运行一次。

希望这有意义。

1

你可以尝试的一件事是用表变量替换临时表。有时候这样做会更快，有时候则不然，你需要自己试一下。

看看那70个更新语句。能否将它们合并？如果编写者没有使用CASE语句，可能可以减少语句数量。

其他明显需要注意的事情 - 消除任何游标，将任何子查询改为与表或派生表的连接。

1
也许可以重写一下。硬件上的一个解决方案是确保您的数据库临时表位于“快速”驱动器上，例如固态硬盘（SSD），或可以全部存储在内存中进行管理。
我猜这个“解决方案”是由一个对电子表格有了解和依赖的人开发的，可能不太懂得如何构建和填充表以保留数据用于报告目的，这方面也许可以利用复杂而又适应性强的商业智能软件来实现。
您没有说明更新过程运行的“位置”。更新过程是否作为SQL脚本从单独的计算机（台式机）针对数据所在的服务器运行？这种方法可能会产生显著的瓶颈和开销。如果是这样，请考虑直接在服务器上作为本地作业运行整个更新过程，作为编译过的存储过程，绕过网络和（多个）光标管理开销。它可以有一个预定的运行时间和一个被控制的优先级，在非业务高峰期完成。
评估“提交”语句在更新语句序列中真正需要多少次……减少一堆提交行可能会显着改善整体更新时间。数据库客户端驱动程序中可能有一些设置可以产生显著的差异。

更新条件所使用的查询是否可以作为静态“视图”进行分解，然后可以在多个更新语句之间共享？视图可以保留经常访问的内存数据/查询行。在确定挂起多少更新数据才是最优提交之前，可能需要进行性能调整。

值得评估的是，触发器是否可以用于替换批处理作业更新序列。您没有说明数据来自多少个表...这可能有助于决策。我不知道您是否有将触发器添加到收集数据的数据库表的选项。如果有，向多个表添加几个触发器不会真正降低整个系统的性能，但可能会节省大量更新过程的时间。您可以尝试逐个用触发器替换更新语句，看结果是否与以前相同。创建一个类似的临时表，基于相同的更新过程，然后仔细测试触发器是否可以将更新提供给临时表，以替换单个更新语句。也许您可能有一种“数据仓库”应用程序。回顾如何设置表的“星型”模式以保留汇总的业务数据进行报告可能很有用。

创建全面且缓存的“视图”，每天通过查询进行更新，反映更新可能是另一种要探索的方法。

0
你应该使用一个工具，可以让你获取应用程序将运行的所有查询的解释计划。对于 SQL 重型应用程序来说，这是性能提升的最佳选择。如果你阅读并根据解释计划所告诉你的内容做出反应。如果你使用 Oracle，我们曾经使用的是 Qwest 的 TOAD 工具（我想是这个）。它是一个很棒的工具。
0
我建议您查看涉及的表格、最终结果，并从头开始，以查看查询是否可以以更高效的方式完成。保留查询以验证新查询与旧查询完全相同，但尝试忘记用于获取最终结果的所有方法。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接