title: Optimizing slow query with multiple withs
body: <p>I have the following query:</p>
  <pre class="lang-sql prettyprint-override"><code>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
  </code></pre>
  <p>These are the tables used in the query:</p>
  <p><a href="https://i.stack.imgur.com/bLYVF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bLYVF.png" alt="enter image description here" /></a></p>
  <p>And this is the DDL for the tables:</p>
  <pre class="lang-sql prettyprint-override"><code>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
  </code></pre>
  <p>Here is the execution plan:</p>
  <p><a href="https://www.brentozar.com/pastetheplan/?id=H1tHt5pvP" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=H1tHt5pvP</a></p>
  <p>The query is pulling all urls for a given domain and are then supposed to be ordered by UrlFilterType - however not all landing pages have a match, hence the two with clauses.</p>
  <p>As far as I can see from the execution plan it's mainly doing index seeks, so I think I have the right indexes. However, the query takes very long to execute, so I hope there might be a smarter way of doing this.</p>
  <p>Any input will be greatly appreciated!</p>