优化含日期比较的SQL JOIN查询性能问题求助

原本获取16900行数据的查询耗时约2秒：

SELECT x.lid
FROM schema1.view1 x
INNER JOIN schema1.view2 y
    ON x.cid = y.cid
        and datediff(day, x.indt, y.linvcy)=0    -- 原条件，性能正常
        and x.indt = y.indt                      -- 添加此条件后，查询超时（超过1小时未完成）

添加最后一行的时间等值比较后，查询执行时间直接超过1小时（从未执行完成）。由于两个视图本身都包含多层连接逻辑，查询计划复杂度很高，现寻求优化建议。

相关视图与表定义 #

schema1.view1 定义 #

select
    D.lid,
    D.cid,
    D.indt,
    lower(trim(substring(C.bse, 1, charindex('-', C.bse)-1))) as bse
from (
    select
        B.lid,
        B.cid,
        B.indt
    from ds.v A
    inner join (
        select
            x.lid,
            x.cid,
            x.ivid,
            x.clior,
            x.sosy,
            x.indt
        from rd.ui x
        where
            x.clior like 'abc'
    ) B
    on
        B.ivid like A.ivn
        and B.clior like A.clior
) D
left join ctlg.brchs C
on
    C.paor like D.clior
    and C.sosy like D.sosy
    and C.bid like D.cid

rd.ui 表结构与索引 #

create table rd.ui (
    id int identity(1,1) primary key,
    lid varchar(256),
    cid varchar(256),
    ivid varchar(256),
    indt date,
    clior varchar(256),
    sosy varchar(256)
)

create unique index idx_unlid
on rd.ui(lid)

create index idx_sebid
on rd.ui(bid)

create index idx_seindt
on rd.ui(indt)

create index idx_sec
on rd.ui(clior)

ctlg.brchs 表结构与索引 #

create table ctlg.brchs (
    id int identity(1,1) primary key,
    paor varchar(256),
    sosy varchar(256),
    bid varchar(256)
)
create unique index idx_unbr
on ctlg.brchs (paor, sosy, bid)

schema1.view2 定义 #

SELECT 
    A.cid
    , A.cinvcy
    , B.linvcy
FROM (
    SELECT
        x.cid
        , MAX(x.indt) AS cinvcy
    FROM schema1.view1 x
    GROUP BY
        x.cid
) A
INNER JOIN (
    SELECT
        z.cid
        , MAX(z.indt) AS linvcy
    FROM (
        SELECT
            x.cid
            , x.indt
        FROM schema1.view1 x
        WHERE CONCAT(x.cid, x.ind) NOT IN (
            SELECT CONCAT(y.cid, MAX(y.indt))
            FROM schema1.view1 y
            GROUP BY y.cid 
        )
    ) z
    GROUP BY
        z.cid
) B
ON A.cid=B.cid

已尝试的优化 #

• 尝试用with schemabinding创建索引视图，但因视图包含派生表，不符合索引视图的创建条件，无法实现。
• 将查询中的like替换为=，该部分查询速度显著提升，但整体查询仍极慢。

优化建议 #

1. 重构schema1.view2，避免重复扫描视图 #

当前view2两次调用view1，且内层嵌套查询逻辑冗余，会导致view1的计算逻辑被重复执行，极大增加资源消耗。改用窗口函数直接获取每个cid的最大日期和次大日期，仅需扫描view1一次：

SELECT 
    cid,
    MAX(indt) AS cinvcy,
    MAX(CASE WHEN rn = 2 THEN indt END) AS linvcy
FROM (
    SELECT 
        cid,
        indt,
        ROW_NUMBER() OVER(PARTITION BY cid ORDER BY indt DESC) AS rn
    FROM schema1.view1
) t
GROUP BY cid

2. 优化底层表索引，覆盖查询需求 #

在rd.ui表上创建复合索引，直接覆盖view1的查询和分组聚合需求，避免回表操作：

CREATE NONCLUSTERED INDEX idx_cid_indt ON rd.ui(cid, indt) 
INCLUDE(lid, clior, sosy, ivid)

该索引能加速cid分组求最大indt的操作，同时减少view1中对rd.ui的扫描开销。

3. 简化schema1.view1的嵌套逻辑 #

原view1的多层嵌套会增加查询计划的复杂度，可直接合并为单层连接：

select
    B.lid,
    B.cid,
    B.indt,
    lower(trim(substring(C.bse, 1, charindex('-', C.bse)-1))) as bse
from ds.v A
inner join rd.ui B
    on B.ivid = A.ivn
    and B.clior = A.clior
    and B.clior = 'abc'
left join ctlg.brchs C
    on C.paor = B.clior
    and C.sosy = B.sosy
    and C.bid = B.cid

合并后查询优化器更容易生成高效的执行计划。

4. 替换CONCAT拼接的NOT IN逻辑 #

原view2中CONCAT(x.cid, x.ind) NOT IN (...)的写法会导致索引失效，还可能因字符串拼接出现匹配错误（比如cid为'123'、indt为'20240101'与cid为'12'、indt为'320240101'会被误判为相同）。前面的窗口函数方案已完全避免这个问题。

5. 更新底层表统计信息 #

确保底层表的统计信息是最新的，让查询优化器能生成最优计划：

UPDATE STATISTICS rd.ui;
UPDATE STATISTICS ctlg.brchs;
UPDATE STATISTICS ds.v;

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