主页 / dba / 问题 / 77703
Accepted
elty123
Asked: 2014-09-26 19:13:49 +0800 CST

高效传输大量（8400 万行）数据

  • 772

我有大约 8400 万行。其中所有这些都需要转移到同一台服务器上的单独数据库中，然后我删除以从源数据库中删除大约 6000 万行。

8400 万行都在同一个表中。仅该表就占了整个数据库的 90%。

所以...来源：8400 万行 -> 2400 万行目标：0 行 -> 8400 万行

源运行完全恢复模式，目标运行简单。

我想知道最有效的方法是什么？

计划A：

1) INSERT INTO 目标选择 * FROM 源

2) 截断源

3) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

B计划：

1) 将源数据库的备份还原为目标数据库

2) 删除除目标数据库所需的表之外的所有表

3) 截断源

4) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

计划C：

1) INSERT INTO 目标选择 * FROM 源

2) 删除源 WHERE keep_condition = 0

或者是其他东西？

谢谢

sql-server

5 个回答

  1. Best Answer

    我要补充一点，无论您决定如何处理，您都需要批量处理这些事务。我最近对链接的文章非常幸运，我很欣赏它利用索引的方式，而不是我看到的大多数批处理解决方案。

    即使是最低限度的日志记录，这些也是大事务，您可能会花费大量时间处理异常日志增长的后果（VLF、截断、调整大小等）。

    谢谢

    • 11

  2. “高效”可以应用于日志文件使用、I/O 性能、CPU 时间或执行时间。

    我会尝试实现最少记录的操作，从日志记录的角度来看这将是相当有效的。这应该可以为您节省一些执行时间作为奖励。如果您有 tempdb 空间，则以下内容可能对您有用。

    CREATE TABLE #temp;
ALTER source -> BULK_LOGGED recovery model

BEGIN TRANSACTION;

    INSERT INTO dest SELECT FROM source;
    INSERT INTO #temp SELECT FROM source WHERE keep_condition=1;
    TRUNCATE TABLE source;
    INSERT INTO source SELECT FROM #temp;

COMMIT TRANSACTION;

ALTER source -> FULL recovery model
DROP TABLE #temp;

    要进行最少记录的操作，必须满足许多条件，包括当前没有运行备份、数据库设置为BULK_LOGGED恢复模式，并且根据您的索引，目标表可能必须为空。从 SQL Server 2005 到 2008，其中一些行为也发生了变化（改进）。

    再说一次，在不知道您的表和数据的细节的情况下，您的任何其他选项都可能表现得更好。尝试使用

    SET STATISTICS IO ON;
SET STATISTICS TIME ON;

    ..看看哪个效果最好。

    编辑：执行批量记录操作时，如果您需要时间点还原功能并且您怀疑数据库中可能正在进行其他活动，请确保在操作之前和之后进行备份（完整或事务日志）在您的 ETL 作业运行的同时。

    不久前，我写了一篇关于最少记录操作的博客文章，其中有指向其他文章和文档的链接。

    • 3

  3. 为什么不是 BCP？

    1. 备份源数据库
    2. 将 sourcedb 更改为批量记录

    3. 打开命令提示符

    4. bcp server.sourcedb.table out Filename.flt -T -c

    5. bcp "SELECT * FROM sourcedb.table WHERE keep_condition = 1" queryout Filename2.flt -T -c

    6. bcp Server.destinationdb.table in Filename.flt -T -c -b1000

    7. 检查数据

    8. 从 SSMS 截断 sourcedb 表
    9. bcp server.sourcedb.table in Filename2.flt -T -c -b1000
    10. 将 sourcedb 改回完整
    • 1

  4. 不要认为您应该建议在没有完整数据库备份或 t-log 备份之前和之后的情况下更改恢复模式。BULK_LOGGED 恢复模型的特点之一是您将失去对包含大量记录操作的 t-log 进行时间点恢复的能力。经典场景：每晚完整备份，每小时 t-log 备份。您将恢复模式更改为批量记录并开始您的操作。出现问题并且事务回滚（或者您没有使用过）。但是，您不确定数据库中还发生了什么，因此您想恢复到一个已知的好点。

    什么时候可以恢复？不包含批量记录操作的最后一个每小时 t-log 备份，可能会丢失 n 分钟的事务。更改恢复模式之前的完整备份或 t-log 备份将创建一个回退点。您选择哪一个取决于您的 RTO。

    • 0

  5. 从表中删除分区是从表中删除大块数据的一种非常快速且资源高效的方法。如果此表以支持您的源/目标拆分的方式进行分区，答案将是恢复副本，从目标中删除冗余表和冗余分区，并从源中删除互补分区。

    然而，启用分区的成本可能会使这成为一项总体上更昂贵的操作。

    • 0

相关问题