title: Improving performance for large table ordered top query
body: <p>I have a database that stores Feeds and their articles. Users can have subscriptions to them and I allow them to categorize them by Folders, which I solved by using Tags i store in a relation to the Users and Feeds table.</p>
  
  <p>The tables look like this:</p>
  
  <p>Feeds:</p>
  
  <pre><code>CREATE TABLE [dbo].[Feed](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Url] [nvarchar](250) NOT NULL,
  ...
  </code></pre>
  
  <p>FeedItems:</p>
  
  <pre><code>CREATE TABLE [dbo].[FeedItem](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [FeedId] [int] NOT NULL, -- FK to Feed
  ..
  </code></pre>
  
  <p>FeedTags:</p>
  
  <pre><code>CREATE TABLE [dbo].[FeedTag](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [UserId] [int] NOT NULL, -- FK to Users
  [FeedId] [int] NOT NULL, -- FK to Feed
  ...
  </code></pre>
  
  <p>I spare User and UserSubscriptions tables as they're pretty obvious and not really part of the Problem (IMHO).</p>
  
  <p>On FeedItem, I have a clustered index with [FeedId] ASC, [Id] DESC</p>
  
  <p>I choose DESC on Id as I sort feeditems always descending by their Id and I'm most of the time intrested in the most recent items. I also choose this index because normally, I'd like to know the FeedItems of a specific Feed.</p>
  
  <p>But when I need to query for a range of Feeds, for example when I want the top 50 articles for all subscriptions of the user, or the top 50 articles of a specific Folder (= Feeds with a specific FeedTag), things get complicated. Mainly if there are many Feeds and/or the Feeds have a lot of articles.</p>
  
  <p>In most cases, SQL Server chooses to do a Clustered Index Seek on FeedItems and, as far as I can tell from the row Counts, it retrieves <em>all</em> articles from the affected Feeds from the disk. This can cause a lot of physical reads.</p>
  
  <p>Here is an example query:</p>
  
  <pre><code>select f.Id, f.Name, fi.Id, fi.Title, fi.Inserted
  from Feed f
  inner join FeedItem fi on f.id = fi.feedid
  where (exists (select 1 from UserSubscription us where us.UserId = @userId and us.FeedId = f.Id))
  and (exists(select 1 from FeedTag ft where ft.FeedId = f.id and ft.UserId = @userId and ft.TagId = @tagid))
  order by fi.id desc
  </code></pre>
  
  <p>The resulting execution plan Looks like this:</p>
  
  <p><img src="https://i.stack.imgur.com/WV2eN.jpg" alt="Execution plan"></p>
  
  <p>Obviously, SQL Server needs all rows for sorting to apply the top 50 on these sorted results as the clustered index not really has the items in the perfect sort order when I'm limiting the results by FeedId.</p>
  
  <p>Is there a way to help SQL Server not needing to get each and every FeedItem of each feed? I thought a lot about it but I can't come up with an additional index that would help here.</p>
  
  <p>Is there a better data model that could help in such a scenario? Or could the query be improved?</p>
  
  <p>Any ideas &amp; help would be greatly appreciated :)</p>