优化SQL Azure中多层视图关联的慢查询问题

环境说明 #

针对 Microsoft SQL Azure (RTM) - 12.0.2000.8，Azure Data Studio导出的执行计划XML无法在Paste The Plan网站使用，提供了原始XML内容。

数据与视图结构 #

现有发票数据集表raw_data.invoices结构如下：

已创建视图myview.invoices整理数据：

select
    D.invoice_date,
    D.invoice_id,
    D.institution,
    C.institution_name,
    lower(trim(substring(C.institution_name, 1, charindex('-', C.institution_name)-1))) as institution_name,
    D.billed_to,
    D.item,
    D.qty
from raw_data.invoices
left join catalogues.institutions C
on
    C.institution_code = D.institution

视图myview.last_2_inv_cycles用于获取每个机构的最近两个发票日期：

select 
    A.institution
    , A.current_inv_cycle
    , B.last_inv_cycle
from (
    select
        x.institution
        , max(x.invoice_date) as current_inv_cycle
    from myview.invoices x
    group by
        x.institution
) A
inner join (
    select
        z.institution
        , max(z.invoice_date) as last_inv_cycle
    from (
        select
            x.institution
            , x.invoice_date
        from myview.invoices x
        where concat(x.institution, x.invoice_date) not in (
            select concat(y.institution, max(y.invoice_date))
            from myview.invoices y
            group by y.institution 
        )
    ) z
    group by
        z.institution
) B
on A.institution=B.institution

最终视图myview.qty_over_15关联上述两个视图，筛选最近两个发票周期中qty > 15的记录：

with over_2_weeks as (
    select
        x.institution,
        x.billed_to,
        x.item
    from myview.invoices x
    inner join myview.last_2_inv_cycles y
    on x.institution = y.institution
        and (x.invoice_date = y.last_inv_cycle or x.invoice_date = y.current_inv_cycle)
    group by
        x.institution,
        x.billed_to,
        x.item
    having
        sum(case when x.qty > 15 then 1 else 0 end) >= 2

        -- 业务定义的例外规则
        and x.institution <> '2222'
)

select
    A.invoice_date,
    A.invoice_id,
    A.institution,
    A.billed_to,
    A.item,
    A.qty
from myview.invoices A
inner join over_2_weeks D

-- 存在性能问题的关联，耗时超1小时
on
    A.institution=D.institution
    AND A.billed_to=D.billed_to
    AND A.item=D.item

inner join myview.last_2_inv_cycles C
on
    A.institution=C.institution
    and A.invoice_date=C.current_inv_cycle

-- 更多例外名单
where
    A.billed_to not in (
        'Jake Johnson', 'Bill Gates'
    )

性能问题 #

当前查询复杂度高，执行速度极慢，最终视图运行4小时仍未完成加载。由于myview.qty_over_15需与myview.invoices保持格式一致，必须基于视图而非raw_data.invoices表查询，寻求性能优化方案。

表结构与索引 #

raw_data.invoices的表定义及索引如下：

create table raw_data.uniform_invoices (
    id int identity(1,1) primary key
    , invoice_id varchar(256)
    , invoice_date date
    , institution varchar(256)
    , billed_to varchar(256)
    , item varchar(256)
    , qty int
)

create index idx_search_invoice_id
on raw_data.invoices(invoice_id)

create index idx_search_invoice_date
on raw_data.invoices(invoice_date)

create index idx_search_institution
on raw_data.invoices(institution)

create index idx_invoice_of_institution
on raw_data.invoices(invoice_id, institution)

create index idx_search_billed_to
on raw_data.invoices(institution, billed_to)

create index idx_search_billed_to_item
on raw_data.invoices(billed_to, item)

create index idx_search_bill
on raw_data.invoices(qty, billed_to)

create index idx_search_item_charge
on raw_data.invoices(
    institution
    , invoice_id
    , billed_to
    , item
)

优化方案 #

1. 重构last_2_inv_cycles视图，消除低效嵌套与字符串拼接 #

原视图用concat判断排除最大日期，性能极差，改用窗口函数直接提取每个机构的最近两个日期：

create or alter view myview.last_2_inv_cycles
as
select
    institution,
    max(case when rn = 1 then invoice_date end) as current_inv_cycle,
    max(case when rn = 2 then invoice_date end) as last_inv_cycle
from (
    select
        institution,
        invoice_date,
        row_number() over(partition by institution order by invoice_date desc) as rn
    from myview.invoices
    group by institution, invoice_date -- 去重同一机构的重复日期
) t
where rn <= 2
group by institution;

窗口函数row_number()能高效完成分组排序，直接获取前N条记录，彻底避免原查询的多层嵌套和字符串拼接开销。

2. 精简over_2_weeksCTE，减少不必要计算 #

原CTE中sum(case...)可以提前过滤qty>15的记录，再统计符合条件的周期数：

with over_2_weeks as (
    select
        x.institution,
        x.billed_to,
        x.item
    from myview.invoices x
    inner join myview.last_2_inv_cycles y
        on x.institution = y.institution
        and x.invoice_date in (y.last_inv_cycle, y.current_inv_cycle)
    where x.qty > 15 -- 提前过滤，缩小分组数据范围
        and x.institution <> '2222'
    group by x.institution, x.billed_to, x.item
    having count(distinct x.invoice_date) >= 2 -- 统计两个周期均有符合条件的记录
)

提前过滤qty>15的记录，减少后续分组的数据量；用count(distinct x.invoice_date)替代原sum(case)，逻辑更清晰且性能更优。

3. 调整关联顺序，降低数据处理量 #

最终查询中先关联last_2_inv_cycles过滤出当前周期的发票，再关联over_2_weeks，减少关联的数据范围：

with over_2_weeks as (
    select
        x.institution,
        x.billed_to,
        x.item
    from myview.invoices x
    inner join myview.last_2_inv_cycles y
        on x.institution = y.institution
        and x.invoice_date in (y.last_inv_cycle, y.current_inv_cycle)
    where x.qty > 15
        and x.institution <> '2222'
    group by x.institution, x.billed_to, x.item
    having count(distinct x.invoice_date) >= 2
)
select
    A.invoice_date,
    A.invoice_id,
    A.institution,
    A.billed_to,
    A.item,
    A.qty
from myview.invoices A
inner join myview.last_2_inv_cycles C
    on A.institution = C.institution
    and A.invoice_date = C.current_inv_cycle
inner join over_2_weeks D
    on A.institution = D.institution
    and A.billed_to = D.billed_to
    and A.item = D.item
where
    A.billed_to not in ('Jake Johnson', 'Bill Gates')

先过滤出当前周期的发票，再关联over_2_weeks，大幅减少需要处理的数据行数。

4. 优化索引，消除回表查询 #

针对raw_data.invoices和catalogues.institutions创建覆盖索引，支持视图的高效查询：

-- 支持myview.invoices的左连接和过滤需求，避免回表
create nonclustered index idx_invoices_institution_date_billed_item_qty
on raw_data.invoices(institution, invoice_date, billed_to, item)
include (qty);

-- 支持catalogues.institutions的连接需求，避免回表
create nonclustered index idx_institutions_code_name
on catalogues.institutions(institution_code)
include (institution_name);

覆盖索引包含查询所需的所有字段，无需回表读取数据，大幅提升视图查询效率。

5. 改用索引视图（可选，适合低更新频率场景） #

如果数据更新频率不高，将myview.invoices改为索引视图，提前计算并存储结果：

create or alter view myview.invoices
with schemabinding
as
select
    D.invoice_date,
    D.invoice_id,
    D.institution,
    C.institution_name,
    lower(trim(substring(C.institution_name, 1, charindex('-', C.institution_name)-1))) as short_institution_name,
    D.billed_to,
    D.item,
    D.qty,
    count_big(*) as dummy -- 索引视图必须包含count_big函数
from raw_data.invoices D
left join catalogues.institutions C
    on C.institution_code = D.institution
group by 
    D.invoice_date, D.invoice_id, D.institution, C.institution_name,
    lower(trim(substring(C.institution_name, 1, charindex('-', C.institution_name)-1))),
    D.billed_to, D.item, D.qty;

create unique clustered index idx_invoices_clustered
on myview.invoices(institution, invoice_date, billed_to, item);

索引视图将查询结果持久化到磁盘，后续查询直接读取索引数据，避免重复计算连接和字符串处理逻辑。

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