时间戳

首页 > 解决方案 > 添加临时表步骤以提高插入性能 - 可接受的做法？

sql - 添加临时表步骤以提高插入性能 - 可接受的做法？

问题描述

在 INSERT 过程中创建一个附加步骤，从源视图中选择相关数据到临时表中，然后将其用作后续 INSERT 语句的源，这是一种好的/可接受的做法吗？

背景：我正在尝试提高 ETL 过程的性能，该过程由于数据量呈指数增长而大幅下降。

有 c.1500 个表，由使用少量模板和可自定义元数据创建的存储过程（通过 SSIS 包）填充。

大多数 procs 遵循一个过程，例如：

INSERT INTO dest_tbl
SELECT col1, col2, col3...
FROM source_view s
LEFT JOIN other_tbl ot on s.col1 = ot.col1
WHERE s.date > ot.prev_date
AND ot.col2 is null

对于受影响最严重的过程，source_views 必然相当复杂，要连接 5-10 个表并应用业务规则。示例表通常将 80,000 条记录的区域插入到具有 89,000,000 行的表中。

我在查询计划中发现了一些糟糕的估计，我试图通过以下方式解决： -更新使用的表/索引的统计信息（使用 FULL SCAN）。- 修改底层视图中的逻辑 - 创建新索引

我在这些事情上没有很强的背景，尤其是更复杂的索引端——但取得了一些有限的成功。

但是，当我尝试在另一个 JOIN / filter/ INSERT 之前添加一个将视图中的所有内容插入临时表的步骤时，它显着提高了性能 - 一些表的运行速度提高了 85% - 例如：

SELECT *
INTO #TMP
FROM source_view

INSERT INTO dest_tbl
SELECT col1, col2, col3...
FROM #TMP s
LEFT JOIN other_tbl ot on s.col1 = ot.col1
WHERE s.date > ot.prev_date
AND ot.col2 is null

现在，在中短期内，我对此感到满意；我的关键性能问题已解决。

我的问题真的是：
1）这被认为是好的/可接受的做法吗？
2）随着数据量的不断增加，这是否可能是可扩展的？
3）这种方法有什么我可能没有考虑过的问题吗？

还是我可能只是延迟了更深入的查询/索引调整的痛苦？

编辑 06/09/2019

艾伦伯恩斯坦回答后的更多信息：
1）我相信这不仅仅是更新的执行计划导致这种情况下的改进。我使用这种方法的一些 procs 已经运行了大约 6 周，并且一直表现得比以前 proc 的最佳运行要好得多。在测试中，我在运行每种方法之前清除了缓存，即使更新了统计/查询计划，旧版本也总是较慢。

2) Tempdb 被拆分为 6 个 mdf 文件，并且最近没有经历过自动增长。我们的服务器位于虚拟基础架构上，虽然我不会假装非常了解它的工作原理，但我们的 IT 团队和存储供应商向我保证，所有 DB/LUN 都将根据活动迁移到更快的存储。虽然我怀疑这可以改进，但我认为这不是这个例子中的一个主要因素——而且我的双手在改变该设置的任何内容方面相当束缚。

3）我尝试了一些更改视图的编写方式，但没有取得多大成功，但接受那里可能还有改进的余地。视图和基础表几乎只在夜间发生的日常 ETL 期间使用，因此不应该有其他进程锁定和阻塞。

4) 两个版本都是并行的，没有用户定义的函数或视图调用的计算列。

5) 这是可能的，但是传递到 proc 的这一部分的唯一参数是加载日期。

我的背景更多是使用 SQL 进行分析，所以虽然我现在正在学习如何阅读查询计划、理解统计信息、设计索引和调整查询，但这是一个陡峭的学习曲线，而且我还是个新手。

虽然我在其他情况下遇到了爆炸性的查询日志问题（尽管是简单的恢复模式），但大多数情况下都没有发生这种情况。不过，批处理可能值得一试。

似乎从视图中选择的行数估计不佳在选择临时表时的影响比插入到大型持久表中时的影响要小，因此首先运行该步骤然后使用准确信息运行的 INSERT好吧...但我可能从错误的角度来看。

标签： sqlperformancetsqloptimization

解决方案

我的问题真的是：1）这被认为是好的/可接受的做法吗？

是的 - 可以接受，但您没有提供足够的细节来确定这是否是一种“好的”做法。例如，

“但是，当我尝试在另一个 JOIN/过滤/插入之前添加一个步骤以将视图中的所有内容插入临时表中时，它显着提高了性能 - 一些表的运行速度提高了 85%”

要理解的重要一点是查询改进的原因。所有事情都是平等的 - 将数据从表中提取到临时表中，然后从该临时表中读取在理论上应该会更慢，因为您正在做更多的事情，但是有很多原因可以让您按照自己的方式进行操作可以提高性能。这里有一些（就在我的脑海中）：

  1. 创建一个新的临时表将强制执行一个新的执行计划，其中包含新的统计数据和更好的基数估计，这通常会导致更好的计划。当/如果您使用 OPTION (RECOMPILE) 运行原始代码时，它是否仍然快 85%。

  2. 临时表是在 tempdb 中创建的...如果 tempdb 配置良好（例如在快速磁盘上，正确拆分，没有发生自动增长）并且视图引用磁盘上慢速/碎片/重击的对象，那也会影响事情.

  3. 您不会从临时表（或表变量）中读取锁定、阻塞、锁定升级、死锁等，而您可以使用视图，特别是如果它写得不好或经常受到冲击。

  4. 并行性可以极大地改进查询，您经常会看到使用串行执行计划运行数分钟的查询，使用并行执行计划可加快几秒或几毫秒。视图或基础数据结构中可能存在某些内容，例如作为视图的一部分或作为基础表之一上的约束和/或计算列调用的标量用户定义函数。首先转储到临时表中可能会规避这一点。

  5. 使用临时表可以消除您在引用视图时遇到的参数嗅探问题......

我可以继续，但这都是假设的。为了更准确地理解为什么您的查询变慢/变快，改进或倒退 - 学习阅读执行计划以及如何使用 STATISTICS TIME 和 STATISTICS IO。您可以通过运行跟踪、利用 perfmon、使用扩展事件和/或查询存储以及各种第三方工具（如 Idera、Redgate 和 SentryOne）来更深入地了解。从了解执行计划开始——实际执行计划（除了估计的计划）。

2）随着数据量的不断增加，这是否可能是可扩展的？

取决于你对第一个问题的回答。添加更多行会增加、减少还是保持这些性能增益不变。如果将数据翻倍导致您现在只看到 20% 的性能提升，请尝试使用三倍数据进行测试，看看收益减少的趋势是否会继续。相反，如果在将数据翻倍后您现在看到 200% 的性能提升，则添加更多数据并查看趋势是否继续。

3）这种方法有什么我可能没有考虑过的问题吗？

是的 - 吨。我没有时间讨论所有这些，但我会给你一个我最喜欢的“陷阱”——自动增长和爆炸性事务日志。但是插入会产生大量的事务日志活动，尤其是当恢复模式设置为 FULL Recovery 时。为了处理这个问题，我发现最好对我的插入进行批处理，通常使用允许我指定每批执行多少行的存储过程。

这是一个简单的示例，我介绍了如何批量修改数据，尤其是插入...

SET NOCOUNT ON;

-- Sample data 
------------------------------------------------------------------------------------------
DECLARE @source TABLE(id INT IDENTITY PRIMARY KEY, Col1 CHAR(1))
DECLARE @target TABLE(id INT IDENTITY PRIMARY KEY, Col1 CHAR(1))

INSERT @source (Col1)
SELECT TOP (1000) LEFT(NEWID(),1) -- random letter/number
FROM sys.all_columns

-- User-Defined input parameter:
------------------------------------------------------------------------------------------
DECLARE @batchsize INT = 100;

-- Routine
------------------------------------------------------------------------------------------
DECLARE @i INT = @batchsize
DECLARE @stage TABLE(id INT IDENTITY PRIMARY KEY, Col1 CHAR(1));

INSERT @stage(Col1) SELECT Col1 FROM @source;

WHILE EXISTS (SELECT 1 FROM @stage)
BEGIN
  INSERT @target (Col1)
  SELECT TOP (@batchsize) s.Col1
  FROM @stage AS s
  WHERE s.ID <= @batchsize;

  DELETE FROM @stage
  WHERE ID <= @batchsize;

  SELECT @batchsize += @batchsize;
END;

--SELECT * FROM @target;

推荐阅读