主页 / dba / 问题 / 188391
Accepted
Christian4145
Asked: 2017-10-14 01:35:01 +0800 CST

用于暂存数据的 DROP、DELETE 和 TRUNCATE

  • 772

我需要将数据 (>> 1 GB) 导入 MS-SQL-Server。出于多种原因，我需要将此数据导入临时表（而不是直接导入目标）。从登台开始，数据被加载到目标表中。暂存表中的数据永远不会再次使用。我有几种方法：

  1. 将数据加载到 staging stable 中，将此数据标记为新数据，仅将标记的数据加载到目标中，取消标记数据。
  2. 删除 staging 中的所有数据，将数据加载到 staging 表中，将所有数据加载到目标中。
  3. 截断登台表，加载到登台，加载到目标
  4. 删除临时表，创建临时表，加载到临时表，加载到目标。
  5. 删除临时数据库，创建临时表，加载到临时表，加载到目标。

如果解决方案不受您的服务器角色的限制：推荐的解决方案是什么？哪个最健壮？

如果您不再需要数据，解决方案 1 很可能是无稽之谈。与 1 和 2 相比，解决方案 3 的性能要好得多。但是解决方案 4 和 3 之间呢？实际上，我更喜欢解决方案 5，因为我可以在加载过程中更改暂存过程中的几乎每个参数，但您需要删除和创建数据库的权限。这对于加载过程来说是相当多的。所以在大多数情况下，我使用 drop 并重新创建临时表。但这是推荐的方式吗？

sql-server t-sql

2 个回答

  1. “推荐方式”是在给定相关因素的情况下设计出最佳解决方案。

    选项 #1 和 #2 不太可能是理想的，因为它们会导致大量不必要的 I/O。#3 在大多数情况下是最好的，但就 I/O 而言，它与 #4 没有太大区别。所以在性能方面，#3 和#4 可以认为是相同的。与典型数据加载的 I/O 相比，删除/创建表而不是截断表的额外 I/O 是微不足道的。

    您的评论似乎表明您需要经常更改临时表，如果是这种情况，那么#4 可能最适合您。由于它是一个临时表，因此权限不太可能很复杂，并且如果它在一个专用的临时数据库中，其中临时帐户是唯一具有读/写的 ID，那么您甚至不需要在表时显式设置权限创建。

    选项#5 是非首发，除非您每次都有特定的理由要创建一个新数据库。

    • 2
  2. Best Answer

    推荐的解决方案是什么？

    • 选项 3 如果您可以更改要插入的数据库的恢复模型，否则创建一个暂存数据库并将其进行批量或简单恢复。
    • 根据 sql server 的版本，您甚至可以将内存优化表用于使用 schema_ONLY 创建的暂存。

    哪个最健壮？- 测试它适合您的要求。不要删除整个数据库或表 - 无论如何，当您重用它时，删除和重新创建 .. 有什么意义。

    最近我需要将 3+ GB 的 csv 文件导入 sql server。下面是我在使用 SSD 和 16 核和 256GB RAM 的物理机（在 VM 上稍微少一点）上实现 100K 行/秒（YMMV，由于硬件和环境差异）所做的工作。

    • 使用BULK INSERT .. WITH (TABLOCK) & TF 610 & 适当的批量和大容量日志恢复模型，并将数据插入暂存表（根本没有索引）或使用SSIS - 使用快速加载。
    • 根据用于压缩的 sql server 版本，您可以使用列存储索引（这可能会减慢一点……但不会显着）。
    • 为获得最佳性能，您可以使用 BULK INSERT 拆分文件并同时插入。
    • 如果您无法为您尝试插入的数据库切换到 BULK Logged 或 Simple 恢复模式，您可以拥有一个带有临时表的单独数据库。
    • 如果数据很大，则插入不同的分区并在加载完成时使用分区和 SWITCH 分区。从 sql server 2016 开始，您可以截断单个分区。这样，您可以创建一个动态脚本来使用 BULK INSERT 插入不同的分区，然后切换分区。
    • 确保预先设置数据和日志文件的大小以使自动增长事件最少，电源计划设置为高性能并启用即时文件初始化。

    有关其他提示，请参阅我的回答：

    • 1

相关问题