如何优化SQL GROUP BY去重求和查询？耗时查询性能优化咨询

我现在运行以下SQL查询，功能正常但耗时约20分钟，比同表其他操作慢很多，有没有更高效的实现方式？

/*将分组数据存入临时表 */

DROP TABLE IF EXISTS _TEMP_FORECAST_PARTITION_DATA

SELECT 
    SCENARIO, PERIOD, DEPARTMENT, ACCOUNT, CATEGORY, CURRENCY,
    PROJECT, PARTNERSHIP, EMPLOYEE, BOOKING_DATE, ORIGINAL_CURRENCY, 
    ANALYSIS_COMMENT, COMMENT_TYPE, NOTE, ALLOCATION_EXPLAINER, 
    PIPELINE_VALUATION_DATE, PIPELINE_PROBABILITY, TECH_ORIGINAL_USER,
    TECH_ORIGINAL_ORIGIN, TECH_ORIGINAL_DATEUPD, DEPARTMENT_VISIBILITY,
    SUM(AMOUNT) AS AMOUNT,
    SUM(ORIGINAL_AMOUNT) AS ORIGINAL_AMOUNT
INTO 
    _TEMP_FORECAST_PARTITION_DATA
FROM 
    AW_FCT_000002_000001
GROUP BY    
    SCENARIO, PERIOD, DEPARTMENT, ACCOUNT, CATEGORY, CURRENCY,
    PROJECT, PARTNERSHIP, EMPLOYEE, BOOKING_DATE, ORIGINAL_CURRENCY,
    ANALYSIS_COMMENT, COMMENT_TYPE, NOTE, ALLOCATION_EXPLAINER,
    PIPELINE_VALUATION_DATE, PIPELINE_PROBABILITY, TECH_ORIGINAL_USER,
    TECH_ORIGINAL_ORIGIN, TECH_ORIGINAL_DATEUPD, DEPARTMENT_VISIBILITY

/* 清空原表，并用临时表数据填充 */

TRUNCATE TABLE AW_FCT_000002_000001

INSERT INTO AW_FCT_000002_000001
(OID, SCENARIO, PERIOD, DEPARTMENT, ACCOUNT, CATEGORY, CURRENCY,
   PROJECT, PARTNERSHIP, EMPLOYEE, BOOKING_DATE, ORIGINAL_CURRENCY, ANALYSIS_COMMENT,
   COMMENT_TYPE, NOTE, ALLOCATION_EXPLAINER, PIPELINE_VALUATION_DATE,
   PIPELINE_PROBABILITY, AMOUNT, ORIGINAL_AMOUNT, 
   TECH_ORIGINAL_USER, TECH_ORIGINAL_ORIGIN, 
   TECH_ORIGINAL_DATEUPD, DEPARTMENT_VISIBILITY)

SELECT
    NEWID(), SCENARIO, PERIOD, DEPARTMENT, ACCOUNT, CATEGORY, CURRENCY,
    PROJECT, PARTNERSHIP, EMPLOYEE, BOOKING_DATE, ORIGINAL_CURRENCY,
    ANALYSIS_COMMENT, COMMENT_TYPE, NOTE, ALLOCATION_EXPLAINER,
    PIPELINE_VALUATION_DATE, PIPELINE_PROBABILITY, 
    AMOUNT, ORIGINAL_AMOUNT, TECH_ORIGINAL_USER,
    TECH_ORIGINAL_ORIGIN, TECH_ORIGINAL_DATEUPD, DEPARTMENT_VISIBILITY
FROM 
    _TEMP_FORECAST_PARTITION_DATA

运行统计信息 #

• 分组写入临时表阶段：CPU耗时1852172ms，总耗时544239ms（约9分钟），处理54830198行数据
• 清空原表阶段：CPU耗时79ms，总耗时75ms
• 插入回原表阶段：CPU耗时348640ms，总耗时405714ms（约6.7分钟），插入54830198行数据
• 整体完成时间：2024-09-10T06:00:59.0020584+01:00

表定义 #

USE [TGK04_DATA_002]
GO

/****** Object:  Table [dbo].[AW_FCT_000002_000001]    Script Date: 9/10/2024 3:06:40 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AW_FCT_000002_000001](
    [OID] [varchar](36) NOT NULL,
    [SCENARIO] [varchar](15) NULL,
    [PERIOD] [varchar](2) NULL,
    [DEPARTMENT] [varchar](30) NULL,
    [ACCOUNT] [varchar](30) NULL,
    [CATEGORY] [varchar](30) NULL,
    [CURRENCY] [varchar](5) NULL,
    [PROJECT] [varchar](30) NULL,
    [PARTNERSHIP] [varchar](30) NULL,
    [EMPLOYEE] [varchar](30) NULL,
    [BOOKING_DATE] [varchar](30) NULL,
    [AMOUNT] [numeric](27, 9) NULL,
    [ORIGINAL_AMOUNT] [numeric](27, 9) NULL,
    [ORIGINAL_CURRENCY] [varchar](3) NULL,
    [NOTE] [varchar](8000) NULL,
    [ANALYSIS_COMMENT] [varchar](255) NULL,
    [PIPELINE_VALUATION_DATE] [varchar](30) NULL,
    [COMMENT_TYPE] [varchar](30) NULL,
    [TECH_ORIGINAL_USER] [varchar](255) NULL,
    [TECH_ORIGINAL_ORIGIN] [varchar](255) NULL,
    [TECH_ORIGINAL_DATEUPD] [datetime] NULL,
    [ALLOCATION_EXPLAINER] [varchar](2000) NULL,
    [PIPELINE_PROBABILITY] [varchar](30) NULL,
    [DEPARTMENT_VISIBILITY] [varchar](30) NULL,
    [DAY_RATE] [numeric](27, 9) NULL,
    [DAY_RATE_ORIGINAL_CURRENCY] [numeric](27, 9) NULL,
    [DAYS] [numeric](27, 9) NULL,
    [PROVENIENZA] [varchar](80) NULL,
    [USERUPD] [varchar](255) NULL,
    [DATEUPD] [datetime] NULL,
    [EN_VERSION] [numeric](5, 0) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AW_FCT_000002_000001] ADD  DEFAULT ((0.000000000)) FOR [DAY_RATE]
GO

ALTER TABLE [dbo].[AW_FCT_000002_000001] ADD  DEFAULT ((0.000000000)) FOR [DAY_RATE_ORIGINAL_CURRENCY]
GO

ALTER TABLE [dbo].[AW_FCT_000002_000001] ADD  DEFAULT ((0.000000000)) FOR [DAYS]
GO

ALTER TABLE [dbo].[AW_FCT_000002_000001] ADD  DEFAULT ((0)) FOR [EN_VERSION]
GO

优化方案 #

1. 移除临时表中转，直接聚合写入 #

当前方案多了一次临时表IO操作，可直接聚合后写入原表：

/* 清空原表（TRUNCATE比DELETE快，适合全量替换场景） */
TRUNCATE TABLE AW_FCT_000002_000001;

/* 直接聚合后插入原表 */
INSERT INTO AW_FCT_000002_000001
(OID, SCENARIO, PERIOD, DEPARTMENT, ACCOUNT, CATEGORY, CURRENCY,
   PROJECT, PARTNERSHIP, EMPLOYEE, BOOKING_DATE, ORIGINAL_CURRENCY, ANALYSIS_COMMENT,
   COMMENT_TYPE, NOTE, ALLOCATION_EXPLAINER, PIPELINE_VALUATION_DATE,
   PIPELINE_PROBABILITY, AMOUNT, ORIGINAL_AMOUNT, 
   TECH_ORIGINAL_USER, TECH_ORIGINAL_ORIGIN, 
   TECH_ORIGINAL_DATEUPD, DEPARTMENT_VISIBILITY)
SELECT
    NEWID(), SCENARIO, PERIOD, DEPARTMENT, ACCOUNT, CATEGORY, CURRENCY,
    PROJECT, PARTNERSHIP, EMPLOYEE, BOOKING_DATE, ORIGINAL_CURRENCY,
    ANALYSIS_COMMENT, COMMENT_TYPE, NOTE, ALLOCATION_EXPLAINER,
    PIPELINE_VALUATION_DATE, PIPELINE_PROBABILITY, 
    SUM(AMOUNT) AS AMOUNT,
    SUM(ORIGINAL_AMOUNT) AS ORIGINAL_AMOUNT,
    TECH_ORIGINAL_USER, TECH_ORIGINAL_ORIGIN, 
    TECH_ORIGINAL_DATEUPD, DEPARTMENT_VISIBILITY
FROM 
    AW_FCT_000002_000001
GROUP BY    
    SCENARIO, PERIOD, DEPARTMENT, ACCOUNT, CATEGORY, CURRENCY,
    PROJECT, PARTNERSHIP, EMPLOYEE, BOOKING_DATE, ORIGINAL_CURRENCY,
    ANALYSIS_COMMENT, COMMENT_TYPE, NOTE, ALLOCATION_EXPLAINER,
    PIPELINE_VALUATION_DATE, PIPELINE_PROBABILITY, TECH_ORIGINAL_USER,
    TECH_ORIGINAL_ORIGIN, TECH_ORIGINAL_DATEUPD, DEPARTMENT_VISIBILITY;

2. 创建聚合专用非聚集索引 #

当前聚合需要全表扫描，创建包含分组字段和聚合字段的索引，让SQL Server直接通过索引完成计算：

CREATE NONCLUSTERED INDEX IX_AW_FCT_Aggregation
ON [dbo].[AW_FCT_000002_000001]
(
    SCENARIO, PERIOD, DEPARTMENT, ACCOUNT, CATEGORY, CURRENCY,
    PROJECT, PARTNERSHIP, EMPLOYEE, BOOKING_DATE, ORIGINAL_CURRENCY,
    ANALYSIS_COMMENT, COMMENT_TYPE, NOTE, ALLOCATION_EXPLAINER,
    PIPELINE_VALUATION_DATE, PIPELINE_PROBABILITY, TECH_ORIGINAL_USER,
    TECH_ORIGINAL_DATEUPD, DEPARTMENT_VISIBILITY
)
INCLUDE (AMOUNT, ORIGINAL_AMOUNT);

注：索引会占用存储空间，若仅偶尔执行聚合，可在操作完成后删除索引。

3. 提升插入性能 #

• 批量操作前执行SET NOCOUNT ON; SET XACT_ABORT ON;，减少日志开销和异常处理成本
• 临时禁用表上的触发器、非必要约束（操作完成后重新启用）
• 开启显式事务：BEGIN TRANSACTION; 执行插入后 COMMIT;，减少日志提交次数

4. 修正不合理数据类型 #

表中BOOKING_DATE、PIPELINE_VALUATION_DATE等日期字段用varchar存储，字符串分组比对远慢于日期类型。建议将这些字段改为date或datetime，既节省空间又提升排序分组效率。

5. 考虑分区表改造 #

若表数据量持续增长，可按PERIOD或SCENARIO字段分区，聚合时仅扫描目标分区，大幅减少数据扫描量。

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