如何优化多SELECT语句的SQL查询？——30天事务等待时间统计慢查询优化咨询

你的核心问题是重复执行30次几乎相同的查询来统计每日指标，再加上表变量无索引、低效的日期字符串操作，导致700万条数据下性能拉胯。下面是几个针对性的优化方案，能大幅提升执行速度：

一、核心瓶颈拆解 #

• 重复查询表变量：30次独立查询都要全表扫描@transactionsList，累计扫描量达到21亿条记录，这是最大的性能杀手。
• 低效日期处理：大量用SUBSTRING、CONCAT拼接日期字符串，不仅慢，还容易引发隐式类型转换，无法利用索引。
• 表变量无索引：表变量默认没有统计信息，SQL Server无法生成最优执行计划，全表扫描是必然结果。

二、针对性优化方案 #

1. 生成日期维度，一次性计算所有日期的指标 #

用递归CTE生成需要统计的30天日期列表，然后通过关联逻辑把单条事务映射到对应的统计日期，避免重复查询。

2. 替换低效的日期字符串操作 #

把那些字符串拼接的日期处理换成原生日期函数，比如直接用CAST转换日期时间组合，避免字符串操作的额外开销。

3. 用临时表替代表变量，并添加索引 #

临时表支持创建索引，也有完整的统计信息，能让查询引擎生成更高效的执行计划。

完整优化后代码示例 #

-- 1. 生成30天的日期列表（这里以2022-06-01到2022-06-30为例）
WITH DateRange AS (
    SELECT CAST('2022-06-01' AS DATE) AS reportDate
    UNION ALL
    SELECT DATEADD(DAY, 1, reportDate)
    FROM DateRange
    WHERE reportDate < '2022-06-30'
)
-- 2. 创建临时表存储过滤后的事务数据，并添加索引
SELECT 
    t.id,
    t.currentAssignedQueueId,
    q.accessPointId AS queueAccessPointId,
    q.name AS queueName,
    q.reportCategory AS queueReportCategory,
    q.priority AS queuePriority,
    q.organizationHierarchyId AS queueOrganizationHierarchyId,
    t.receivedDate,
    t.claimedDT,
    t.transactionStatus,
    t.receivedDateUTC,
    t.appointmentDT,
    t.createdDT
INTO #transactionsList
FROM Transactions t
LEFT JOIN Queues q ON t.currentAssignedQueueId = q.Id
WHERE t.consumerId = '66458f4a-b3d4-4f80-93d4-5aa3ea123249'
  AND t.isActive = 1
  AND t.receivedDate <= '2022-06-30'
  AND (t.claimedDT >= '2022-06-02' OR t.transactionStatus = 'WaitingAssignment');

-- 添加关键索引，覆盖查询所需字段，避免回表
CREATE NONCLUSTERED INDEX IX_Transactions_Queue_Dates ON #transactionsList (
    currentAssignedQueueId,
    receivedDate
) INCLUDE (
    queueAccessPointId, queueName, queueReportCategory, queuePriority,
    queueOrganizationHierarchyId, claimedDT, transactionStatus, receivedDateUTC,
    appointmentDT, createdDT, id
);

-- 3. 一次性计算所有日期的统计指标
SELECT 
    dr.reportDate,
    tl.currentAssignedQueueId,
    tl.queueAccessPointId,
    tl.queueName,
    tl.queueReportCategory,
    tl.queuePriority,
    tl.queueOrganizationHierarchyId,
    COUNT(tl.id) AS totalCasesWaiting,
    SUM(CASE WHEN wt.waitTimeMinutes > 0 THEN wt.waitTimeMinutes ELSE 0 END) AS sumWaitTimeMinutes,
    MAX(CASE WHEN wt.waitTimeMinutes > 0 THEN wt.waitTimeMinutes ELSE 0 END) AS maxWaitTimeMinutes
FROM DateRange dr
CROSS JOIN #transactionsList tl
CROSS APPLY (
    -- 计算单条事务在当前reportDate下的等待时间
    SELECT 
        CASE 
            WHEN (tl.receivedDateUTC > '0001-01-01' OR tl.receivedDate > '0001-01-01') 
                 AND (tl.appointmentDT IS NULL OR tl.appointmentDT < '2022-09-28T12:58:47')
            THEN CAST(DATEDIFF(MINUTE, 
                -- 修正开始时间计算，用原生日期函数替代字符串拼接
                CASE 
                    WHEN tl.receivedDateUTC > '0001-01-01' THEN tl.receivedDateUTC
                    ELSE CAST(CONCAT(tl.receivedDate, ' ', CAST(tl.createdDT AS TIME)) AS DATETIME2)
                END,
                -- 修正结束时间计算，避免字符串操作
                CASE 
                    WHEN tl.claimedDT > '0001-01-01' AND tl.claimedDT < DATEADD(DAY, 1, dr.reportDate) 
                         AND tl.transactionStatus != 'WaitingAssignment'
                    THEN tl.claimedDT
                    ELSE DATEADD(SECOND, -1, DATEADD(DAY, 1, dr.reportDate))
                END
            ) AS BIGINT)
            ELSE 0
        END AS waitTimeMinutes
) wt
-- 过滤符合当前reportDate的事务
WHERE tl.receivedDate <= dr.reportDate
  AND (tl.claimedDT >= DATEADD(DAY, 1, dr.reportDate) OR tl.transactionStatus = 'WaitingAssignment')
GROUP BY 
    dr.reportDate,
    tl.currentAssignedQueueId,
    tl.queueAccessPointId,
    tl.queueName,
    tl.queueReportCategory,
    tl.queuePriority,
    tl.queueOrganizationHierarchyId
ORDER BY dr.reportDate;

-- 清理临时表
DROP TABLE #transactionsList;

三、额外索引优化建议 #

• Queues表添加主键索引：因为你频繁通过currentAssignedQueueId关联Queues表，给Queues的id字段添加主键索引（或唯一非聚集索引），消除关联时的全表扫描：

CREATE UNIQUE CLUSTERED INDEX IX_Queues_Id ON Queues(id);

• Transactions索引补充：现有索引已经包含了大部分需要的字段，如果claimedDT的过滤占比高，可以考虑在现有索引中添加claimedDT作为包含列，进一步优化过滤效率。

四、其他细节优化 #

• 避免用VARCHAR存储日期字段（比如Transactions里的dateAssignedBySupervisor），尽量用DATE/DATETIME2类型，减少类型转换开销。
• 检查appointmentDT < '2022-09-28T12:58:47'这个条件是否可以提前过滤，比如在临时表生成时就过滤掉不符合的记录，减少后续计算量。

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