大数据集子集查询的全局排序异常修正及性能优化问询

背景 #

需要生成数据集每日变更增量，核心需求是返回视图结果集的子集记录时，同时获取每条记录在整个全局结果集中的排序。原本查询正常，但某复杂视图（关联大量左连接、涉及百万行数据表，且索引已维护）导致查询触发应用30秒ADO超时限制。

现有拆分查询逻辑 #

• Query1：针对复杂视图仅返回2个键值及百万级记录的row_number（避免SELECT *触发超时）
• Query2：将大视图与包含25个ID的键列表关联，再关联Query1获取全局数据偏移量
• Query3：移除两个数据集关联后产生的重复项

当前测试代码 #

DECLARE @KeysToSelect TABLE(Key1 INT)
INSERT INTO @KeysToSelect VALUES(139743),(139878),(139953)

DECLARE @BigView TABLE(Key1 INT, Key2 INT, Area DECIMAL(18,2), BuildingType NVARCHAR(25))
INSERT @BigView VALUES
(100, NULL, 0,''),
(101, NULL, 0,''),
(200, NULL, 0,''),
(201, NULL, 0,''),
(139743, NULL, 8475.00,'Industrial'),
(139743, NULL, 593.00,  'Office'),
(139744, NULL, 0,''),
(139745, NULL, 0,''),
(139746, NULL, 0,''),
(139747, NULL, 0,''),
(139878, NULL, 1268.00,'Office'),
(139878, NULL, 15534.00,'Warehouse'),
(139879, NULL, 0,''),
(139880, NULL, 0,''),
(139881, NULL, 0,''),
(139953, 6, 20000.00,'Warehouse'),
(139953, 14,20000.00,'Office'),
(149956, NULL, 0,''),
(149957, NULL, 0,''),
(149958, NULL, 0,'')

;WITH OveralOrderInData AS
(
    SELECT
        --!!!! Cant't SELECT * FROM @BigView Here because it hits a 30 second timeout limit.  This is what the solve is for. 
        --It would be easy just to return the data with a rank, however, in this case going into a skinny slice of the data to get overall count
        --and joining that against a limited subset of the larger viuew returns in under s second
        --Grabbing the 2 keys and ranking takes less than a second, lots of left joins and views calling views
        ROW_NUMBER() OVER (ORDER BY vw.Key1 ASC, vw.Key2 ASC) AS OrderInData,
        CASE WHEN Keys.Key1 IS NULL THEN NULL ELSE SUM(CASE WHEN Keys.Key1 IS NULL THEN NULL ELSE 1 END) OVER (ORDER BY vw.Key2 ASC,vw.Key1 ASC ROWS UNBOUNDED PRECEDING) END AS MatchedOrderInSearchKeys,
        CASE WHEN Keys.Key1 IS NULL THEN 0 ELSE 1 END AS IsMatched,
        vw.Key1,
        vw.Key2
    FROM
        @BigView vw
        LEFT OUTER JOIN @KeysToSelect Keys ON Keys.Key1 = vw.Key1
)
,Normalized AS
(
    --Second dive into the data, this time, we can filter records based on a very selected Key list and join back our overall order data
    --this can cause doubles and quadruples. Basically, a count will be associated with each duplicate Key1 and Key 2 value.
    --somehow turn these over and rank the duplicates as 1 and 2 and 1 and 2 as opposed to 1 and 1 and 1 and 1 :(
    SELECT
        O.OrderInData,
        O.MatchedOrderInSearchKeys,
        O.Key1,
        O.Key2,
        vw.Area , 
        vw.BuildingType,    
        DENSE_RANK() OVER(PARTITION BY O.Key1 ,O.Key2 ORDER BY OrderInData) AS DistributeOrder, 
        *
    FROM
        OveralOrderInData O
        INNER JOIN @BigView vw ON vw.Key1 = O.Key1 AND (vw.Key2 = O.Key2 OR O.Key2 IS NULL)
    WHERE
        O.IsMatched = 1
)
SELECT 
    * 
FROM 
    Normalized
WHERE   
    DistributeOrder = 1

当前问题 #

查询返回结果中，Key1+Key2重复的记录（如Key1=139743的两条记录），其OrderInData值完全相同（均为5），但期望这些记录的OrderInData按全局顺序连续递增（如5、6）。不想新增额外子查询，需要快速修复方案。

快速修复方案 #

问题根源在于：OveralOrderInData中仅用Key1+Key2生成ROW_NUMBER，但这两个字段存在重复值，导致序号重复；后续关联大视图时，相同Key1+Key2的记录会匹配到同一个OrderInData。

只需在原有CTE基础上做两处调整，无需新增子查询：

调整后的代码 #

DECLARE @KeysToSelect TABLE(Key1 INT)
INSERT INTO @KeysToSelect VALUES(139743),(139878),(139953)

DECLARE @BigView TABLE(Key1 INT, Key2 INT, Area DECIMAL(18,2), BuildingType NVARCHAR(25))
INSERT @BigView VALUES
(100, NULL, 0,''),
(101, NULL, 0,''),
(200, NULL, 0,''),
(201, NULL, 0,''),
(139743, NULL, 8475.00,'Industrial'),
(139743, NULL, 593.00,  'Office'),
(139744, NULL, 0,''),
(139745, NULL, 0,''),
(139746, NULL, 0,''),
(139747, NULL, 0,''),
(139878, NULL, 1268.00,'Office'),
(139878, NULL, 15534.00,'Warehouse'),
(139879, NULL, 0,''),
(139880, NULL, 0,''),
(139881, NULL, 0,''),
(139953, 6, 20000.00,'Warehouse'),
(139953, 14,20000.00,'Office'),
(149956, NULL, 0,''),
(149957, NULL, 0,''),
(149958, NULL, 0,'')

;WITH OveralOrderInData AS
(
    SELECT
        -- 补充Area、BuildingType作为排序字段，确保同Key1+Key2的记录生成唯一全局序号
        ROW_NUMBER() OVER (ORDER BY vw.Key1 ASC, vw.Key2 ASC, vw.Area ASC, vw.BuildingType ASC) AS OrderInData,
        -- 同步调整MatchedOrderInSearchKeys的排序逻辑，保证序号计算一致
        CASE WHEN Keys.Key1 IS NULL THEN NULL ELSE SUM(CASE WHEN Keys.Key1 IS NULL THEN NULL ELSE 1 END) OVER (ORDER BY vw.Key1 ASC,vw.Key2 ASC, vw.Area ASC, vw.BuildingType ASC ROWS UNBOUNDED PRECEDING) END AS MatchedOrderInSearchKeys,
        CASE WHEN Keys.Key1 IS NULL THEN 0 ELSE 1 END AS IsMatched,
        vw.Key1,
        vw.Key2,
        vw.Area,
        vw.BuildingType -- 新增字段用于后续精准关联
    FROM
        @BigView vw
        LEFT OUTER JOIN @KeysToSelect Keys ON Keys.Key1 = vw.Key1
)
,Normalized AS
(
    SELECT
        O.OrderInData,
        O.MatchedOrderInSearchKeys,
        O.Key1,
        O.Key2,
        vw.Area , 
        vw.BuildingType
    FROM
        OveralOrderInData O
        -- 改为精准匹配所有用于排序的字段，避免一对多关联产生重复
        INNER JOIN @BigView vw 
            ON vw.Key1 = O.Key1 
            AND ISNULL(vw.Key2, '') = ISNULL(O.Key2, '')
            AND vw.Area = O.Area
            AND vw.BuildingType = O.BuildingType
    WHERE
        O.IsMatched = 1
)
SELECT 
    *
FROM 
    Normalized

修复说明 #

1. 解决OrderInData重复问题：在ROW_NUMBER的排序规则中加入Area和BuildingType，确保即使Key1+Key2重复，也能通过唯一的字段组合生成连续递增的全局序号。
2. 避免关联重复：在OveralOrderInData中新增Area和BuildingType，后续关联大视图时进行精准匹配，彻底消除一对多关联导致的重复记录，因此无需再用DistributeOrder去重。
3. 保持性能：仅在原有CTE上补充字段和调整关联条件，没有新增子查询，符合快速修复的要求，同时维持原有的低超时风险。

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