多列不等式关联的SQL Server查询优化方案问询

我拥有一张名为#TempRule的表，包含多组范围最小值与最大值对；另有一张#TestSet表，存储大量单值记录，需判断这些单值是否落在Rule表对应行的所有范围内（可类比人员属性与规则匹配场景）。Rule表行数可达数十万至千万级，TestSet表可达数亿级，可分块处理。

需求：

• 快速判断TestSet每行是否被至少一条Rule记录的所有范围覆盖（即标记'被覆盖'状态）；
• 可选统计每行被覆盖的规则数量；
• 寻求SQL Server环境下的查询或算法优化方案，包括如何实现多不等式列关联时仅对每张表做一次扫描。

现有查询处理40万条TestSet记录与630万条Rule记录耗时约23分钟，执行计划显示存在表扫描，核心慢查询部分为嵌套循环左反半连接中的表扫描操作。

--TEST DATA SET UP 'RULES'
CREATE TABLE #TempRule (
    AMin INT,
    AMax INT,
    BMin INT,
    BMax INT,
    CMin INT,
    CMax INT,
    DMin INT,
    DMax INT,
    EMin INT,
    EMax INT,
    FMin INT,
    FMax INT
);


INSERT INTO #TempRule (AMin, AMax, BMin, BMax, CMin, CMax, DMin, DMax, EMin, EMax, FMin, FMax)
SELECT
    AMin,
    CASE WHEN AMin > AMax THEN AMin ELSE AMax END AS AMax,
    BMin,
    CASE WHEN BMin > BMax THEN BMin ELSE BMax END AS BMax,
    CMin,
    CASE WHEN CMin > CMax THEN CMin ELSE CMax END AS CMax,
    DMin,
    CASE WHEN DMin > DMax THEN DMin ELSE DMax END AS DMax,
    EMin,
    CASE WHEN EMin > EMax THEN EMin ELSE EMax END AS EMax,
    FMin,
    CASE WHEN FMin > FMax THEN FMin ELSE FMax END AS FMax
FROM (
    SELECT
        FLOOR(RAND(CHECKSUM(NEWID())) * 100000) + 1 AS AMin,
        FLOOR(RAND(CHECKSUM(NEWID())) * 100000) + 1 AS AMax,
        (FLOOR(RAND(CHECKSUM(NEWID())) * 31) * 10) + 1690 AS BMin,
        (FLOOR(RAND(CHECKSUM(NEWID())) * 31) * 10) + 1690 AS BMax,
        FLOOR(RAND(CHECKSUM(NEWID())) * 51) * 1000 AS CMin,
        FLOOR(RAND(CHECKSUM(NEWID())) * 51) * 1000 AS CMax,
        FLOOR(RAND(CHECKSUM(NEWID())) * 3) + 1 AS DMin,
        FLOOR(RAND(CHECKSUM(NEWID())) * 3) + 1 AS DMax,
        FLOOR(RAND(CHECKSUM(NEWID())) * 5) + 1 AS EMin,
        FLOOR(RAND(CHECKSUM(NEWID())) * 5) + 1 AS EMax,
        FLOOR(RAND(CHECKSUM(NEWID())) * 4) + 1 AS FMin,
        FLOOR(RAND(CHECKSUM(NEWID())) * 4) + 1 AS FMax
    FROM
        (SELECT TOP 400000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.objects AS o1 CROSS JOIN sys.objects AS o2 CROSS JOIN sys.objects AS o3) AS Numbers
) AS RandomValues;


-- Create an index that covers every column
CREATE CLUSTERED INDEX idx_TempRules_AllColumns ON #TempRule (AMin, AMax, BMin, BMax, CMin, CMax, DMin, DMax, EMin, EMax, FMin, FMax);


--TEST DATA SET UP 'Set of Values'
CREATE TABLE #TestSet (
    A INT,
    B INT,    
    C INT,
    D INT,
    E INT,       
    F INT
);

INSERT INTO #TestSet (A, B, C, D, E, F)
SELECT
    FLOOR(RAND(CHECKSUM(NEWID())) * 100000) + 1 AS A,
    (FLOOR(RAND(CHECKSUM(NEWID())) * 31) * 10) + 1690 AS B,
    FLOOR(RAND(CHECKSUM(NEWID())) * 51) * 1000 AS C,
    FLOOR(RAND(CHECKSUM(NEWID())) * 3) + 1 AS D,
    FLOOR(RAND(CHECKSUM(NEWID())) * 5) + 1 AS E,
    FLOOR(RAND(CHECKSUM(NEWID())) * 4) + 1 AS F
FROM
    (SELECT TOP 15000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM master.dbo.spt_values AS a CROSS JOIN master.dbo.spt_values AS b) AS Numbers;

SELECT COUNT(*) FROM #TestSet
CREATE CLUSTERED INDEX idx_TestSet ON #TestSet (A, B, C, D, E, F);

CREATE TABLE #TestSetChunk (
    A INT,
    B INT,    
    C INT,
    D INT,
    E INT,       
    F INT
);
CREATE CLUSTERED INDEX idx_TestSetChunk ON #TestSetChunk (A, B, C, D, E, F);



-- Create the results temp table
CREATE TABLE #Results (
    A INT,
    B INT,
    C INT,
    D INT,
    E INT,
    F INT
);



-- Variables to control the loop
DECLARE @BatchSize INT = 1000000;
DECLARE @Offset INT = 0;
DECLARE @TotalRows INT;
DECLARE @ProcessedRows INT = 0;

-- Get the total number of rows in #TestSet
SELECT @TotalRows = COUNT(*) FROM #TestSet;

-- Loop to process 1 million records at a time
WHILE @Offset < @TotalRows
BEGIN
    
    DELETE FROM #TestSetChunk;  
    INSERT INTO #TestSetChunk (A, B, C, D, E, F)
    SELECT A, B, C, D, E, F
    FROM (
        SELECT A, B, C, D, E, F, 
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
        FROM #TestSet
    ) AS n
    WHERE n.RowNum BETWEEN @Offset + 1 AND @Offset + @BatchSize;
    
    
    ---THIS IS SLOW running part that I am trying to optimize 
    INSERT INTO #Results WITH (TABLOCK) (A, B, C, D, E, F)
    SELECT n.A, n.B, n.C, n.D, n.E, n.F
    FROM #TestSetChunk n WITH (INDEX(idx_TestSetChunk)) 
    WHERE NOT EXISTS (
        SELECT 1
        FROM #TempRule t WITH (INDEX(idx_TempRules_AllColumns)) 
        WHERE n.A >= t.AMin AND n.A <= t.AMax
          AND n.B >= t.BMin AND n.B <= t.BMax
          AND n.C >= t.CMin AND n.C <= t.CMax
          AND n.D >= t.DMin AND n.D <= t.DMax
          AND n.E >= t.EMin AND n.E <= t.EMax
          AND n.F >= t.FMin AND n.F <= t.FMax
    )
    ORDER BY n.A;
   

    -- Increment the offset
    SET @Offset = @Offset + @BatchSize;
    SET @ProcessedRows = @ProcessedRows + @BatchSize;

    -- Print progress
    PRINT 'Processed ' + CAST(@ProcessedRows AS VARCHAR) + ' rows out of ' + CAST(@TotalRows AS VARCHAR);
    

   END



-- Select results (Need to do more.  Just a simplification)
SELECT count(*) FROM #Results;

执行计划：

|--Table Insert(OBJECT:([tempdb].[dbo].[#Results]), SET:([tempdb].[dbo].[#Results].[A] = [tempdb].[dbo].[#TestSetChunk].[A] as [n].[A],[tempdb].[dbo].[#Results].[B] = [tempdb].[dbo].[#TestSetChunk].[B] as [n].[B],[tempdb].[dbo].[#Results].[C] = [tempdb].[dbo].[#TestSetChunk].[C] as [n].[C],[tempdb].[dbo].[#Results].[D] = [tempdb].[dbo].[#TestSetChunk].[D] as [n].[D],[tempdb].[dbo].[#Results].[E] = [tempdb].[dbo].[#TestSetChunk].[E] as [n].[E],[tempdb].[dbo].[#Results].[F] = [tempdb].[dbo].[#TestSetChunk].[F] as [n].[F]))
            |--Nested Loops(Left Anti Semi Join, WHERE:([tempdb].[dbo].[#TestSetChunk].[A] as [n].[A]>=[tempdb].[dbo].[#TempRule].[AMin] as [t].[AMin] AND [tempdb].[dbo].[#TestSetChunk].[A] as [n].[A]<=[tempdb].[dbo].[#TempRule].[AMax] as [t].[AMax] AND [tempdb].[dbo].[#TestSetChunk].[B] as [n].[B]>=[tempdb].[dbo].[#TempRule].[BMin] as [t].[BMin] AND [tempdb].[dbo].[#TestSetChunk].[B] as [n].[B]<=[tempdb].[dbo].[#TempRule].[BMax] as [t].[BMax] AND [tempdb].[dbo].[#TestSetChunk].[C] as [n].[C]>=[tempdb].[dbo].[#TempRule].[CMin] as [t].[CMin] AND [tempdb].[dbo].[#TestSetChunk].[C] as [n].[C]<=[tempdb].[dbo].[#TempRule].[CMax] as [t].[CMax] AND [tempdb].[dbo].[#TestSetChunk].[D] as [n].[D]>=[tempdb].[dbo].[#TempRule].[DMin] as [t].[DMin] AND [tempdb].[dbo].[#TestSetChunk].[D] as [n].[D]<=[tempdb].[dbo].[#TempRule].[DMax] as [t].[DMax] AND [tempdb].[dbo].[#TestSetChunk].[E] as [n].[E]>=[tempdb].[dbo].[#TempRule].[EMin] as [t].[EMin] AND [tempdb].[dbo].[#TestSetChunk].[E] as [n].[E]<=[tempdb].[dbo].[#TempRule].[EMax] as [t].[EMax] AND [tempdb].[dbo].[#TestSetChunk].[F] as [n].[F]>=[tempdb].[dbo].[#TempRule].[FMin] as [t].[FMin] AND [tempdb].[dbo].[#TestSetChunk].[F] as [n].[F]<=[tempdb].[dbo].[#TempRule].[FMax] as [t].[FMax]))
                 |--Table Scan(OBJECT:([tempdb].[dbo].[#TestSetChunk] AS [n]))
                 |--Table Scan(OBJECT:([tempdb].[dbo].[#TempRule] AS [t]))

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