sql - 使用超过 2000 万条记录的 SSIS 的最佳增量加载方法

Question

需要什么 :我需要从 oracle 增量加载 2500 万条记录到 SQL Server 2012。它需要在包中具有更新、删除、新记录功能。 oracle 数据源一直在变化。

我有什么:我以前做过很多次，但没有超过 1000 万条记录。首先，我有一个 [执行 SQL 任务]，它被设置为获取 [最大修改日期] 的结果集。然后我有一个查询，它只从 [ORACLE SOURCE] > [最大修改日期] 中提取数据，并对我的目标表进行查找。

我有 [ORACLE Source] 连接到 [Lookup-Destination table]，查找设置为 NO CACHE 模式，如果我使用部分或完全缓存模式我会出错，因为我假设 [ORACLE Source] 总是在变化。 [Lookup] 然后连接到 [Conditional Split]，我将在其中输入如下所示的表达式。

(REPLACENULL(ORACLE.ID,"") != REPLACENULL(Lookup.ID,"")) 
|| (REPLACENULL(ORACLE.CASE_NUMBER,"") 
!= REPLACENULL(ORACLE.CASE_NUMBER,""))

然后我会将 [Conditional Split] 输出到暂存表中的行。然后我添加一个 [执行 SQL 任务] 并使用以下查询对 DESTINATION-TABLE 执行更新:

 UPDATE Destination
 SET SD.CASE_NUMBER =UP.CASE_NUMBER,
     SD.ID           =   UP.ID,
 From Destination SD
 JOIN  STAGING.TABLE UP
    ON UP.ID = SD.ID

问题:这会变得非常缓慢，需要很长时间，而且它一直在运行。我怎样才能改善时间并让它发挥作用？我应该使用缓存转换吗？我应该使用合并语句吗？

当它是数据列时，如何在条件拆分中使用表达式 REPLACENULL？我会使用类似的东西:

(REPLACENULL(ORACLE.LAST_MODIFIED_DATE,"01-01-1900 00:00:00.000") 
 != REPLACENULL(Lookup.LAST_MODIFIED_DATE," 01-01-1900 00:00:00.000")) 

下图:

Answer 1

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

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 .您的表中应该始终有一个“上次更新”列，以帮助审核和调试。

这是一种插入/更新方法。还有其他数据加载方法，例如窗口化(选择要完全删除和重新加载的数据的尾随窗口)，但该方法取决于您的系统如何工作以及您是否可以对数据做出假设(即源中发布的数据永远不会改变了)

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