主页 / dba / 问题 / 83733
Accepted
Metaphor
Asked: 2014-11-27 09:19:51 +0800 CST

使用存档列获取新数据和更新数据

  • 772

我正在尝试优化一个 SSIS 包，该包从每天获取数百万条新记录的非常大的事务表中检索数据。数据是从数百台安装了 SQL Server 2008 Standard Edition 的现场服务器收集的。CDC，因此不能使用，因为它是企业功能。计划是这样的：

  1. 存档列，smallint - 0=new（默认约束），1=archived，2=updated（触发器）
  2. 默认约束 Archive=0
  3. 更新触发器存档=2
  4. 存档索引，时间戳

然后，SSIS 包将获取 Archive=0 的所有记录，将它们插入到目标中，然后获取 Archive=2 的所有记录并在目标中更新它们。

我需要帮助的是在 SSIS 过程完成写入新行或更新行后将存档列设置为 1。什么是设置所有已处理的行而不触及在初始选择之后可能已插入或更新的任何行的最有效方法？

sql-server trigger

4 个回答

  1. 由于您正在使用 SSIS 并捕获更改的行，因此您应该让 SSIS 捕获正在处理的行的 PK 值。包的最后一步应该是对源表进行有针对性的更新，[Archive]根据这些 PK 值将字段设置为 1。

    如果尚未完成，更新触发器需要将这些更改排除在将[Archive]字段设置为 2 之外。您可能需要以下内容：

    UPDATE tab
SET    tab.[Archive] = 2
FROM   dbo.MyTable tab
INNER JOIN INSERTED ...
INNER JOIN DELETED ...
WHERE INSERTED.Archive = DELETED.Archive
AND   DELETED.Archive IN (0, 1) -- Add 3 to this list; see below for why

    但是，那么您需要处理在 ETL 过程中更新行的情况。您仍然希望 ETL 下次更改。但是，如果 ETL 进程正在运行，它会[Archive]在最后将该字段更改为 1，然后就没有该行被更改的记录。那么你有点需要另一个值来表示“被ETLed”，也许是3。然后，类似于@billinkc 在对问题的评论中建议的内容，您可以UPDATE对OUTPUT子句执行如下操作：

    UPDATE TOP (@BatchSize) tab
SET    tab.[Archive] = 3
OUTPUT INSERTED.*
FROM   dbo.MyTable tab
WHERE  tab.[Archive] IN (0, 2)
ORDER BY tab.[Archive] ASC;

    然后，在 SSIS 包的最后一步，目标更新应该在两个 PK 值上AND [Archive] = 3。并在上面的触发器更新查询中添加3到列表中。IN这样，只有在 ETL 过程中未更改的记录才会更新为1，而在 ETL 过程中已更改的记录将显示为2，并将在下一次使用修改后的值再次被拾取。

    当然，您可能还想在[Archive]字段上创建过滤索引以支持获取标记为 0、2 或 3 的记录。因此：

    CREATE INDEX ... WHERE [Archive] IN (0, 2, 3);

    您想要获取标记为的记录，3因为假设这是每个现场服务器的单线程，标记为的3记录表示由于先前进程失败而导致的卡住记录。这也是为什么您不想将记录标记为1最初UPDATE...OUTPUT获取它们的时间；如果该过程失败，那么它们将被标记为已存档并且不再被拾取。

    话虽如此，您最好使用队列表而不是状态字段。您可以创建一个只有几个字段的简单表：

    QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTablePKfield  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

    然后触发器就变成了一个简单的INSERT, UPDATE触发器：

    INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.PKfield
  FROM   INSERTED ins;

    然后 SSIS 进程通过以下方式获取值：

    SELECT TOP (@BatchSize) etl.QueueID, tab.field1, tab.field2...
FROM   dbo.MyTableEtlQueue etl
INNER JOIN dbo.MyTable tab
        ON tab.PKfield = etl.MyTablePKfield
ORDER BY etl.QueueID ASC;

    SSIS 进程结束时会根据它在开始时提取[MyTableEtlQueue]的值对表进行简单的删除。[QueueID]

    在此模型中，您不需要大表上的过滤索引或状态列，这减少了已经很大的表的争用和大小。您无需担心传输中的记录，无论是更新还是流程失败。

    编辑：
    鉴于源表的新信息没有任何唯一性，只需要对此队列表方法进行微小更改：而不是仅跟踪 PKfield（显然不存在 ;-)，跟踪所有字段（或者至少所有需要ETL的）。

    所以队列表更改为：

    QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTableField1  INT NOT NULL,
MyTableField2  INT NOT NULL,
...
MyTableFieldN  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

    触发器更改为：

    INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.MyTableField1, ins.MyTableField2, ..., ins.MyTableField1N
  FROM   INSERTED ins;

    然后 SSIS 流程变为（注意现在缺少 JOIN 到源表）：

    SELECT TOP (@BatchSize) etl.QueueID, etl.MyTableField1, etl.MyTableField2, ...
FROM   dbo.MyTableEtlQueue etl
ORDER BY etl.QueueID ASC;

    这种队列表方法将源表（及其处理）与审计/ETL 进程（很像 CDC 所做的）隔离开来。这使您的应用程序可以毫无问题地与源表进行交互，并且没有占用数据页上更多空间的新列，并且在 ETL 处理时没有可能长时间运行的事务保留在源表上。

    • 2
  2. Best Answer

    如何使用OUTPUT虚拟表？正确设置您的事务隔离级别（快照/可序列化），以便您只看到流程开始时的行。

    将以下内容用于您的 OLE DB 源

    UPDATE T SET Archive = 1 OUTPUT DELETED.* FROM Table T WHERE T.Archive = 0;

    这会在一个不错的原子操作中更新所有内容，并产生将目标输出生成到数据流缓冲区的副作用。将其路由到您的目的地，它就完成了。漂亮整洁

    • 2

  3. 更改跟踪 (CT) 和更改数据捕获 (CDC) 内置在 SQL Server 中。CT 可以识别自您上次询问以来发生变化的行。CDC 可以提供自您上次询问以来所有数据更改的完整历史记录。两者都可以从 SSIS轻松操作。

    处理后无需每行重置标志。系统通过专门为此目的的附加隐藏表来处理所有这些。

    • 1

  4. 纯粹的 SSIS 解决方案是将插入和更新任务的数据流传递到缓存转换中。

    http://msdn.microsoft.com/en-us/library/bb895264.aspx

    如果它们出现在 1 个数据流中，那么您可以将它们发送到单个缓存，否则您将需要 2 个。

    然后，您可以使用查找转换在以后的数据流中针对这些缓存进行测试。我会重定向任何匹配的行以有效地丢弃它们，然后只使用不匹配的行。

    注意缓存存储在内存中，因此尽量保持内容精简，例如仅关键列。只要不耗尽物理内存，运行时性能会非常快（无 I/O）。

    这个解决方案可能是最容易构建和测试的，因为它不需要代码来实现。

    • 0

相关问题