Azure DWH中75亿条360列数据归档拷贝耗时过长，求优化方案

针对你75亿条、360列数据的归档场景，以下是几个能显著提升操作效率的实用方案：

1. 基于分区的并行处理（最优方案） #

如果源表[dwh].[StagingxyzDetail]尚未按fiscalmonth分区，先给表做分区改造；已分区的话直接按分区批量执行归档：

• 按单个fiscalmonth分区单独执行归档操作，利用Azure DWH的MPP并行架构分散计算压力，避免单条大查询占满集群资源
• 示例代码：

-- 逐个分区归档（以202106、202105为例）
SELECT * INTO [Archive].[StagingxyzDetail_20220718_202106]
FROM [dwh].[StagingxyzDetail]
WHERE fiscalmonth = 202106;

SELECT * INTO [Archive].[StagingxyzDetail_20220718_202105]
FROM [dwh].[StagingxyzDetail]
WHERE fiscalmonth = 202105;

• 后续可将这些分表合并为一个归档表，或保留分区结构以方便后续查询。

2. 用CTAS替代SELECT INTO，自定义存储策略 #

默认SELECT INTO会使用ROUND_ROBIN分布和行存储，效率较低。改用CREATE TABLE AS SELECT (CTAS)，可以自定义分布键、索引和压缩策略：

• 指定和源表一致的哈希分布键，减少跨节点数据移动
• 启用聚集列存储索引，大表批量写入和压缩效率远高于行存储
• 开启归档级压缩，节省存储空间的同时不影响批量写入
• 示例代码：

CREATE TABLE [Archive].[StagingxyzDetail_20220718]
WITH (
    DISTRIBUTION = HASH(transaction_id), -- 和源表保持相同的分布键
    CLUSTERED COLUMNSTORE INDEX,
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
)
AS SELECT * FROM [dwh].[StagingxyzDetail] WHERE fiscalmonth < 202107;

3. 高效删除源表数据 #

不要一次性删除75亿条记录，优先用分区切换（已分区场景），几乎零耗时；未分区则采用批量删除：

分区切换方式（最快） #

-- 创建与源表结构、分区规则一致的临时表
CREATE TABLE [dwh].[StagingxyzDetail_Temp]
WITH (
    DISTRIBUTION = HASH(transaction_id),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION (fiscalmonth RANGE RIGHT FOR VALUES (202107))
) AS SELECT TOP 0 * FROM [dwh].[StagingxyzDetail];

-- 将需归档的分区切换到临时表
ALTER TABLE [dwh].[StagingxyzDetail] SWITCH PARTITION 1 TO [dwh].[StagingxyzDetail_Temp] PARTITION 1;

-- 删除临时表完成数据移除
DROP TABLE [dwh].[StagingxyzDetail_Temp];

批量删除方式（未分区场景） #

WHILE 1=1
BEGIN
    DELETE TOP (10000000) FROM [dwh].[StagingxyzDetail] WHERE fiscalmonth < 202107;
    IF @@ROWCOUNT = 0 BREAK;
END

每次删除1000万条左右，避免长时间锁表和资源耗尽。

4. 辅助优化措施 #

• 更新统计信息：确保fiscalmonth字段的统计信息最新，让优化器生成最优执行计划

  UPDATE STATISTICS [dwh].[StagingxyzDetail] (fiscalmonth);
  

• 临时提升DWU：操作前临时调高DWU等级（比如从DW1000升到DW3000c），利用更多计算资源加速并行处理，完成后再降回原等级节省成本。

内容的提问来源于stack exchange，提问作者TomG