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

Question

在INSERT proc中创建一个额外的步骤，将相关数据从源视图中选择到临时表中，然后使用它作为后续INSERT语句的源，这是一个好/可接受的做法吗？

背景:我正在努力提高ETL进程的性能，由于数据量呈指数增长，ETL进程已经大幅下降。

有c.1500个表，由存储过程(通过SSIS包)填充，这些存储过程使用少数模板和可定制的元数据创建。

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

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行的表中。

我在查询计划中发现了一些糟糕的估计，我试图通过：-Updating统计数据(完全扫描)来解析所使用的表/索引。底层视图中的-Amending逻辑-Creating新索引

在这些方面，我没有很强的背景，尤其是索引中比较复杂的部分--但取得了一些有限的成功。

但是，当我尝试在另一个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)我相信，在这种情况下，不仅仅是更新的执行计划导致了改进。我使用过这种方法的一些proc已经在现场运行了大约6周，并且一直比以前的proc的最佳运行表现要好得多。在测试中，我在运行每个方法之前清除了缓存，即使使用更新的stats/查询计划，旧版本也总是比较慢。

2) Tempdb被拆分为6个mdf文件，最近没有经历过自动增长。我们的服务器是在一个虚拟基础设施上，虽然我不会假装了解它的工作原理，但我们的it团队和存储供应商向我保证，所有DBs/ LUN都将基于活动转向更快的存储。虽然我怀疑这是可以改进的，但我不认为这是这个例子中的一个主要因素，而且我的手在改变设置上的任何东西方面都是相当有限的。

( 3)我尝试了一些修改意见的方法，但没有取得很大的成功，但我承认那里可能还有改进的余地。视图和底层表几乎只在一夜之间发生的日常ETL中使用，因此不应该有其他进程锁定和阻塞。

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

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

我的背景更多地是使用SQL进行分析，因此，虽然我现在正在学习如何读取查询计划、理解统计数据、设计索引和优化查询，但这是一个陡峭的学习曲线，我仍然是个新手。

虽然在其他情况下(尽管有简单的恢复模式)，我已经经历了爆炸性的查询日志问题，但大多数情况下并没有发生这种情况。不过，分批也值得一试。

当从视图中选择时，错误的行计数估计值在选择到临时表时比在大型持久化表中插入时影响更小，因此，先运行该步骤，然后使用准确的信息运行插入操作，效果很好.但我可能是从错误的角度看的。

Answer 1

您可以使用var表而不是临时表，在var表中可以定义索引，因此可以避免基础源视图问题。

试着做这样的事情：

DECLARE @SRC TABLE (
    IDX INT IDENTITY PRIMARY KEY, 
    col1 INT, 
    col2 INT, 
    col3 INT, 
    ...
    ...
    [date] DATETIME, 
    UNIQUE (COL1, [DATE], IDX) -- ADD AN INDEX ON COL1 + DATE
    )


SELECT *
INTO @SRC
FROM source_view

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

您还可以尝试以这种方式更改联接条件，这取决于表other_tbl上的索引。

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

根据服务器版本和硬件，优化器可以使用不同的计划，因此您还应该尝试使用其他索引，如：

DECLARE @SRC TABLE (
    IDX INT IDENTITY PRIMARY KEY, 
    col1 INT, 
    col2 INT, 
    col3 INT, 
    ...
    ...
    [date] DATETIME, 
    UNIQUE (COL1, [DATE], IDX) -- ADD AN INDEX ON COL1 + DATE
    UNIQUE ([DATE], IDX) -- ADD AN INDEX ON DATE
    )

最后提示，还可以在var表中提取筛选other_table空值的COL2行。

DECLARE @OTHER TABLE (
    IDX INT IDENTITY PRIMARY KEY, 
    col1 INT, 
    col2 INT, 
    col3 INT, 
    ...
    ...
    prev_date DATETIME, 
    UNIQUE (COL1, prev_date, IDX) -- ADD AN INDEX ON COL1 + prev_date
    UNIQUE (prev_date, IDX) -- ADD AN INDEX ON prev_date
    )


SELECT *
INTO @OTHER
FROM other_table
WHERE COL2 IS NULL

INSERT INTO dest_tbl
SELECT col1, col2, col3...
FROM @SRC s
LEFT JOIN @OTHER ot on s.col1 = ot.col1 AND s.date > ot.prev_date

Answer 2

我的问题是: 1)这种做法是否被认为是好的/可接受的做法？

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

然而，当我尝试在另一个联接/筛选器/插入之前添加一个步骤，将从视图到临时表的所有内容都插入到临时表中时，它就显着地提高了性能--有些表运行速度快了85%

重要的是要理解为什么要改进查询。在所有条件相同的情况下--将数据从表中提取到临时表中，然后从临时表中读取数据，理论上应该会慢一些，因为您正在做更多的事情，但是有许多原因可以解释为什么以提高性能的方式这样做。这里有一些(就在我头上)：

1. 创建一个新的临时表将强制一个新的执行计划，具有新的统计数据和更好的基数估计，这通常会导致更好的计划。当/如果您使用选项运行原始代码(重新编译)时，它仍然快85%吗？
2. 临时表是在tempdb中创建的。如果配置良好(例如，在快速磁盘上，正确分割，不发生自动增长)，并且视图引用磁盘上的对象，这也会影响到事情。
3. 您将不需要从临时表(或表变量)读取锁、阻塞、锁升级、死锁等，而可以使用视图来读取，特别是当它写得很糟糕或经常受到攻击时。
4. 并行性可以极大地改善查询，您经常会看到使用串行执行计划运行几分钟的查询，并行执行计划的速度可高达秒或毫秒。视图或底层数据结构中可能有一些内容，例如作为视图的一部分调用的标量用户定义的函数，或者作为底层表中的一个约束和/或计算列调用的函数。首先将数据倒入临时表可能会避免这种情况。
5. 使用temp表可以消除引用视图时遇到的参数嗅探问题.

我可以继续，但这都是假设。为了更准确地理解查询速度慢/速度快的原因，改进或倒退--学习如何阅读执行计划，以及如何使用统计时间和统计IO。通过运行跟踪、利用perfmon、使用扩展事件和/或查询存储以及各种第三方工具(如Idera、Redgate和SentryOne )，您可以获得更深入的信息。首先了解执行计划--实际的执行计划(除了估计的计划)。

2)随着数据量的持续增长，这是否可能是可扩展的？

取决于你对第一个问题的回答。添加更多行是否会增加、减少或保持这些性能增益不变。如果双倍的数据使你现在只看到20%的性能增益，试着用三倍的数据测试，看看回报下降的趋势是否继续。相反，如果在将数据翻一番之后，您现在看到了200%的性能增长，那么添加更多，看看这种趋势是否继续下去。

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

是的-吨。我没有时间检查所有这些信息，但是我会给您一个我最喜欢的“陷阱”--自动增长和开发事务日志。但是，插入将产生大量事务日志活动，特别是当恢复模型被设置为完全恢复时。为了处理这个问题，我发现最好对插入进行批处理，通常使用一个存储过程，允许我指定每批要执行多少行。

下面是一个快速的示例，我介绍了如何对数据修改进行批量处理，特别是插入。

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;