如何优化含CTE、GROUP BY且运行超20小时的Azure SQL查询？

查询逻辑与代码 #

现有SQL查询通过CTE（公共表表达式）先执行多表连接，再基于该CTE关联其他表，经GROUP BY聚合后获取前15条记录，最终关联尺寸表返回明细数据。具体代码如下：

WITH join_table as (
    SELECT pl.yearmonth, 
    pl.pdid, 
    pl.OrderId,
    pl.OrderNo AS OrderNumber, 
    pl.pmc,
    pl.pmcode AS pmaa,
    pl.spid,
    pl.issuingdate,
    pl.plguid, 
    pusa.SizeCode,
    pusa.atnumber,
    pu.tpc, 
    pu.ItemQty AS puItemQty
    FROM table1 pl 
    JOIN table3 pusa ON pl.plguid=pusa.plguid
    JOIN table2 pu ON pusa.plguid=pu.plguid AND pusa.puguid=pu.puguid
    WHERE pl.pmode='BBB'
      and pl.pltype='CCC'
      and pl.plstatus='AAA'
      and pu.ctcode='S'
      AND pu.ctype='DDD'
      AND pu.tpc <> 'EEE' 
),
get_top_15PM as (
    SELECT TOP 15 pl.pmc, sum(cast(puItemQty as BIGINT)) AS SumpuItemQty
    FROM join_table pl
    join abctable b on b.pdid= pl.pdid and b.spid= pl.spid
    WHERE pl.issuingdate > '2021-8-1'
    and prodtypeid in (select prodtypeid from prodtype where prodgrpid in (3,4,5,7,8,9,10,13,20))
    group by pl.pmc
    ORDER BY SumpuItemQty DESC
)
SELECT DISTINCT 
    pl.yearmonth, 
    pl.pdid, 
    pl.OrderId,
    pl.OrderNumber, 
    pl.pmc,
    pl.pmaa,
    pl.spid,
    pl.issuingdate, 
    pl.atnumber,
    pl.tpc, 
    pl.puItemQty,
    s.SizeName AS Size,
    s.SizeLength,
    s.SizeWidth,
    s.SizeHeight,
    s.SizeVolume,
    s.SizeWeight
FROM join_table pl 
JOIN PLSize s ON pl.plguid=s.plguid AND pl.SizeCode=s.SizeCode
WHERE  pl.pmc IN ( SELECT pmc from get_top_15PM) 
      AND pl.yearmonth>=202100

该查询在Azure SQL数据库上运行时长已超过20小时。

环境与表统计信息 #

• 数据库定价层级：S6，750GB存储，剩余20%未使用存储空间
• 表数据统计：

TableName   rows         TotalSpaceGB   UsedSpaceGB UnusedSpaceGB
table2      332,318,173  117.72         117.71      0.01
table3      153,700,352  60.78          60.76       0.01
table1      15,339,815   13.21          13.20       0.01
abctable    1,232,868    0.81           0.80        0.00

• 等待类型：(14ms)PAGEIOLATCH_SH:dev-db:1(*)，有时为NULL，通过sp_WhoIsActive获取

补充说明 #

• 实际执行计划通过Stack Overflow上的方法从运行中查询获取
• 各表主键均存在聚集索引，现有非聚集索引未被用于筛选、连接或排序操作
• 表关系：table1是table2的父表，table2是table3的父表

现有索引信息 #

--table1
index_name          index_description                 index_keys
idx_table1_pmcode   nonclustered                      pmcode
PK_table1           clustered, unique, primary key    plguid

--table2
index_name          index_description                 index_keys
IX_table2_plguid    nonclustered                      plguid
PK_table2           clustered, unique, primary key    puguid

--table3
index_name          index_description                 index_keys
IX_table3_plguid    nonclustered                      plguid
PK_table3           clustered, unique, primary key    pusguid

--abctable
index_name             index_description               index_keys
nci_wi_abctable_2BC1D  nonclustered                    dpyearmonth
nci_wi_abctable_FAA89  nonclustered                    dpnumber, ptid
PK_abctable            clustered, unique, primary key  OrderId

索引DDL语句 #

--table1
SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [idx_table1_pmcode] ON [dbo].[table1]
(
    [pmcode] ASC
)
INCLUDE([OrderID],[OrderNo],[yearmonth],[pdid]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

--table2
SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [IX_table2_plguid] ON [dbo].[table2]
(
    [plguid] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

--table3
SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [IX_table3_plguid] ON [dbo].[table3]
(
    [plguid] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

--abctable
SET ANSI_PADDING ON
GO


CREATE NONCLUSTERED INDEX [nci_wi_abctable_2BC1D] ON [dbo].[abctable]
(
    [dpyearmonth] ASC
)
INCLUDE([pduid],[pdid]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [nci_wi_abctable_FAA89] ON [dbo].[abctable]
(
    [dpyearmonth] ASC,
    [ptid] ASC
)
INCLUDE([OrderStatus],[spid]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

恳请提供优化建议或解决方案。

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