使用多个with优化慢查询

Question

我有以下疑问：

with matched_urls as 
(
    select l.Url, min(f.urlfiltertype) as Filter
    from landingpages l
    join landingpageurlfilters lpf on lpf.landingpageid = l.Url
    join urlfilters f on lpf.urlfilterid = f.id
    where f.groupid = 3062
    group by l.Url
), 
all_urls as 
(
    select l.Url, 5 as Filter
    from landingpages l
    where 'iylsuqnzukwv0milinztea' in (select domainid
                                       from domainlandingpages dlp
                                       where l.Url = dlp.landingpageid) 
      and l.Url not in (select Url from matched_urls)
    union 
    select * from matched_urls
) 
select l.*
from landingpages l
join all_urls u on l.Url = u.Url
order by u.Filter asc
offset 0 rows fetch next 30 rows only

以下是查询中使用的表：

​

​

下面是表的DDL：

CREATE TABLE [dbo].[LandingPages]
(
    [Url] [nvarchar](448) NOT NULL,
    [LastUpdated] [datetime2](7) NOT NULL,

     CONSTRAINT [PK_LandingPages] 
         PRIMARY KEY CLUSTERED ([Url] ASC)
                 WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DomainLandingPages]
(
    [LandingPageId] [nvarchar](448) NOT NULL,
    [DomainId] [nvarchar](128) NOT NULL,

    CONSTRAINT [PK_DomainLandingPages] 
        PRIMARY KEY CLUSTERED ([DomainId] ASC, [LandingPageId] ASC)
                WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DomainLandingPages] WITH CHECK 
    ADD CONSTRAINT [FK_DomainLandingPages_Domains_DomainId] 
        FOREIGN KEY([DomainId]) REFERENCES [dbo].[Domains] ([Id])
GO

ALTER TABLE [dbo].[DomainLandingPages] CHECK CONSTRAINT [FK_DomainLandingPages_Domains_DomainId]
GO

ALTER TABLE [dbo].[DomainLandingPages] WITH CHECK 
    ADD CONSTRAINT [FK_DomainLandingPages_LandingPages_LandingPageId] 
        FOREIGN KEY([LandingPageId]) REFERENCES [dbo].[LandingPages] ([Url])
GO

ALTER TABLE [dbo].[DomainLandingPages] CHECK CONSTRAINT [FK_DomainLandingPages_LandingPages_LandingPageId]
GO

CREATE TABLE [dbo].[UrlFilters]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [GroupId] [int] NOT NULL,
    [UrlFilterType] [int] NOT NULL,
    [Filter] [nvarchar](max) NOT NULL,

    CONSTRAINT [PK_UrlFilters] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[UrlFilters] WITH CHECK 
    ADD CONSTRAINT [FK_UrlFilters_Groups_GroupId] 
        FOREIGN KEY([GroupId]) REFERENCES [dbo].[Groups] ([Id])
                ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UrlFilters] CHECK CONSTRAINT [FK_UrlFilters_Groups_GroupId]
GO

CREATE TABLE [dbo].[LandingPageUrlFilters]
(
    [LandingPageId] [nvarchar](448) NOT NULL,
    [UrlFilterId] [int] NOT NULL,

    CONSTRAINT [PK_LandingPageUrlFilters] 
        PRIMARY KEY CLUSTERED ([LandingPageId] ASC, [UrlFilterId] ASC)
                WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LandingPageUrlFilters] WITH CHECK 
    ADD CONSTRAINT [FK_LandingPageUrlFilters_LandingPages_LandingPageId] 
        FOREIGN KEY([LandingPageId]) REFERENCES [dbo].[LandingPages] ([Url])
GO

ALTER TABLE [dbo].[LandingPageUrlFilters] CHECK CONSTRAINT [FK_LandingPageUrlFilters_LandingPages_LandingPageId]
GO

ALTER TABLE [dbo].[LandingPageUrlFilters] WITH CHECK 
    ADD CONSTRAINT [FK_LandingPageUrlFilters_UrlFilters_UrlFilterId] 
        FOREIGN KEY([UrlFilterId]) REFERENCES [dbo].[UrlFilters] ([Id])
GO

ALTER TABLE [dbo].[LandingPageUrlFilters] CHECK CONSTRAINT [FK_LandingPageUrlFilters_UrlFilters_UrlFilterId]
GO

执行计划如下：

https://www.brentozar.com/pastetheplan/?id=H1tHt5pvP

查询拉取给定域的所有urls，然后按UrlFilterType排序-然而，并不是所有的登录页面都有匹配，因此有两个with子句。

就我从执行计划中看到的，它主要是进行索引查找，所以我认为我有正确的索引。但是，查询需要很长时间才能执行，所以我希望有一种更聪明的方法来实现这一点。

我们将非常感谢您的任何意见！

Answer 1

首先，你的统计数据看起来太离谱了。估计为3,700行，实际为219,000行。这至少表明，统计信息更新可能会改变优化器所做的选择。由于这些行估计，优化器选择了查找和嵌套循环操作，根据数据分布，您将读取表的1/3,600k表中的200k行。在这里进行扫描，可能会使用散列连接，效率会更高。

查询本身并没有提供太多信息来过滤aarhus-cluster-onesearch-staging.dbo.LandingPages.PK_LandingPages表。所以它拉取了200k行，以便将它们过滤到30行。如果你能找到一种额外过滤的方法，你应该会看到性能的提高。