防止 SSIS 中"INSERT IF NOT EXISTS"死锁

Question

我正在尝试将数据插入数据库，但首先我使用查找检查每一行是否存在，类似于此处建议的方法：如何防止SSIS从数据库中已存在的文件导入数据？

SELECT DISTINCT VALUES    // OleDb Source
         |
       LOOKUP             // If exists
         |                // No Match Output
  OLE DB DESTINATION      // Insert new records

我正在使用RetainSameConnection=True在我的工作流上启用事务。使用大约 10,000 行的默认缓冲区，当行传递到 OLE DB 目标时，目标INSERT将使用查找SELECT锁定。

我已经尝试了SET READ_COMMITTED_SNAPSHOT ON，这将起作用，但是查找期间的性能现在非常慢，我相信这是由于 RetainSameConnection 属性造成的，而且我无法判断 SSIS 甚至正在使用读取提交的快照隔离级别。我想过忽略目标上的失败，但我读到它会导致批量插入完全失败而不是按行失败。我也考虑过在所有读取中使用 NOLOCK，但它会将我所有的查找变成 SQL 查询。

源数据库可能会读取数百万行。有没有更好的方法来实现这一目标？

Answer 1

这个问题正在进入一个领域，答案将更多地基于偏好和经验，而不仅仅是一种容易定义的方式来做到这一点。可能会有很多答案，这些答案将显示可能适合您的不同方法，但其中任何一项的基础知识都将基于几件事。

1. 减少在数据流任务预缓存期间保存在查找中的行数

   一般来说，在数据流开始执行并根据查找的配置从源读取数据之前，将填充所有查找的值。如果以这种方式配置 SSIS，则无论隔离级别如何，查找和在表中插入行之间都不应存在任何争用。

   根据您上面所说的，我认为查找的性能可能不会因为您的配置而发生巨大变化，而是因为每次执行时缓存到查找中的数据量都在增加。

2. 更改查找模式以使用其他模式

   在大多数情况下，查找的最基本实现通常没问题。但是，如果性能是包执行的重要问题，那么可能还有其他更理想的方法来实现相同的目标。我在博客中谈到的其中之一是指使用合并联接作为标准查找的替代方法。这可能不适合您的情况，因为我设计了这个特定的策略来涵盖涉及大型数据集的极端情况，但希望它应该给你一些想法。

   我在下面的链接中对该模式进行了相当详细的演练

   https://web.archive.org/web/20140819083150/http://bigdatabigdave.info/archive/2013/02/15/alternate-ssis-lookup-pattern-merge-join/

Answer 2

我记得他们把所有的插入行都写到一个原始文件中。 然后是第二个数据流（在第一个数据流完成后），它将原始文件记录插入到目标表中。