超大数据表关联查询超时，求无额外索引优化方案

问题背景 #

• 表A含5.8亿行数据，表B含12亿行数据，需基于Claim_Nbr、mbr_id、Last_DOS三列做关联查询
• 表B存在关联列完全相同但其他字段不同的多行数据
• 两张表均已在上述三关联列创建非聚集索引，查询仅针对2023年数据
• 当前执行SQL持续超时（运行数小时），无权限新增索引，拆分临时表的尝试未改善性能
• 原查询还涉及与小型临时表左联补充字段，拆分后仍无效

原SQL语句 #

SELECT det.Year,
    det.Claim_Nbr,
    det.Last_DOS,
    det.mbr_id AS Mbr_ID,
    diag.diag,
    diag.status
INTO #Temp_Table_1
FROM A det WITH (NOLOCK)
INNER LOOP JOIN B diag WITH (NOLOCK)
    ON det.Claim_Nbr = diag.claim_nbr
    AND det.mbr_id = diag.mbr_id
    AND det.Last_DOS = diag.last_dos 
WHERE det.YEAR = '2023'
    AND diag.last_dos >= '2023-01-01 00:00:00.000'
    AND diag.last_dos <= '2023-12-31 00:00:00.000'

可行优化方案 #

1. 精简过滤逻辑，消除冗余判断 #

• 直接删除diag.last_dos的时间过滤条件：由于det.Last_DOS = diag.last_dos且det.YEAR='2023'，表B的last_dos必然满足2023年范围，重复过滤会额外增加表B的扫描开销
• 若det.Year是从Last_DOS计算得到的虚拟列，将过滤条件改为det.Last_DOS BETWEEN '2023-01-01' AND '2023-12-31 23:59:59.997'，避免隐式计算导致索引失效

2. 调整关联算法，替换Loop Join #

• 将INNER LOOP JOIN改为INNER HASH JOIN：Loop Join适合小表驱动大表，而两张超大规模表用Hash Join的内存哈希匹配效率更高，可强制指定OPTION (HASH JOIN)
• 先将表A的2023年数据筛选到窄临时表（仅保留关联列和输出列），并给临时表创建聚集索引，再关联表B：

-- 先筛选表A的2023年数据到临时表
SELECT Year, Claim_Nbr, Last_DOS, mbr_id INTO #Filtered_A
FROM A WITH(NOLOCK) WHERE Year='2023'
-- 给临时表创建聚集索引，加速关联
CREATE CLUSTERED INDEX IX_Filtered_A ON #Filtered_A(Claim_Nbr, mbr_id, Last_DOS)

-- 用临时表关联表B
SELECT det.Year,
    det.Claim_Nbr,
    det.Last_DOS,
    det.mbr_id AS Mbr_ID,
    diag.diag,
    diag.status
INTO #Temp_Table_1
FROM #Filtered_A det
INNER HASH JOIN B diag WITH(NOLOCK)
    ON det.Claim_Nbr = diag.claim_nbr
    AND det.mbr_id = diag.mbr_id
    AND det.Last_DOS = diag.last_dos

3. 强制利用现有索引，避免全表扫描 #

• 给表B添加FORCESEEK提示，强制优化器使用已有的非聚集索引进行查找，而非全表扫描：FROM B diag WITH(NOLOCK, FORCESEEK)
• 检查现有非聚集索引的覆盖性：若表A的索引未包含Year列，尽量确保查询只取索引包含的字段，避免书签查找（无权限修改索引时只能被动适配）

4. 分批处理，降低单次负载 #

• 按Claim_Nbr或mbr_id的范围分批查询插入，避免一次性处理超大规模数据导致内存耗尽或锁等待：

DECLARE @BatchStart VARCHAR(50) = '', @BatchEnd VARCHAR(50)

WHILE 1=1
BEGIN
    -- 获取下一批起始的Claim_Nbr
    SELECT TOP 1 @BatchStart = Claim_Nbr 
    FROM A WITH(NOLOCK) 
    WHERE Year='2023' AND Claim_Nbr > @BatchStart 
    ORDER BY Claim_Nbr

    IF @BatchStart IS NULL BREAK

    -- 取当前批次的结束值（每次处理10万条）
    SELECT @BatchEnd = MAX(Claim_Nbr) 
    FROM (SELECT TOP 100000 Claim_Nbr 
          FROM A WITH(NOLOCK) 
          WHERE Year='2023' AND Claim_Nbr >= @BatchStart 
          ORDER BY Claim_Nbr) t

    -- 插入当前批次数据
    INSERT INTO #Temp_Table_1
    SELECT det.Year, det.Claim_Nbr, det.Last_DOS, det.mbr_id AS Mbr_ID, diag.diag, diag.status
    FROM A det WITH(NOLOCK)
    INNER HASH JOIN B diag WITH(NOLOCK)
        ON det.Claim_Nbr = diag.claim_nbr
        AND det.mbr_id = diag.mbr_id
        AND det.Last_DOS = diag.last_dos
    WHERE det.YEAR='2023' 
      AND det.Claim_Nbr BETWEEN @BatchStart AND @BatchEnd
END

5. 排查隐式转换，确保索引生效 #

• 检查两张表关联列的字段类型：若Claim_Nbr、mbr_id、Last_DOS在表A和表B中的类型不一致（如一个是VARCHAR，一个是INT），会导致索引失效，需在查询中显式转换其中一方的类型，例如CAST(det.Claim_Nbr AS VARCHAR(50)) = diag.claim_nbr（优先转换驱动表的字段，以利用被驱动表的索引）

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