慢SQL查询优化求助：数据量不大但执行耗时2-2分20秒

算力阿峰

2026-8-7

SQL查询性能优化方案

核心优化方向

1. 替换逐行执行的关联子查询

原SQL中3个SELECT COUNT(...)子查询会对每行结果重复执行，改用CTE预聚合后JOIN，大幅减少查询次数：

WITH ActionItemStats AS (
    SELECT
        vr.id AS visitReportId,
        COUNT(CASE WHEN ai.createdAt > vr.startDate AND ai.createdAt < vr.finalized THEN 1 END) AS created_count,
        COUNT(DISTINCT CASE WHEN aiu.createdAt > vr.startDate AND aiu.createdAt < vr.finalized THEN ai.id END) AS updated_count,
        COUNT(CASE WHEN ai.closed > vr.startDate AND ai.closed < vr.finalized THEN 1 END) AS closed_count
    FROM [DB].[SCHEMA].VisitReports vr
    LEFT JOIN [DB].[SCHEMA].ActionItems ai ON ai.siteId = vr.siteId AND ai.deletedAt IS NULL
    LEFT JOIN [DB].[SCHEMA].ActionItemUpdates aiu ON ai.id = aiu.actionItemId
        AND aiu.deletedAt IS NULL
        AND aiu.updateType IS NULL
    WHERE vr.questionnaireVersion >= 7
        AND vr.visitType = 'ongoing'
        AND vr.finalized IS NOT NULL
        AND vr.endDate > '2022-01-01' AND vr.endDate < '2022-11-11'
    GROUP BY vr.id, vr.startDate, vr.finalized
)

2. 合并多次Answers表LEFT JOIN

原SQL重复LEFT JOIN同一个Answers表，改用单次JOIN + 条件聚合，减少表连接开销：

AnswersAgg AS (
    SELECT
        reportId,
        MAX(CASE WHEN questionId = 'R3' THEN data END) AS R2_data,
        MAX(CASE WHEN questionId = 'SDR3' THEN data END) AS SDR5_data,
        MAX(CASE WHEN questionId = 'SDR3.3' THEN data END) AS SDR5_3_data,
        MAX(CASE WHEN questionId = 'SDR10' THEN data END) AS SDR6_data,
        MAX(CASE WHEN questionId = 'SDR4' THEN data END) AS SDR7_data
    FROM [DB].[SCHEMA].Answers
    WHERE questionId IN ('R3','SDR3','SDR3.3','SDR10','SDR4')
    GROUP BY reportId
)

3. 优化区域映射逻辑

用临时表（而非表变量）存储国家-区域映射，临时表具备统计信息，优化器可生成更优执行计划：

CREATE TABLE #RegionMap (
    Country NVARCHAR(100) PRIMARY KEY,
    Region NVARCHAR(50)
);
INSERT INTO #RegionMap (Country, Region)
VALUES
('Australia', 'APAC'), ('Hong Kong', 'APAC'), ('India', 'APAC'), ('Korea, Republic of', 'APAC'),
('Malaysia', 'APAC'), ('New Zealand', 'APAC'), ('Philippines', 'APAC'), ('Singapore', 'APAC'),
('Taiwan', 'APAC'), ('Thailand', 'APAC'), ('Viet Nam', 'APAC'),
('China', 'China'),
('Austria', 'EMEA'), ('Belgium', 'EMEA'), ('Bulgaria', 'EMEA'), ('Croatia', 'EMEA'),
('Czech Republic', 'EMEA'), ('Denmark', 'EMEA'), ('Estonia', 'EMEA'), ('Finland', 'EMEA'),
('France', 'EMEA'), ('Germany', 'EMEA'), ('Greece', 'EMEA'), ('Hungary', 'EMEA'),
('Ireland', 'EMEA'), ('Israel', 'EMEA'), ('Italy', 'EMEA'), ('Latvia', 'EMEA'),
('Lebanon', 'EMEA'), ('Lithuania', 'EMEA'), ('Netherlands', 'EMEA'), ('Norway', 'EMEA'),
('Poland', 'EMEA'), ('Portugal', 'EMEA'), ('Romania', 'EMEA'), ('Russian Federation', 'EMEA'),
('Saudi Arabia', 'EMEA'), ('Serbia', 'EMEA'), ('South Africa', 'EMEA'), ('Spain', 'EMEA'),
('Sweden', 'EMEA'), ('Switzerland', 'EMEA'), ('Turkey', 'EMEA'), ('Ukraine', 'EMEA'),
('United Arab Emirates', 'EMEA'), ('United Kingdom', 'EMEA'),
('Japan', 'Japan'),
('Argentina', 'Latin America'), ('Brazil', 'Latin America'), ('Chile', 'Latin America'),
('Colombia', 'Latin America'), ('Costa Rica', 'Latin America'), ('Guatemala', 'Latin America'),
('Mexico', 'Latin America'), ('Peru', 'Latin America'), ('Puerto Rico', 'Latin America'),
('Canada', 'North America'), ('United States', 'North America');

4. 添加针对性索引

根据查询的过滤、连接条件创建以下索引，提升数据检索效率：

VisitReports：(endDate, siteId, finalized, startDate, questionnaireVersion, visitType)
Answers：(reportId, questionId, data)
ActionItems：(siteId, deletedAt, createdAt, closed)
ActionItemUpdates：(actionItemId, deletedAt, updateType, createdAt)
Sites：(id, protocolId, country)
Protocols：(id, number)

完整优化后SQL示例

CREATE TABLE #RegionMap (
    Country NVARCHAR(100) PRIMARY KEY,
    Region NVARCHAR(50)
);
INSERT INTO #RegionMap (Country, Region)
VALUES
('Australia', 'APAC'), ('Hong Kong', 'APAC'), ('India', 'APAC'), ('Korea, Republic of', 'APAC'),
('Malaysia', 'APAC'), ('New Zealand', 'APAC'), ('Philippines', 'APAC'), ('Singapore', 'APAC'),
('Taiwan', 'APAC'), ('Thailand', 'APAC'), ('Viet Nam', 'APAC'),
('China', 'China'),
('Austria', 'EMEA'), ('Belgium', 'EMEA'), ('Bulgaria', 'EMEA'), ('Croatia', 'EMEA'),
('Czech Republic', 'EMEA'), ('Denmark', 'EMEA'), ('Estonia', 'EMEA'), ('Finland', 'EMEA'),
('France', 'EMEA'), ('Germany', 'EMEA'), ('Greece', 'EMEA'), ('Hungary', 'EMEA'),
('Ireland', 'EMEA'), ('Israel', 'EMEA'), ('Italy', 'EMEA'), ('Latvia', 'EMEA'),
('Lebanon', 'EMEA'), ('Lithuania', 'EMEA'), ('Netherlands', 'EMEA'), ('Norway', 'EMEA'),
('Poland', 'EMEA'), ('Portugal', 'EMEA'), ('Romania', 'EMEA'), ('Russian Federation', 'EMEA'),
('Saudi Arabia', 'EMEA'), ('Serbia', 'EMEA'), ('South Africa', 'EMEA'), ('Spain', 'EMEA'),
('Sweden', 'EMEA'), ('Switzerland', 'EMEA'), ('Turkey', 'EMEA'), ('Ukraine', 'EMEA'),
('United Arab Emirates', 'EMEA'), ('United Kingdom', 'EMEA'),
('Japan', 'Japan'),
('Argentina', 'Latin America'), ('Brazil', 'Latin America'), ('Chile', 'Latin America'),
('Colombia', 'Latin America'), ('Costa Rica', 'Latin America'), ('Guatemala', 'Latin America'),
('Mexico', 'Latin America'), ('Peru', 'Latin America'), ('Puerto Rico', 'Latin America'),
('Canada', 'North America'), ('United States', 'North America');

WITH ActionItemStats AS (
    SELECT
        vr.id AS visitReportId,
        COUNT(CASE WHEN ai.createdAt > vr.startDate AND ai.createdAt < vr.finalized THEN 1 END) AS created_count,
        COUNT(DISTINCT CASE WHEN aiu.createdAt > vr.startDate AND aiu.createdAt < vr.finalized THEN ai.id END) AS updated_count,
        COUNT(CASE WHEN ai.closed > vr.startDate AND ai.closed < vr.finalized THEN 1 END) AS closed_count
    FROM [DB].[SCHEMA].VisitReports vr
    LEFT JOIN [DB].[SCHEMA].ActionItems ai ON ai.siteId = vr.siteId AND ai.deletedAt IS NULL
    LEFT JOIN [DB].[SCHEMA].ActionItemUpdates aiu ON ai.id = aiu.actionItemId
        AND aiu.deletedAt IS NULL
        AND aiu.updateType IS NULL
    WHERE vr.questionnaireVersion >= 7
        AND vr.visitType = 'ongoing'
        AND vr.finalized IS NOT NULL
        AND vr.endDate > '2022-01-01' AND vr.endDate < '2022-11-11'
    GROUP BY vr.id, vr.startDate, vr.finalized
),
AnswersAgg AS (
    SELECT
        reportId,
        MAX(CASE WHEN questionId = 'R3' THEN data END) AS R2_data,
        MAX(CASE WHEN questionId = 'SDR3' THEN data END) AS SDR5_data,
        MAX(CASE WHEN questionId = 'SDR3.3' THEN data END) AS SDR5_3_data,
        MAX(CASE WHEN questionId = 'SDR10' THEN data END) AS SDR6_data,
        MAX(CASE WHEN questionId = 'SDR4' THEN data END) AS SDR7_data
    FROM [DB].[SCHEMA].Answers
    WHERE questionId IN ('R3','SDR3','SDR3.3','SDR10','SDR4')
    GROUP BY reportId
)
SELECT
    ISNULL(rm.Region, '?') AS 'Region',
    s.country AS 'Country',
    p.number AS 'Protocol #',
    s.number AS 'Site #',
    vr.finalized AS 'Finalized date',
    vr.startDate AS 'Start date',
    vr.endDate AS 'End date',
    vr.visitMode AS 'Mode',
    LEN(REPLACE(vr.remoteVisitDates, ',', '')) / 13 AS '# of remote dates',
    vr.onSiteVisitDates AS 'On site dates',
    vr.remoteVisitDates AS 'Remote dates',
    vr.visitId AS 'Visit ID',
    CASE
        WHEN aa.R2_data LIKE '%choice_yes%' THEN 'Yes'
        WHEN aa.R2_data LIKE '%choice_no%' THEN 'No'
        ELSE '-'
    END AS 'R2',
    CASE
        WHEN aa.SDR5_data LIKE '%choice_yes%' THEN 'Yes'
        WHEN aa.SDR5_data LIKE '%choice_no%' THEN 'No'
        ELSE '-'
    END AS 'SDR5',
    CASE
        WHEN aa.SDR5_3_data LIKE '%choice_yes%' THEN 'Yes'
        WHEN aa.SDR5_3_data LIKE '%choice_no%' THEN 'No'
        ELSE '-'
    END AS 'SDR5_3',
    CASE
        WHEN aa.SDR6_data LIKE '%choice_yes%' THEN 'Yes'
        WHEN aa.SDR6_data LIKE '%choice_no%' THEN 'No'
        ELSE '-'
    END AS 'SDR6',
    CASE
        WHEN aa.SDR7_data LIKE '%choice_yes%' THEN 'Yes'
        WHEN aa.SDR7_data LIKE '%choice_no%' THEN 'No'
        WHEN aa.SDR7_data LIKE '%choice_n_a%' THEN 'N/A'
        ELSE '-'
    END AS 'SDR7',
    COALESCE(aist.created_count, 0) AS '# created ActionItems',
    COALESCE(aist.updated_count, 0) AS '# updated ActionItems',
    COALESCE(aist.closed_count, 0) AS '# closed ActionItems'
FROM [DB].[SCHEMA].VisitReports vr
JOIN [DB].[SCHEMA].Sites s ON vr.siteId = s.id
JOIN [DB].[SCHEMA].Protocols p ON s.protocolId = p.id
LEFT JOIN #RegionMap rm ON s.country = rm.Country
LEFT JOIN AnswersAgg aa ON vr.id = aa.reportId
LEFT JOIN ActionItemStats aist ON vr.id = aist.visitReportId
WHERE vr.questionnaireVersion >= 7
    AND vr.visitType = 'ongoing'
    AND vr.finalized IS NOT NULL
    AND vr.endDate > '2022-01-01' AND vr.endDate < '2022-11-11'
ORDER BY vr.finalized;

DROP TABLE #RegionMap;

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

慢SQL查询优化求助：数据量不大但执行耗时2-2分20秒

核心优化方向 #

1. 替换逐行执行的关联子查询 #

2. 合并多次Answers表LEFT JOIN #

3. 优化区域映射逻辑 #

4. 添加针对性索引 #