使用SSIS的最佳增量加载方法，包含超过2000万条记录

3 回答

• 3

  对于较大的数据集通常更快的模式是将源数据加载到本地登台表中，然后使用如下的查询来标识新记录：

  SELECT column1,column 2
  FROM StagingTable SRC
  WHERE NOT EXISTS (
      SELECT * FROM TargetTable TGT 
      WHERE TGT.MatchKey = SRC.MatchKey
  )
  

  然后，您只需将该数据集提供给插入：

  INSERT INTO TargetTable (column1,column 2)
  SELECT column1,column 2
  FROM StagingTable SRC
  WHERE NOT EXISTS (
      SELECT * FROM TargetTable TGT 
      WHERE TGT.MatchKey = SRC.MatchKey
  )
  

  更新看起来像这样：

  UPDATE TGT
  SET 
  column1 = SRC.column1,
  column2 = SRC.column2,
  DTUpdated=GETDATE()
  FROM TargetTable TGT
  WHERE EXISTS (
      SELECT * FROM TargetTable SRC
      WHERE TGT.MatchKey = SRC.MatchKey
  )
  

  请注意附加列 DTUpdated . 您应始终在表中包含'last updated'列以帮助进行审核和调试 .

  这是一种INSERT / UPDATE方法 . 还有其他数据加载方法，例如窗口化（选择要完全删除和重新加载的数据的尾随窗口），但方法取决于系统的工作方式以及是否可以对数据做出假设（即源中的发布数据永远不会出现改变）

  您可以将单独的 INSERT 和 UPDATE 语句压缩为单个 MERGE 语句，尽管它变得非常庞大，并且我遇到了性能问题，并且还有其他记录的问题 MERGE

  回复于 2024-05-08T00:44:56+08:00
• 1

  不幸的是，没有一种好方法可以做你想做的事情 . SSIS有一些控件和记录的方法来执行此操作，但是当您开始处理大量数据时，它们发现它们不能正常工作 .

  在以前的工作中，我们有类似的事情需要我们做 . 我们需要将源系统的医疗索赔更新到另一个系统，类似于您的设置 . 很长一段时间，我们只是截断目的地的一切，每晚重建 . 我想我们每天都在做超过2500万行 . 如果您能够在相当长的时间内将所有行从Oracle传输到SQL，那么截断和重新加载可能是一种选择 .

  然而，随着我们的数量增长，我们最终不得不摆脱这种局面 . 我们试图按照你正在尝试的方式做一些事情，但从来没有得到任何我们满意的东西 . 我们最终得到了一种非传统的过程 . 首先，每个医疗索赔都有一个唯一的数字标识符 . 其次，每当在源系统中更新医疗索赔时，个别索赔上的增量ID也会增加 .

  我们流程的第一步是提出任何新的医疗索赔或已发生变化的索赔 . 我们可以很容易地确定这一点，因为唯一ID和"change ID"列都在源和目标中都被索引 . 这些记录将直接插入目标表 . 第二步是我们"deletes"，我们在记录上用逻辑标志处理 . 对于实际删除，其中记录存在于目标但不再存在于源中，我认为通过从源系统中选择 DISTINCT 索引号并将它们放在SQL端的临时表中实际上最快 . 然后，我们只是进行了 LEFT JOIN 更新，将缺少的声明设置为逻辑删除 . 我们做了类似于我们的更新：如果我们的原始Lookup带来了更新版本的声明，我们会逻辑地删除旧的 . 我们经常会清理逻辑删除并实际删除它们，但由于逻辑删除指示符已编入索引，因此不需要太频繁地执行 . 即使逻辑删除的记录数以千万计，我们也从未见过太多的性能损失 .

  随着我们的服务器负载和数据源卷的变化，这个过程总是不断发展，我怀疑你的流程也是如此 . 因为每个系统和设置都不同，所以对我们有用的一些东西可能不适合你，反之亦然 . 我知道我们的数据中心相对较好而且我们使用了一些愚蠢的快速闪存，因此截断和重新加载对我们来说非常长时间 . 在传统存储上可能不是这样，在传统存储中，您的数据互连速度不是很快，或者您的服务器没有位于同一位置 .

  在设计流程时，请记住删除是您可以执行的更昂贵的操作之一，其次是更新和非批量插入 .

  回复于 2024-05-08T00:44:56+08:00
• 1

  我假设您在oracle表中有一些像（pk）列这样的标识 .

  1从目标数据库（SQL Server one）获取最大标识（业务密钥）

  2创建两个数据流

  a）从oracle中仅提取数据> max identity并直接将它们放入Destination . （因为这些是新记录） .

  b）获取所有记录<最大身份和更新日期>最后加载将它们放入临时（临时）表（因为这是更新的数据）

  3更新目标表，其中包含临时表记录中的记录（在步骤b中创建）

  回复于 2024-05-08T00:44:56+08:00